в 15:31, 26 мая 2010

2010-05-26T15:31:53Z

Admin: 1 версия

2010-05-26T12:18:58Z

1 версия

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

==Adding Data to MDF file with SqlDataAdapter==

<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(thisDataSet.Tables["Customers"].Rows.Count);
DataRow thisRow = thisDataSet.Tables["Customers"].NewRow();
thisRow["CustomerID"] = "1";
thisRow["CompanyName"] = "ABC Ltd.";
thisDataSet.Tables["Customers"].Rows.Add(thisRow);
Console.WriteLine(thisDataSet.Tables["Customers"].Rows.Count);
thisAdapter.Update(thisDataSet, "Customers");
thisConnection.Close();
}
}</source>

==Create a data adapter from select statement and connection string==

<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);
// Output the rows from the table
Console.WriteLine("---foreach loop over DataRowCollection (DataTable 1)---");
foreach (DataRow row in dt1.Rows)
{
Console.WriteLine(
"ContactID = {0}\tFirstName = {1}\tLastName = {2}",
row["ContactID"], row["FirstName"], row["LastName"]);
}
}
}</source>

==Delete command with parameters==

<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 qry = @"select * from employees where country = "UK"";
string del = @"delete from employees where employeeid = @employeeid";
SqlConnection conn = new SqlConnection(connString);
try
{
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = new SqlCommand(qry, conn);
DataSet ds = new DataSet();
da.Fill(ds, "employees");
DataTable dt = ds.Tables["employees"];
SqlCommand cmd = new SqlCommand(del, conn);
cmd.Parameters.Add("@employeeid",SqlDbType.Int,4,"employeeid");
string filt = @"firstname = "R" and lastname = "B"";
foreach (DataRow row in dt.Select(filt))
{
row.Delete();
}
da.DeleteCommand = cmd;
da.Update(ds, "employees");
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>

==Executing a SQL Server Scalar-Valued Function==

<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 1 value FROM SalesOrderDetail";
SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);
DataTable dt = new DataTable( );
da.Fill(dt);
foreach (DataRow row in dt.Rows)
Console.WriteLine("SalesOrderDetailID = {0}, LineTotal = {1}, " +
"ExtendedPrice = {2}", row["SalesOrderDetailID"],
row["LineTotal"], row["ExtendedPrice"]);
}
}</source>

==Executing a SQL Server Table-Valued Function==

<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 dbo.ufnGetContactInformation(10)";
SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);
DataTable dt = new DataTable( );
da.Fill(dt);
foreach (DataColumn col in dt.Columns)
{
Console.WriteLine("{0} = {1}",col.ColumnName, dt.Rows[0][col.Ordinal]);
}
}
}</source>

==Fill DataSet With SqlDataAdapter==

<source lang="csharp">using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.rumon;
class Program
{
static void Main(string[] args)
{
string cnStr = "Integrated Security = SSPI;Initial Catalog=YourDB;Data Source=(local)\\SQLEXPRESS";
DataSet ds = new DataSet("YourDB");
SqlDataAdapter dAdapt = new SqlDataAdapter("Select * From Inventory", cnStr);
DataTableMapping custMap = dAdapt.TableMappings.Add("Inventory", "Current Inventory");
custMap.ColumnMappings.Add("CarID", "Car ID");
custMap.ColumnMappings.Add("PetName", "Name of Car");
dAdapt.Fill(ds, "Inventory");
PrintDataSet(ds);
}
static void PrintDataSet(DataSet ds)
{
Console.WriteLine(ds.DataSetName);
foreach (System.Collections.DictionaryEntry de in ds.ExtendedProperties)
{
Console.WriteLine("Key = {0}, Value = {1}", de.Key, de.Value);
}
foreach (DataTable dt in ds.Tables)
{
Console.WriteLine(dt.TableName);
for (int curCol = 0; curCol < dt.Columns.Count; curCol++)
{
Console.Write(dt.Columns[curCol].ColumnName.Trim() + "\t");
}
PrintTable(dt);
}
}
private static void PrintTable(DataTable dt)
{
DataTableReader dtReader = dt.CreateDataReader();
while (dtReader.Read())
{
for (int i = 0; i < dtReader.FieldCount; i++)
{
Console.WriteLine(dtReader.GetValue(i).ToString().Trim());
}
}
dtReader.Close();
}
}</source>

==Insert command with parameters==

