Csharp/CSharp Tutorial/ADO.Net/DataColumn — различия между версиями

Материал из .Net Framework эксперт
Перейти к: навигация, поиск
м (1 версия)
 
(нет различий)

Текущая версия на 15:19, 26 мая 2010

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>