Csharp/CSharp Tutorial/ADO.Net/DataColumn
Содержание
Add an expression column to the table
<source lang="csharp">using System; using System.Data; using System.Data.SqlClient;
class Program { static void Main(string[] args) { string sqlConnectString = "Data Source=(local);Integrated security=SSPI;Initial Catalog=AdventureWorks;"; string sqlSelect = "SELECT TOP 5 * FROM Sales.SalesOrderDetail"; SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString); DataTable dt = new DataTable(); da.Fill(dt); dt.Columns.Add(new DataColumn("ExtendedPrice", typeof(Decimal),"UnitPrice * (1 - UnitPriceDiscount) * OrderQty")); foreach (DataRow row in dt.Rows) Console.WriteLine("ID = {0}, UnitPrice = {1}, " + "Disc = {2}, Qty = {3}, Total = {4}", row["SalesOrderDetailID"], row["UnitPrice"], row["UnitPriceDiscount"], row["OrderQty"], row["ExtendedPrice"]); } }</source>
Creating an Autoincrementing Primary Key
<source lang="csharp">using System; using System.Data;
class Program { static void Main(string[] args) { DataTable dt = new DataTable(); DataColumn pkCol = dt.Columns.Add("Id", typeof(int)); dt.Columns.Add("Field1", typeof(string)).MaxLength = 50; dt.PrimaryKey = new DataColumn[] {dt.Columns["Id"]}; pkCol.AutoIncrement = true; pkCol.AutoIncrementSeed = 100; pkCol.AutoIncrementStep = 10; for (int i = 1; i <= 5; i++) dt.Rows.Add(new object[] {null, "Value " + i }); foreach (DataRow row in dt.Rows) Console.WriteLine("Id = {0}\tField1 = {1}",row["Id"], row["Field1"]); } }</source>
Find data by primary key, if not found add to database
<source lang="csharp">using System; using System.Data; using System.Data.SqlClient; using System.Collections.Generic; using System.Text;
class Program { static void Main(string[] args) { SqlConnection thisConnection = new SqlConnection( @"Data Source=.\SQLEXPRESS;" + @"AttachDbFilename="NORTHWND.MDF";" + @"Integrated Security=True;Connect Timeout=30;User Instance=true"); SqlDataAdapter thisAdapter = new SqlDataAdapter("SELECT CustomerID, CompanyName FROM Customers", thisConnection); SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter); DataSet thisDataSet = new DataSet(); thisAdapter.Fill(thisDataSet, "Customers"); Console.WriteLine("# rows before change: {0}",thisDataSet.Tables["Customers"].Rows.Count); DataColumn[] keys = new DataColumn[1]; keys[0] = thisDataSet.Tables["Customers"].Columns["CustomerID"]; thisDataSet.Tables["Customers"].PrimaryKey = keys; DataRow findRow = thisDataSet.Tables["Customers"].Rows.Find("1"); if (findRow == null) { DataRow thisRow = thisDataSet.Tables["Customers"].NewRow(); thisRow["CustomerID"] = "1"; thisRow["CompanyName"] = "Abc Ltd."; thisDataSet.Tables["Customers"].Rows.Add(thisRow); if ((findRow = thisDataSet.Tables["Customers"].Rows.Find("1")) != null) { Console.WriteLine("success"); } } thisAdapter.Update(thisDataSet, "Customers"); Console.WriteLine(thisDataSet.Tables["Customers"].Rows.Count); thisConnection.Close(); } }</source>
Output DataSet as Binary file
<source lang="csharp">using System; using System.Collections.Generic; using System.Text; using System.Data; using System.IO; using System.Runtime.Serialization.Formatters.Binary;
class Program { static void Main(string[] args) { DataSet carsInventoryDS = new DataSet("Car"); carsInventoryDS.ExtendedProperties["TimeStamp"] = DateTime.Now; carsInventoryDS.ExtendedProperties["DataSetID"] = Guid.NewGuid(); carsInventoryDS.ExtendedProperties["Company"] = "Training"; DataColumn carIDColumn = new DataColumn("CarID", typeof(int)); carIDColumn.Caption = "Car ID"; carIDColumn.ReadOnly = true; carIDColumn.AllowDBNull = false; carIDColumn.Unique = true; carIDColumn.AutoIncrement = true; carIDColumn.AutoIncrementSeed = 0; carIDColumn.AutoIncrementStep = 1; DataColumn carMakeColumn = new DataColumn("Make", typeof(string)); DataColumn carColorColumn = new DataColumn("Color", typeof(string)); DataColumn carPetNameColumn = new DataColumn("PetName", typeof(string)); carPetNameColumn.Caption = "Pet Name"; DataTable inventoryTable = new DataTable("Inventory"); inventoryTable.Columns.AddRange(new DataColumn[] { carIDColumn, carMakeColumn, carColorColumn, carPetNameColumn }); DataRow carRow = inventoryTable.NewRow(); carRow["Make"] = "BMW"; carRow["Color"] = "Black"; carRow["PetName"] = "Hamlet"; inventoryTable.Rows.Add(carRow); carRow = inventoryTable.NewRow(); carRow[1] = "A"; carRow[2] = "B"; carRow[3] = "C"; inventoryTable.Rows.Add(carRow); inventoryTable.PrimaryKey = new DataColumn[] { inventoryTable.Columns[0] }; carsInventoryDS.Tables.Add(inventoryTable); carsInventoryDS.RemotingFormat = SerializationFormat.Binary; FileStream fs = new FileStream("BinaryCars.bin", FileMode.Create); BinaryFormatter bFormat = new BinaryFormatter(); bFormat.Serialize(fs, carsInventoryDS); fs.Close(); carsInventoryDS.Clear(); fs = new FileStream("BinaryCars.bin", FileMode.Open); DataSet data = (DataSet)bFormat.Deserialize(fs); } }</source>
Set auto increment, seed and step for primary key column
<source lang="csharp">using System; using System.Data;
class Program { static void Main(string[] args) { DataTable dt = new DataTable(); DataColumn pkCol = dt.Columns.Add("Id", typeof(int)); dt.Columns.Add("Field1", typeof(string)).MaxLength = 50; dt.PrimaryKey = new DataColumn[] {dt.Columns["Id"]}; pkCol.AutoIncrement = true; pkCol.AutoIncrementSeed = 100; pkCol.AutoIncrementStep = 10; for (int i = 1; i <= 5; i++) dt.Rows.Add(new object[] {null, "Value " + i }); foreach (DataRow row in dt.Rows) Console.WriteLine("Id = {0}\tField1 = {1}",row["Id"], row["Field1"]); } }</source>
Use a relationship with a calculated column
<source lang="csharp">using System; using System.Data; using System.Data.SqlClient; public class CalculatedColumn {
public static void Main() { string connectionString = "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI"; string SQL = "SELECT * FROM Categories"; SqlConnection con = new SqlConnection(connectionString); SqlCommand cmd = new SqlCommand(SQL, con); SqlDataAdapter adapter = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); con.Open(); adapter.Fill(ds, "Categories"); cmd.rumandText = "SELECT * FROM Products"; adapter.Fill(ds, "Products"); con.Close(); DataColumn parentCol = ds.Tables["Categories"].Columns["CategoryID"]; DataColumn childCol = ds.Tables["Products"].Columns["CategoryID"]; DataRelation relation = new DataRelation("Cat_Prod", parentCol,childCol); ds.Relations.Add(relation); ds.Tables["Categories"].Columns.Add("AveragePrice", typeof(Decimal), "AVG(Child(Cat_Prod).UnitPrice)"); ds.Tables["Categories"].Columns.Add("TotalPrice", typeof(Decimal), "SUM(Child(Cat_Prod).UnitPrice)"); foreach (DataRow row in ds.Tables["Categories"].Rows) { Console.WriteLine(row["CategoryName"]); Console.WriteLine(row["AveragePrice"]); Console.WriteLine(row["TotalPrice"]); } }
}</source>