в 15:31, 26 мая 2010

2010-05-26T15:31:53Z

Admin: 1 версия

2010-05-26T12:19:00Z

1 версия

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

==Accessing Data Values in a DataTable or DataSet==

<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 ContactID, FirstName, LastName FROM Person.Contact";
SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);
DataTable dt = new DataTable( );
da.Fill(dt);
for (int i = 0; i < 5; i++)
{
DataRow row = dt.Rows[i];
Console.WriteLine(i);
Console.WriteLine(row[0]);
Console.WriteLine(row["FirstName"]);
Console.WriteLine(row[2, DataRowVersion.Default]);
}

}
}</source>

==Accessing FirstName value in row 3 directly==

<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 ContactID, FirstName, LastName FROM Person.Contact";
SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);
DataTable dt = new DataTable( );
da.Fill(dt);
Console.WriteLine("FirstName = {0}", dt.Rows[2][1]);
Console.WriteLine("FirstName = {0}", dt.Rows[2]["FirstName"]);
Console.WriteLine("FirstName = {0}",dt.Rows[2]["FirstName", DataRowVersion.Default]);
Console.WriteLine("FirstName = {0}", dt.Rows[2][dt.Columns[1]]);
Console.WriteLine("FirstName = {0}",dt.Rows[2][dt.Columns["FirstName"]]);
Console.WriteLine("FirstName = {0}",dt.Rows[2].Field<string>(1));
Console.WriteLine("FirstName = {0}",dt.Rows[2].Field<string>("FirstName"));
Console.WriteLine("FirstName = {0}",dt.Rows[2].Field<string>("FirstName", DataRowVersion.Default));
Console.WriteLine("FirstName = {0}",dt.Rows[2].Field<string>(dt.Columns[1]));
Console.WriteLine("FirstName = {0}",dt.Rows[2].Field<string>(dt.Columns["FirstName"]));
}
}</source>

==Adding a Calculated Column to a DataTable==

<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(row["SalesOrderDetailID"]);
}
}</source>

==Adding a Column to a Child DataTable That Displays Data from the Parent 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 * FROM Sales.SalesOrderHeader;SELECT * FROM Sales.SalesOrderDetail;";
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);
da.TableMappings.Add("Table", "SalesOrderHeader");
da.TableMappings.Add("Table1", "SalesOrderDetail");
da.Fill(ds);
DataRelation dr = new DataRelation("SalesOrderHeader_SalesOrderDetail",
ds.Tables["SalesOrderHeader"].Columns["SalesOrderID"],
ds.Tables["SalesOrderDetail"].Columns["SalesOrderID"]);
ds.Relations.Add(dr);
ds.Tables["SalesOrderDetail"].Columns.Add("CustomerID", typeof(int),"Parent(SalesOrderHeader_SalesOrderDetail).CustomerID");
}
}</source>

==Adding a Column to a Parent DataTable That Aggregates a Child Table"s Column Values==

<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 * FROM Sales.SalesOrderHeader;SELECT * FROM Sales.SalesOrderDetail;";
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);
da.TableMappings.Add("Table", "SalesOrderHeader");
da.TableMappings.Add("Table1", "SalesOrderDetail");
da.Fill(ds);
// Relate the Header and Order tables in the DataSet
DataRelation dr = new DataRelation("SalesOrderHeader_SalesOrderDetail",
ds.Tables["SalesOrderHeader"].Columns["SalesOrderID"],
ds.Tables["SalesOrderDetail"].Columns["SalesOrderID"]);
ds.Relations.Add(dr);
ds.Tables["SalesOrderHeader"].Columns.Add("SumDetailLineTotal",typeof(decimal), "SUM(Child.LineTotal)");
for (int i = 0; i < 2; i++){
DataRow rowHeader = ds.Tables["SalesOrderHeader"].Rows[i];
Console.WriteLine(rowHeader["SalesOrderID"]);
Console.WriteLine(rowHeader["CustomerID"]);
Console.WriteLine(rowHeader["SumDetailLineTotal"]);
foreach (DataRow rowDetail in rowHeader.GetChildRows(dr))
{
Console.WriteLine(rowDetail["SalesOrderID"]);
Console.WriteLine(rowDetail["SalesOrderDetailID"]);
Console.WriteLine(rowDetail["LineTotal"]);
}
}
}
}</source>

