using System; using System.Data; using System.Data.SqlClient; using System.Data.Common; class Mappings { public static void Main() { SqlConnection mySqlConnection =new SqlConnection("server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;"); SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); mySqlCommand.CommandText = "SELECT ID AS MappedID, FirstName, LastName " + "FROM Employee AS Emp " + "WHERE ID = 9"; SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(); mySqlDataAdapter.SelectCommand = mySqlCommand; DataSet myDataSet = new DataSet(); mySqlConnection.Open(); mySqlDataAdapter.Fill(myDataSet, "Employee"); mySqlConnection.Close(); DataTableMapping myDataTableMapping = mySqlDataAdapter.TableMappings.Add("Employee", "Emp"); myDataSet.Tables["Employee"].TableName = "Emp"; Console.WriteLine("myDataTableMapping.DataSetTable = " + myDataTableMapping.DataSetTable); Console.WriteLine("myDataTableMapping.SourceTable = " + myDataTableMapping.SourceTable); myDataTableMapping.ColumnMappings.Add("ID", "MappedID"); DataTable myDataTable = myDataSet.Tables["Emp"]; foreach (DataRow myDataRow in myDataTable.Rows) { Console.WriteLine("ID = " + myDataRow["MappedID"]); Console.WriteLine("FirstName = " + myDataRow["FirstName"]); Console.WriteLine("LastName = " + myDataRow["LastName"]); } } }
Move DataRow in a DataTable
///////////////////////////////////////////////////////////////////////////////////////////////
//
// This File is Part of the CallButler Open Source PBX (http://www.codeplex.com/callbutler
//
// Copyright (c) 2005-2008, Jim Heising
// All rights reserved.
//
// Redistribution and use in source and binary forms, with or without modification,
// are permitted provided that the following conditions are met:
//
// * Redistributions of source code must retain the above copyright notice,
// this list of conditions and the following disclaimer.
//
// * Redistributions in binary form must reproduce the above copyright notice,
// this list of conditions and the following disclaimer in the documentation and/or
// other materials provided with the distribution.
//
// * Neither the name of Jim Heising nor the names of its contributors may be
// used to endorse or promote products derived from this software without specific prior
// written permission.
//
// THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS “AS IS” AND
// ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
// WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED.
// IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT,
// INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT
// NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR
// PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY,
// WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
// ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
// POSSIBILITY OF SUCH DAMAGE.
//
///////////////////////////////////////////////////////////////////////////////////////////////
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
namespace WOSI.Utilities
{
public class DataUtils
{
public static DataRow CreateRowCopy(DataTable newParentTable, DataRow dataRow)
{
DataRow newRow = newParentTable.NewRow();
for (int index = 0; index < dataRow.Table.Columns.Count; index++)
{
newRow[index] = dataRow[index];
}
return newRow;
}
public static void MoveDataRowUp(DataRow dataRow)
{
DataTable parentTable = dataRow.Table;
int rowIndex = parentTable.Rows.IndexOf(dataRow);
if (rowIndex > 0)
{
DataRow newDataRow = parentTable.NewRow();
for (int index = 0; index < dataRow.ItemArray.Length; index++) { newDataRow[index] = dataRow[index]; } parentTable.Rows.Remove(dataRow); parentTable.Rows.InsertAt(newDataRow, rowIndex - 1); dataRow = newDataRow; } } public static void MoveDataRowDown(DataRow dataRow) { DataTable parentTable = dataRow.Table; int rowIndex = parentTable.Rows.IndexOf(dataRow); if (rowIndex < parentTable.Rows.Count - 1) { DataRow newDataRow = parentTable.NewRow(); for (int index = 0; index < dataRow.ItemArray.Length; index++) { newDataRow[index] = dataRow[index]; } parentTable.Rows.Remove(dataRow); parentTable.Rows.InsertAt(newDataRow, rowIndex + 1); dataRow = newDataRow; } } } } [/csharp]
Modify DataTable: insert data to database table
using System; using System.Data; using System.Data.SqlClient; class ModifyDataTable { static void Main() { string connString = "server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI"; string sql = @"select * from employee"; SqlConnection conn = new SqlConnection(connString); try { SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = new SqlCommand(sql, conn); DataSet ds = new DataSet(); da.Fill(ds, "employee"); DataTable dt = ds.Tables["employee"]; dt.Columns["firstname"].AllowDBNull = true; dt.Rows[0]["FirstName"] = "Joe"; DataRow newRow = dt.NewRow(); newRow["firstname"] = "Roy"; newRow["lastname"] = "Beatty"; dt.Rows.Add(newRow); foreach (DataRow row in dt.Rows) { Console.WriteLine("{0} {1}", row["firstname"].ToString().PadRight(15), row["lastname"].ToString().PadLeft(25)); } } catch(Exception e) { Console.WriteLine("Error: " + e); } finally { conn.Close(); } } }
Filter sort based on DataTableCollection
using System; using System.Data; using System.Data.SqlClient; class FilterSort { static void Main(string[] args) { string connString = "server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI"; string sql1 = @"select * from employee;"; string sql2 = @"select firstname, lastname from employee"; string sql = sql1 + sql2; SqlConnection conn = new SqlConnection(connString); try { SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = new SqlCommand(sql, conn); DataSet ds = new DataSet(); da.Fill(ds, "employee"); DataTableCollection dtc = ds.Tables; Console.WriteLine("Results from Customers table:"); Console.WriteLine("FirstName".PadRight(20) + "LastName".PadLeft(23) + " "); string fl = "FirstName = 'Z'"; string srt = "LastName asc"; foreach (DataRow row in dtc["Employee"].Select(fl, srt)) { Console.WriteLine( "{0} {1}", row["FirstName"].ToString().PadRight(25), row["LastName"]); } Console.WriteLine("Results from Products table:"); Console.WriteLine("FirstName".PadRight(20) + "LastName".PadLeft(21) + " "); foreach (DataRow row in dtc[1].Rows){ Console.WriteLine("{0} {1}", row["FirstName"].ToString().PadRight(25), row["LastName"]); } } catch(Exception e) { Console.WriteLine("Error: " + e); } finally { conn.Close(); } } }
illustrates how to specify and use a relationship between two DataTable objects
/* Mastering Visual C# .NET by Jason Price, Mike Gunderloy Publisher: Sybex; ISBN: 0782129110 */ /* Example23_6.cs illustrates how to specify and use a relationship between two DataTable objects */ using System; using System.Data; using System.Data.SqlClient; public class Example23_6 { public static void Main() { // formulate a string containing the details of the // database connection string connectionString = "server=localhost;database=Northwind;uid=sa;pwd=sa"; // create a SqlConnection object to connect to the // database, passing the connection string to the constructor SqlConnection mySqlConnection = new SqlConnection(connectionString); // formulate a string containing a SELECT statement to // retrieve a row from the Customers table string selectString = "SELECT CustomerID, CompanyName " + "FROM Customers " + "WHERE CustomerID = 'ALFKI'"; // create a SqlCommand object to hold the SELECT statement SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); // set the CommandText property of the SqlCommand object to // the SELECT string mySqlCommand.CommandText = selectString; // create a SqlDataAdapter object SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(); // set the SelectCommand property of the SqlAdapter object // to the SqlCommand object mySqlDataAdapter.SelectCommand = mySqlCommand; // create a DataSet object to store the results of // the SELECT statement DataSet myDataSet = new DataSet(); // open the database connection using the // Open() method of the SqlConnection object mySqlConnection.Open(); // use the Fill() method of the SqlDataAdapter object to // retrieve the rows from the database, storing the rows // in a DataTable named "Customers" mySqlDataAdapter.Fill(myDataSet, "Customers"); // formulate a string containing a SELECT statement to // retrieve the rows from the Orders table where the CustomerID // column is equal to ALFKI selectString = "SELECT OrderID, CustomerID " + "FROM Orders " + "WHERE CustomerID = 'ALFKI'"; // set the CommandText property of the SqlCommand object to // the SELECT string mySqlCommand.CommandText = selectString; // use the Fill() method of the SqlDataAdapter object to // retrieve the rows from the database, storing the rows // in a DataTable named "Orders" mySqlDataAdapter.Fill(myDataSet, "Orders"); // use the Add() method through the Relations property // to define a relationship between the Customers and // Orders DataTable objects myDataSet.Relations.Add( "Orders", myDataSet.Tables["Customers"].Columns["CustomerID"], myDataSet.Tables["Orders"].Columns["CustomerID"] ); // display the rows in the Customers and Orders DataTable objects, // using the GetChildRows() method to get the orders for the // customer DataTable customers = myDataSet.Tables["Customers"]; foreach (DataRow customer in customers.Rows) { Console.WriteLine("CustomerID = " + customer["CustomerID"]); Console.WriteLine("CompanyName = " + customer["CompanyName"]); DataRow[] orders = customer.GetChildRows("Orders"); Console.WriteLine("This customer placed the following orders:"); foreach (DataRow order in orders) { Console.WriteLine(" OrderID = " + order["OrderID"]); } } // close the database connection using the Close() method // of the SqlConnection object mySqlConnection.Close(); } }
illustrates the use of adding, modifying, and deleting a row in a DataTable object and synchronizing those changes with the
/* Mastering Visual C# .NET by Jason Price, Mike Gunderloy Publisher: Sybex; ISBN: 0782129110 */ /* Example23_3.cs illustrates the use of adding, modifying, and deleting a row in a DataTable object and synchronizing those changes with the database */ using System; using System.Data; using System.Data.SqlClient; public class Example23_3 { public static void DisplayDataTable(DataTable myDataTable) { // display the columns for each row in the DataTable, // using a DataRow object to access each row in the DataTable foreach (DataRow myDataRow in myDataTable.Rows) { Console.WriteLine("CustomerID = " + myDataRow["CustomerID"]); Console.WriteLine("CompanyName = " + myDataRow["CompanyName"]); Console.WriteLine("ContactName = " + myDataRow["ContactName"]); Console.WriteLine("Address = " + myDataRow["Address"]); } } public static void AddRow( DataTable myDataTable ) { Console.WriteLine(" Adding a new row with CustomerID of 'T1COM'"); // step 1: use the NewRow() method of the DataRow object to create // a new row in the DataTable DataRow myNewDataRow = myDataTable.NewRow(); // step 2: set the values for the columns of the new row myNewDataRow["CustomerID"] = "T1COM"; myNewDataRow["CompanyName"] = "T1 Company"; myNewDataRow["ContactName"] = "Jason Price"; myNewDataRow["Address"] = "1 Main Street"; // step 3: use the Add() method through the Rows property to add // the new DataRow to the DataTable myDataTable.Rows.Add(myNewDataRow); // step 4: use the AcceptChanges() method of the DataTable to commit // the changes myDataTable.AcceptChanges(); } public static void ModifyRow( DataTable myDataTable ) { Console.WriteLine(" Modifying the new row"); // step 1: set the PrimaryKey property for the DataTable object DataColumn[] myPrimaryKey = new DataColumn[1]; myPrimaryKey[0] = myDataTable.Columns["CustomerID"]; myDataTable.PrimaryKey = myPrimaryKey; // step 2: use the Find() method to locate the DataRow // in the DataTable using the primary key value DataRow myEditDataRow = myDataTable.Rows.Find("T1COM"); // step 3: change the column values myEditDataRow["CompanyName"] = "Widgets Inc."; myEditDataRow["ContactName"] = "John Smith"; myEditDataRow["Address"] = "1 Any Street"; // step 4: use the AcceptChanges() method of the DataTable to commit // the changes myDataTable.AcceptChanges(); Console.WriteLine("myEditDataRow.RowState = " + myEditDataRow.RowState); } public static void RemoveRow( DataTable myDataTable ) { Console.WriteLine(" Removing the new row"); // step 1: set the PrimaryKey property for the DataTable object DataColumn[] myPrimaryKey = new DataColumn[1]; myPrimaryKey[0] = myDataTable.Columns["CustomerID"]; myDataTable.PrimaryKey = myPrimaryKey; // step 2: use the Find() method to locate the DataRow DataRow myRemoveDataRow = myDataTable.Rows.Find("T1COM"); // step 3: use the Delete() method to remove the DataRow myRemoveDataRow.Delete(); // step 4: use the AcceptChanges() method of the DataTable to commit // the changes myDataTable.AcceptChanges(); } public static void Main() { // formulate a string containing the details of the // database connection string connectionString = "server=localhost;database=Northwind;uid=sa;pwd=sa"; // create a SqlConnection object to connect to the // database, passing the connection string to the constructor SqlConnection mySqlConnection = new SqlConnection(connectionString); // formulate a SELECT statement to retrieve the // CustomerID, CompanyName, ContactName, and Address // columns for the first row from the Customers table string selectString = "SELECT CustomerID, CompanyName, ContactName, Address " + "FROM Customers " + "WHERE CustomerID = 'ALFKI'"; // create a SqlCommand object to hold the SELECT statement SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); // set the CommandText property of the SqlCommand object to // the SELECT string mySqlCommand.CommandText = selectString; // create a SqlDataAdapter object SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(); // set the SelectCommand property of the SqlAdapter object // to the SqlCommand object mySqlDataAdapter.SelectCommand = mySqlCommand; // create a DataSet object to store the results of // the SELECT statement DataSet myDataSet = new DataSet(); // open the database connection using the // Open() method of the SqlConnection object mySqlConnection.Open(); // use the Fill() method of the SqlDataAdapter object to // retrieve the rows from the table, storing the rows locally // in a DataTable of the DataSet object Console.WriteLine("Retrieving a row from the Customers table"); mySqlDataAdapter.Fill(myDataSet, "Customers"); // get the DataTable object from the DataSet object DataTable myDataTable = myDataSet.Tables["Customers"]; // display the rows in the DataTable object DisplayDataTable(myDataTable); // add a new row AddRow(myDataTable); DisplayDataTable(myDataTable); // modify a row ModifyRow(myDataTable); DisplayDataTable(myDataTable); // remove a row RemoveRow(myDataTable); DisplayDataTable(myDataTable); // use the Fill() method of the SqlDataAdapter object // to synchronize the changes with the database mySqlDataAdapter.Fill(myDataSet, "Customers"); // close the database connection using the Close() method // of the SqlConnection object mySqlConnection.Close(); } }
Print DataTable
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
class Program {
static void Main(string[] args) {
DataSet carsInventoryDS = new DataSet(“Inventory”);
carsInventoryDS.ExtendedProperties[“TimeStamp”] = DateTime.Now;
carsInventoryDS.ExtendedProperties[“Company”] = “Name”;
DataColumn carIDColumn = new DataColumn(“CarID”, typeof(int));
carIDColumn.ReadOnly = true;
carIDColumn.Caption = “Car ID”;
carIDColumn.AllowDBNull = false;
carIDColumn.Unique = true;
carIDColumn.AutoIncrement = true;
carIDColumn.AutoIncrementSeed = 0;
carIDColumn.AutoIncrementStep = 1;
carIDColumn.ColumnMapping = MappingType.Attribute;
DataColumn carMakeColumn = new DataColumn(“Make”, typeof(string));
DataColumn carColorColumn = new DataColumn(“Color”, typeof(string));
DataColumn carPetNameColumn = new DataColumn(“PetName”, typeof(string));
carPetNameColumn.Caption = “Name”;
DataTable inventoryTable = new DataTable(“Inventory”);
inventoryTable.Columns.AddRange(new DataColumn[] { carIDColumn, carMakeColumn, carColorColumn, carPetNameColumn });
inventoryTable.PrimaryKey = new DataColumn[] { inventoryTable.Columns[0] };
DataRow carRow = inventoryTable.NewRow();
carRow[“Make”] = “B”;
carRow[“Color”] = “C”;
carRow[“PetName”] = “A”;
inventoryTable.Rows.Add(carRow);
carRow = inventoryTable.NewRow();
carRow[“Make”] = “S”;
carRow[“Color”] = “R”;
carRow[“PetName”] = “E”;
inventoryTable.Rows.Add(carRow);
carsInventoryDS.Tables.Add(inventoryTable);
PrintTable(carsInventoryDS.Tables[“Inventory”]);
}
private static void PrintTable(DataTable dt) {
DataTableReader dtReader = dt.CreateDataReader();
while (dtReader.Read()) {
for (int i = 0; i < dtReader.FieldCount; i++) {
Console.Write("{0} = {1} ",
dtReader.GetName(i).Trim(),
dtReader.GetValue(i).ToString().Trim());
}
Console.WriteLine();
}
dtReader.Close();
}
}
[/csharp]