в 15:31, 26 мая 2010

2010-05-26T15:31:53Z

Admin: 1 версия

2010-05-26T12:19:10Z

1 версия

Новая страница

==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>

← Предыдущая	Версия 15:31, 26 мая 2010
(нет различий)

Csharp/CSharp Tutorial/ADO.Net/DataColumn - История изменений

в 15:31, 26 мая 2010

Admin: 1 версия