==Adding Columns to DataTable==

<source lang="csharp">using System;
using System.Data;
class Program
{
static void Main(string[] args)
{
DataTable dt1 = new DataTable("Table-1");
DataColumn pkCol = dt1.Columns.Add("Id", typeof(int));
dt1.Columns.Add("Field1", typeof(string)).MaxLength = 50;
dt1.PrimaryKey = new DataColumn[] { pkCol };
DataTable dt2 = new DataTable("Table-2");
dt2.Columns.Add("Id", typeof(int));
dt2.Columns.Add("Field1", typeof(string)).MaxLength = 50;
UniqueConstraint uc = new UniqueConstraint("PrimaryKey-2", dt2.Columns["Id"], true);
dt2.Constraints.Add(uc);
DataTable dt3 = new DataTable("Table-3");
dt3.Columns.Add("Id1", typeof(int));
dt3.Columns.Add("Id2", typeof(int));
dt3.Columns.Add("Field1", typeof(string)).MaxLength = 50;
dt3.Constraints.Add("PrimaryKey-3",new DataColumn[] { dt3.Columns["Id1"], dt3.Columns["Id2"] },true);
}
}</source>

==Adding Constraint to DataTable==

<source lang="csharp">using System;
using System.Data;
class Program
{
static void Main(string[] args)
{
DataTable dt1 = new DataTable("Table-1");
DataColumn pkCol = dt1.Columns.Add("Id", typeof(int));
dt1.Columns.Add("Field1", typeof(string)).MaxLength = 50;
dt1.PrimaryKey = new DataColumn[] { pkCol };
DataTable dt2 = new DataTable("Table-2");
dt2.Columns.Add("Id", typeof(int));
dt2.Columns.Add("Field1", typeof(string)).MaxLength = 50;
UniqueConstraint uc = new UniqueConstraint("PrimaryKey-2", dt2.Columns["Id"], true);
dt2.Constraints.Add(uc);
DataTable dt3 = new DataTable("Table-3");
dt3.Columns.Add("Id1", typeof(int));
dt3.Columns.Add("Id2", typeof(int));
dt3.Columns.Add("Field1", typeof(string)).MaxLength = 50;
dt3.Constraints.Add("PrimaryKey-3",new DataColumn[] { dt3.Columns["Id1"], dt3.Columns["Id2"] },true);
}
}</source>

==Add Row to DataTable==

<source lang="csharp">using System;
using System.Data;
using System.Data.SqlClient;
class ModifyDataTable
{
static void Main(string[] args)
{
string connString = @"server = .\sqlexpress;integrated security = true;database = northwind";
string sql = @"select * from employee where country = "UK"";
SqlConnection conn = new SqlConnection(connString);
try
{
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = new SqlCommand(sql, conn);
DataSet ds = new DataSet();
da.Fill(ds, "employees");
DataTable dt = ds.Tables["employees"];
dt.Columns["firstname"].AllowDBNull = true;
dt.Rows[0]["city"] = "w";
DataRow newRow = dt.NewRow();
newRow["firstname"] = "R";
newRow["lastname"] = "B";
newRow["titleofcourtesy"] = "Sir";
newRow["city"] = "B";
newRow["country"] = "USA";
dt.Rows.Add(newRow);
foreach (DataRow row in dt.Rows)
{
Console.WriteLine("{0} {1} {2}",
row["firstname"].ToString().PadRight(15),
row["lastname"].ToString().PadLeft(25),
row["city"]);
}
}
catch(Exception e)
{
Console.WriteLine("Error: " + e);
}
finally
{
conn.Close();
}
}
}</source>

==Append columns to DataTable==

