Search code examples
c#datasetvisual-studio-2012strongly-typed-dataset

Accessing a database using c# and Visual Studio design-time dataset

I've seen so many ways of accessing a database using C# I don't know which one is "best" for simple reading/writing/manipulating data. I used to just use generic DataSet/DataTable objects but I'm trying to use more type safe structures. I've started by adding a dataset (xsd) in visual studio and connecting to a SQL server backend.

I'm trying to query search a column in a table for a given string, but the column isn't the primary key (so i can't use .Find()). How do i do this? Do i need to use LINQ or can i use extension methods/lambda expressions?

On a more basic level, when using the design-time dataset do I need to use Table adapters to fill each table in the Dataset I use or do i just instantiate the dataset? The documentation is a bit confusing to me.

Solution

  • You can use Linq-To-DataSet which is the most powerful way (not in terms of efficiency but in terms of readability and maintainability) to query the DataSet.

    Read more: Querying Typed DataSets.

    You have the option to create a TableAdapter or just a DataTable on the VS designer. If you add a TableAdapter VS creates also the according DataTable. If you only add a DataTable, you have to provide your own way to fill it. TableAdapters are similar to a DataAdapter in ADO.NET.

    So if you have created a DataSet named DataSet1 and added a TableAdapter which selects a table named tabData, Visual Studio will automatically create a DataTable named tabDataDataTable and a TableAdapter named tabDataTableAdapter in the namespace DataSet1TableAdapters.

    So you could fill this table in this way:

    var dataSet = new DataSet1();
var da = new DataSet1TableAdapters.tabDataTableAdapter();
da.Fill(dataSet.tabData);

    Assuming that the table has columns Name and Age and you want to find all rows where the name starts with "Jon" and age is > 30, you can use LINQ's Where:

    var jons = dataSet.tabData
                  .Where(r => r.Name.StartsWith("Jon") && r.Age > 30);
foreach (DataSet1.tabDataRow row in jons)
{
    Console.WriteLine("{0} is {1} years old", row.Name, row.Age);
}

    Note that you can use LINQ-To-DataSet type safe(r.Name is a string andr.Age an int).