public static DataSet SelectSqlRows(string connectionString,
string queryString, string tableName)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = new SqlCommand(queryString, connection);
SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
connection.Open();
DataSet dataSet = new DataSet();
adapter.Fill(dataSet, tableName);
//code to modify data in DataSet here
builder.GetUpdateCommand();
//Without the SqlCommandBuilder this line would fail
adapter.Update(dataSet, tableName);
return dataSet;
}
}
DataTable Object
A DataSet object is made up of a collection of tables,
relationships, and constraints. In
ADO.NET, DataTable objects are used to represent the tables in a DataSet
object. A DataTable object represents
one table of in-memory relational data.
The data is local to the .NET application in which it resides, however,
can be populated from a data source such as SQL Server or VFP using a
DataAdapter.
You can create and use a DataTable independently or as a member of
a DataSet object. DataTable objects can then
be used by other .NET Framework objects, including the DataView object. Access the collection of tables in a DataSet
object through the DataSet object’s Tables property.
The schema, or structure, of a table is represented by columns and
constraints. Define the schema of a
DataTable object using DataColumn objects, as well as ForeignKeyConstraint and
UniqueConstraint objects. The columns in
a table can map to columns in a data source, contain calculated values from
expressions, automatically increment their values, or contain primary key
values.
If you populate a DataTable object from a database, it will
inherit the constraints from the database so you do not have to do all of the
work manually. A DataTable object must
also have rows in which to contain and order the data. The DataRow class represents the actual data
contained in the table. As you access
and change the data within a row, the DataRow object maintains both its current
and original state.
You can create parent/child relationships between tables within a
database, like SQL Server and VFP, using one or more related columns in the
tables. You can create a relationship
between DataTable objects using a DataRelation object, which may then be used
to return a row’s related child or parent rows.
DataTable Example
// Create a DataTable with 5 columns.
//
DataTable table = new DataTable();
table.Columns.Add("Weight", typeof(int));
table.Columns.Add("Name", typeof(string));
table.Columns.Add("Breed", typeof(string));
table.Columns.Add("Size", typeof(char));
table.Columns.Add("Date", typeof(DateTime));
//
// Add data to the DataTable. [This will be dyanmically generated from your app.]
//
AddDogRow(table, 57, "Koko", "Shar Pei");
AddDogRow(table, 130, "Fido", "Bullmastiff");
AddDogRow(table, 92, "Alex", "Anatolian Shepherd Dog");
AddDogRow(table, 25, "Charles", "Cavalier King Charles Spaniel");
AddDogRow(table, 7, "Candy", "Yorkshire Terrier");
/// <summary>
/// Add dog data to the DataTable.
/// </summary>
static DataRow AddDogRow(DataTable table, int weight, string name, string breed)
{
//
// This method uses custom code to generate the size type.
//
return table.Rows.Add(weight, name, breed, GetSizeChar(weight), DateTime.Now);
}
/// <summary>
/// Get size code for dogs by weight.
/// </summary>
static char GetSizeChar(int weight)
{
//
// Custom method for getting size code.
//
if (weight > 100)
{
return 'B';
}
else if (weight > 50)
{
return 'M';
}
else
{
return 'S';
}
}