<source lang="csharp">using System;
using System.Data;
using System.IO;
using System.Collections.Generic;
using System.Text;
class Program
{
static DataTable dt;
static void Main(string[] args)
{
dt = new DataTable("Employees");
dt.ReadXml("Employees.xml");
Console.WriteLine("{0} Employees Found", dt.Rows.Count);
foreach (DataRow row in dt.Rows)
{
Console.WriteLine("[{0}] {1} {2}", row["EmployeeID"], row["FirstName"], row["LastName"]);
}
//dt.Columns.Add("EmployeeID", typeof(int));
//dt.Columns.Add("FirstName", typeof(string));
// dt.Columns.Add("LastName", typeof(string));
// dt.Rows.Add(new object[] { 1, "A", "H" });
// dt.Rows.Add(new object[] { 2, "J", "D" });
string[] cust = "a b c ".Split(" ");
dt.Rows.Add(new object[] { dt.Rows.Count + 1, cust[0], cust[1] });
dt.WriteXml("Employees.xml", XmlWriteMode.WriteSchema);

}
}</source>

==Creating a DataColumn and Adding It to a DataTable==

<source lang="csharp">using System;
using System.Data;
class Program
{
static void Main(string[] args)
{
DataTable dt = new DataTable();
DataColumn col1 = dt.Columns.Add();
col1.ColumnName= "First Column";
col1.DataType = typeof(int);
col1.DefaultValue = 0;
col1.Unique = true;
col1.AllowDBNull = false;
DataColumn col2 = new DataColumn();
col2.ColumnName = "Second Column";
col2.DataType = typeof(string);
col2.MaxLength = 50;
dt.Columns.Add(col2);
dt.Columns.Add("Column-3", typeof(string)).MaxLength = 50;
DataColumn col4 = new DataColumn("Column-4");
DataColumn col5 = new DataColumn("Column-5", typeof(int));
dt.Columns.AddRange(new DataColumn[] { col4, col5 });
Console.WriteLine("DataTable has {0} DataColumns named:",dt.Columns.Count);
foreach (DataColumn col in dt.Columns)
Console.WriteLine("\t{0}", col.ColumnName);
}
}</source>

==Creating a DataTable and Adding It to a DataSet==

<source lang="csharp">using System;
using System.Data;
class Program
{
static void Main(string[] args)
{
DataSet ds = new DataSet();
DataTable dt1 = ds.Tables.Add("Table-1");
DataTable dt2 = new DataTable("Table-2");
ds.Tables.Add(dt2);
DataTable dt3 = new DataTable("Table-3");
DataTable dt4 = new DataTable("Table-4");
ds.Tables.AddRange(new DataTable[] { dt3, dt4 });
Console.WriteLine("DataSet has {0} DataTables named: ",ds.Tables.Count);
foreach (DataTable dt in ds.Tables)
Console.WriteLine("\t{0}", dt.TableName);
}
}</source>

==Creating a Unique Constraint==

<source lang="csharp">using System;
using System.Data;
class Program
{
static void Main(string[] args)
{
DataTable dt = new DataTable("Table-1");
dt.Columns.Add("Id", typeof(int));
dt.Columns.Add("Field1", typeof(string)).MaxLength = 50;
UniqueConstraint uc1 = new UniqueConstraint("UniqueConstraint", dt.Columns["Field1"]);
dt.Constraints.Add(uc1);
try
{
AddRow(dt, 1, "Value 1");
AddRow(dt, 2, "Value 1");
AddRow(dt, 3, "Value 1");
}
catch (Exception ex)
{
Console.WriteLine("Error: {0}", ex.Message);
}
}
private static void AddRow(DataTable dt, int id, string field1)
{
Console.WriteLine("\nAdding row: {0}, {1}", id, field1);
dt.Rows.Add(new object[] { id, field1 });
Console.WriteLine("Row added.");
}
}</source>

==Creating Single- and Multi-Column Primary Keys==

<source lang="csharp">using System;
using System.Data;
class Program
{
static void Main(string[] args)
{
DataTable dt1 = new DataTable("Table-1");
DataColumn pkCol = dt1.Columns.Add("Id", typeof(int));
dt1.Columns.Add("Field1", typeof(string)).MaxLength = 50;
dt1.PrimaryKey = new DataColumn[] { pkCol };
DataTable dt2 = new DataTable("Table-2");
dt2.Columns.Add("Id", typeof(int));
dt2.Columns.Add("Field1", typeof(string)).MaxLength = 50;
UniqueConstraint uc = new UniqueConstraint("PrimaryKey-2", dt2.Columns["Id"], true);
dt2.Constraints.Add(uc);
DataTable dt3 = new DataTable("Table-3");
dt3.Columns.Add("Id1", typeof(int));
dt3.Columns.Add("Id2", typeof(int));
dt3.Columns.Add("Field1", typeof(string)).MaxLength = 50;
dt3.Constraints.Add("PrimaryKey-3",new DataColumn[] { dt3.Columns["Id1"], dt3.Columns["Id2"] },true);
}
}</source>