<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 qry = @"select * from employees where country = "UK"";
string ins = @"insert into employees(firstname,lastname,titleofcourtesy,city,country)
values(@firstname,@lastname,@titleofcourtesy,@city,@country)";
SqlConnection conn = new SqlConnection(connString);
try
{
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = new SqlCommand(qry, conn);
DataSet ds = new DataSet();
da.Fill(ds, "employees");
DataTable dt = ds.Tables["employees"];
DataRow newRow = dt.NewRow();
newRow["firstname"] = "R";
newRow["lastname"] = "B";
newRow["titleofcourtesy"] = "Sir";
newRow["city"] = "B";
newRow["country"] = "UK";
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"]);
}
SqlCommand cmd = new SqlCommand(ins, conn);
cmd.Parameters.Add("@firstname",SqlDbType.NVarChar, 10,"firstname");
cmd.Parameters.Add("@lastname",SqlDbType.NVarChar,20,"lastname");
cmd.Parameters.Add("@titleofcourtesy",SqlDbType.NVarChar,25,"titleofcourtesy");
cmd.Parameters.Add("@city",SqlDbType.NVarChar,15,"city");
cmd.Parameters.Add("@country",SqlDbType.NVarChar,15,"country");
da.InsertCommand = cmd;
da.Update(ds, "employees");
}
catch(Exception e)
{
Console.WriteLine("Error: " + e);
}
finally
{
conn.Close();
}
}
}</source>

==Persist Adds==

<source lang="csharp">using System;
using System.Data;
using System.Data.SqlClient;
class PersistAdds
{
static void Main(string[] args)
{
string connString = @"server = .\sqlexpress;integrated security = true;database = northwind";
string qry = @"select * from employees where country = "UK"";
string ins = @"insert into employees(firstname,lastname,titleofcourtesy,city,country)values(@firstname,@lastname,@titleofcourtesy,@city,@country)";
SqlConnection conn = new SqlConnection(connString);
try
{
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = new SqlCommand(qry, conn);
DataSet ds = new DataSet();
da.Fill(ds, "employees");
DataTable dt = ds.Tables["employees"];
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"]);
}
SqlCommand cmd = new SqlCommand(ins, conn);
cmd.Parameters.Add("@firstname",SqlDbType.NVarChar,10,"firstname");
cmd.Parameters.Add("@lastname",SqlDbType.NVarChar,20,"lastname");
cmd.Parameters.Add("@titleofcourtesy",SqlDbType.NVarChar,25,"titleofcourtesy");
cmd.Parameters.Add("@city",SqlDbType.NVarChar,15,"city");
cmd.Parameters.Add("@country",SqlDbType.NVarChar,15,"country");
da.InsertCommand = cmd;
da.Update(ds, "employees");
}
catch (Exception e)
{
Console.WriteLine("Error: " + e);
}
finally
{
conn.Close();
}
}
}</source>

==Persist Adds Builder==

<source lang="csharp">using System;
using System.Data;
using System.Data.SqlClient;
class PersistAddsBuilder
{
static void Main(string[] args)
{
string connString = @"server = .\sqlexpress;integrated security = true;database = northwind";
string qry = @"select * from employees where country = "UK"";
SqlConnection conn = new SqlConnection(connString);
try
{
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = new SqlCommand(qry, conn);
SqlCommandBuilder cb = new SqlCommandBuilder(da);
DataSet ds = new DataSet();
da.Fill(ds, "employees");
DataTable dt = ds.Tables["employees"];
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"]);
}
da.Update(ds, "employees");
}
catch (Exception e)
{
Console.WriteLine("Error: " + e);
}
finally
{
conn.Close();
}
}
}</source>

==Persist Changes==

