Csharp/C Sharp/Database ADO.net/DataTable
Содержание
- 1 Filter sort based on DataTableCollection
- 2 illustrates how to specify and use a relationship between two DataTable objects
- 3 illustrates the use of adding, modifying, and deleting a row in a DataTable object and synchronizing those changes with the
- 4 Modify DataTable: insert data to database table
- 5 Print DataTable
Filter sort based on DataTableCollection
<source lang="csharp"> 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) + "\n"); string fl = "FirstName = "Z""; string srt = "LastName asc"; foreach (DataRow row in dtc["Employee"].Select(fl, srt)) { Console.WriteLine( "{0}\t{1}", row["FirstName"].ToString().PadRight(25), row["LastName"]); } Console.WriteLine("Results from Products table:"); Console.WriteLine("FirstName".PadRight(20) + "LastName".PadLeft(21) + "\n"); foreach (DataRow row in dtc[1].Rows){ Console.WriteLine("{0}\t{1}", row["FirstName"].ToString().PadRight(25), row["LastName"]); } } catch(Exception e) { Console.WriteLine("Error: " + e); } finally { conn.Close(); } } }
</source>
illustrates how to specify and use a relationship between two DataTable objects
<source lang="csharp"> /* 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.rumandText = 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.rumandText = 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(); }
}
</source>
illustrates the use of adding, modifying, and deleting a row in a DataTable object and synchronizing those changes with the
<source lang="csharp"> /* 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("\nAdding 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("\nModifying 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("\nRemoving 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.rumandText = 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(); }
}
</source>
Modify DataTable: insert data to database table
<source lang="csharp"> 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(); } } }
</source>
Print DataTable
<source lang="csharp"> 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(); }
}
</source>