==Filter Sort==

<source lang="csharp">using System;
using System.Data;
using System.Data.SqlClient;
class FilterSort{
static void Main(string[] args)
{
string connString = @"server = .\sqlexpress;integrated security = true;database = northwind";
string sql1 = @"select * from customers";
string sql2 = @"select * from products where unitprice < 10";
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, "customers");
DataTableCollection dtc = ds.Tables;
string fl = "country = "Germany"";
string srt = "companyname asc";
foreach (DataRow row in dtc["customers"].Select(fl, srt))
{
Console.WriteLine(row["CompanyName"]);
Console.WriteLine(row["ContactName"]);
}
foreach (DataRow row in dtc[1].Rows)
{
Console.WriteLine(row["productname"]);
Console.WriteLine(row["unitprice"]);
}
}
catch (Exception e)
{
Console.WriteLine("Error: " + e);
}
finally
{
conn.Close();
} Console.ReadLine();
}
}</source>

==Loop through DataTable by DataRow==

<source lang="csharp">using System;
using System.Data;
using System.Data.SqlClient;
class MainClass
{
static void Main(string[] args)
{
string connString = @"server = .\sqlexpress;integrated security = true;database = northwind";
string sql = @"select productname,unitprice from products where unitprice < 20";
SqlConnection conn = new SqlConnection(connString);
try
{
conn.Open();
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
DataTable dt = new DataTable();
da.Fill(dt);
foreach (DataRow row in dt.Rows)
{
foreach (DataColumn col in dt.Columns)
Console.WriteLine(row[col]);
}
}
catch(Exception e)
{
Console.WriteLine("Error: " + e);
}
finally
{
conn.Close();
}
}
}</source>

==Loop through the rows in DataTable==

<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 ContactID, FirstName, LastName FROM Person.Contact";
SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);
DataTable dt = new DataTable( );
da.Fill(dt);
for (int i = 0; i < 5; i++)
{
DataRow row = dt.Rows[i];
Console.WriteLine(i);
Console.WriteLine(row[0]);
Console.WriteLine(row["FirstName"]);
Console.WriteLine(row[2, DataRowVersion.Default]);
}

}
}</source>

==Modify DataTable==

<source lang="csharp">using System;
using System.Data;
using System.Data.SqlClient;
class ModifyDataTable
{
static void Main(string[] args)
{
string connString = @"server = .\sqlexpress;integrated security = true;database = northwind";
string sql = @"select * from employees where country = "UK"";
SqlConnection conn = new SqlConnection(connString);
try
{
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = new SqlCommand(sql, conn);
DataSet ds = new DataSet();
da.Fill(ds, "employees");
DataTable dt = ds.Tables["employees"];
dt.Columns["firstname"].AllowDBNull = true;
dt.Rows[0]["city"] = "Wilmington";
DataRow newRow = dt.NewRow();
newRow["firstname"] = "Roy";
newRow["lastname"] = "Beatty";
newRow["titleofcourtesy"] = "Sir";
newRow["city"] = "Birmingham";
newRow["country"] = "UK";
dt.Rows.Add(newRow);
foreach (DataRow row in dt.Rows)
{
Console.WriteLine(row["firstname"]);
Console.WriteLine(row["lastname"]);
Console.WriteLine(row["city"]);
}
}
catch (Exception e)
{
Console.WriteLine("Error: " + e);
}
finally
{
conn.Close();
}
}
}</source>

==Output Constraint Properties==