<source lang="csharp">using System;
using System.Data;
using System.Data.SqlClient;
class PersistChanges
{
static void Main(string[] args)
{
string connString = @"server = .\sqlexpress;integrated security = true;database = northwind";
string qry = @"select * from employees where country = "UK"";
string upd = @"update employees set city = @city where employeeid = @employeeid";
SqlConnection conn = new SqlConnection(connString);
try
{
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = new SqlCommand(qry, conn);
DataSet ds = new DataSet();
da.Fill(ds, "employees");
DataTable dt = ds.Tables["employees"];
dt.Rows[0]["city"] = "Wilmington";
foreach (DataRow row in dt.Rows)
{
Console.WriteLine(row["firstname"]);
Console.WriteLine(row["lastname"]);
Console.WriteLine(row["city"]);
}
SqlCommand cmd = new SqlCommand(upd, conn);
cmd.Parameters.Add("@city",SqlDbType.NVarChar,15,"city");
SqlParameter parm =cmd.Parameters.Add("@employeeid",SqlDbType.Int,4,"employeeid");
parm.SourceVersion = DataRowVersion.Original;
da.UpdateCommand = cmd;
da.Update(ds, "employees");
}
catch (Exception e)
{
Console.WriteLine("Error: " + e);
}
finally
{
conn.Close();
}
}
}</source>

==Persist Deletes==

<source lang="csharp">using System;
using System.Data;
using System.Data.SqlClient;
class PersistDeletes
{
static void Main(string[] args)
{
string connString = @"server = .\sqlexpress;integrated security = true;database = northwind";
string qry = @"select * from employees where country = "UK"";
string del = @"delete from employees where employeeid = @employeeid";
SqlConnection conn = new SqlConnection(connString);
try
{
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = new SqlCommand(qry, conn);
DataSet ds = new DataSet();
da.Fill(ds, "employees");
DataTable dt = ds.Tables["employees"];
SqlCommand cmd = new SqlCommand(del, conn);
cmd.Parameters.Add("@employeeid",SqlDbType.Int,4,"employeeid");
string filt = @"firstname = "Roy" and lastname = "Beatty"";
foreach (DataRow row in dt.Select(filt))
{
row.Delete();
}
da.DeleteCommand = cmd;
da.Update(ds, "employees");
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>

==Update command with parameters==

<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 qry = @"select * from employees where country = "UK"";
string upd = @"update employees set city = @city where employeeid = @employeeid";
SqlConnection conn = new SqlConnection(connString);
try
{
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = new SqlCommand(qry, conn);
DataSet ds = new DataSet();
da.Fill(ds, "employees");
DataTable dt = ds.Tables["employees"];
dt.Rows[0]["city"] = "W";
foreach (DataRow row in dt.Rows)
{
Console.WriteLine("{0} {1} {2}",
row["firstname"].ToString().PadRight(15),
row["lastname"].ToString().PadLeft(25),
row["city"]);
}
SqlCommand cmd = new SqlCommand(upd, conn);
cmd.Parameters.Add("@city",SqlDbType.NVarChar,15,"city");
SqlParameter parm =cmd.Parameters.Add("@employeeid",SqlDbType.Int,4,"employeeid");
parm.SourceVersion = DataRowVersion.Original;
da.UpdateCommand = cmd;
da.Update(ds, "employees");
}
catch(Exception e)
{
Console.WriteLine("Error: " + e);
}
finally
{
conn.Close();
}
}
}</source>

==Update SqlDataAdapter with DataSet==

<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 qry = @"select * from employees where country = "UK"";
SqlConnection conn = new SqlConnection(connString);
try
{
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = new SqlCommand(qry, conn);
SqlCommandBuilder cb = new SqlCommandBuilder(da);
DataSet ds = new DataSet();
da.Fill(ds, "employees");
DataTable dt = ds.Tables["employees"];
DataRow newRow = dt.NewRow();
newRow["firstname"] = "R";
newRow["lastname"] = "B";
newRow["titleofcourtesy"] = "Sir";
newRow["city"] = "B";
newRow["country"] = "UK";
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"]);
}
da.Update(ds, "employees");
}
catch(Exception e)
{
Console.WriteLine("Error: " + e);
}
finally
{
conn.Close();
}
}
}</source>

==Updating Data with SqlDataAdapter and DataSet==

<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("name before change: {0}",thisDataSet.Tables["Customers"].Rows[9]["CompanyName"]);
thisDataSet.Tables["Customers"].Rows[9]["CompanyName"] = "Acme, Inc.";
thisAdapter.Update(thisDataSet, "Customers");
Console.WriteLine("name after change: {0}",thisDataSet.Tables["Customers"].Rows[9]["CompanyName"]);
thisConnection.Close();
}
}</source>

==Use SqlDataAdapter to deal with the select statement==

<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);
DataSet ds = new DataSet();
da.Fill(ds, "products");
DataTable dt = ds.Tables["products"];
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>

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

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

в 15:31, 26 мая 2010

Admin: 1 версия