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>