<source lang="csharp">using System;
using System.Data;
class Program
{
static void Main(string[] args)
{
DataTable dt = new DataTable("Table-1");
dt.Columns.Add("Id", typeof(int));
dt.Columns.Add("Field1", typeof(string)).MaxLength = 50;
UniqueConstraint uc1 = new UniqueConstraint("UniqueConstraint", dt.Columns["Field1"]);
dt.Constraints.Add(uc1);
OutputConstraintProperties(dt);
}
private static void OutputConstraintProperties(DataTable dt)
{
Console.WriteLine(dt.TableName);
Console.WriteLine(dt.Constraints[0].ConstraintName);
Console.WriteLine(((UniqueConstraint)dt.Constraints[0]).IsPrimaryKey);
foreach (DataColumn col in ((UniqueConstraint)dt.Constraints[0]).Columns)
{
Console.WriteLine(col.ColumnName);
}
}
}</source>

==Output the rows from the DataTable with foreach loop over DataRowCollection==

<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 ContactID, FirstName, LastName FROM Person.Contact";
SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);

DataTable dt1 = new DataTable();
da.Fill(dt1);
foreach (DataRow row in dt1.Rows)
{
Console.WriteLine(
"ContactID = {0}\tFirstName = {1}\tLastName = {2}",
row["ContactID"], row["FirstName"], row["LastName"]);
}
}
}</source>

==Pop DataTable==

<source lang="csharp">using System;
using System.Data;
using System.Data.SqlClient;
class PopDataTable
{
static void Main(string[] args)
{
string connString = @"server = .\sqlexpress;integrated security = true;database = northwind";
string sql = @"select productname,unitprice from products where unitprice < 20";
SqlConnection conn = new SqlConnection(connString);
try{
conn.Open();
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
DataTable dt = new DataTable();
da.Fill(dt);
foreach (DataRow row in dt.Rows)
{
foreach (DataColumn col in dt.Columns)
Console.WriteLine(row[col]);
}
}
catch (Exception e)
{
Console.WriteLine("Error: " + e);
}
finally
{
conn.Close();
}
}
}</source>

==Retrieves a schema table for a query==

<source lang="csharp">using System;
using System.Data;
using System.Data.SqlClient;
public class GetSchema
{
public static void Main()
{
string connectionString = "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI";
string SQL = "SELECT * FROM CUSTOMERS";
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(SQL, con);
SqlDataReader r;
DataTable schema;
con.Open();
r = cmd.ExecuteReader();
schema = r.GetSchemaTable();
con.Close();
foreach (DataRow row in schema.Rows)
{
foreach (DataColumn col in schema.Columns)
{
Console.WriteLine(col.ColumnName + " = " + row[col]);
}
}
}
}</source>

==Retrieving a Result Set Using a DataTable or a DataSet==

<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 FirstName, LastName FROM Person.Contact";
SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);
DataTable dt = new DataTable( );
da.Fill(dt);
foreach (DataRow row in dt.Rows)
Console.WriteLine("{0} {1}", row[0], row["LastName"]);

DataSet ds = new DataSet( );
da.Fill(ds, "Contact");
Console.WriteLine(ds.Tables.Count);
Console.WriteLine(ds.Tables[0].TableName);
foreach (DataRow row in ds.Tables["Contact"].Rows)
Console.WriteLine("{0} {1}", row[0], row[1]);
}
}</source>

==Use DataTable to insert a Row==

<source lang="csharp">using System;
using System.Drawing;
using System.Collections;
using System.ruponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
public class MainClass {
[STAThread]
static void Main()
{
string ConnectionString ="server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;";
SqlConnection conn = new SqlConnection(ConnectionString);
conn.Open();
SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM Employee ORDER BY ID", conn);
SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
// Create a dataset object
DataSet ds = new DataSet("EmployeeSet");
adapter.Fill(ds, "Employee");
// Create a data table object and add a new row
DataTable EmployeeTable = ds.Tables["Employee"];
DataRow row = EmployeeTable.NewRow();
row["FirstName"] = "R";
row["LastName"] = "D";
row["ID"] = "10";
EmployeeTable.Rows.Add(row);
// Update data adapter
adapter.Update(ds, "Employee");
Console.WriteLine(row["FirstName"].ToString().Trim() + " " + row["LastName"].ToString().Trim() + " added to Employees");
}
}</source>

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

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

в 15:31, 26 мая 2010

Admin: 1 версия