в 15:31, 26 мая 2010

2010-05-26T15:31:35Z

Admin: 1 версия

2010-05-26T12:10:55Z

1 версия

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

==new SqlDataAdapter==

<source lang="csharp">
using System;
using System.Data;
using System.Data.rumon;
using System.Data.SqlClient;
class MainClass
{
static void Main(string[] args)
{
SqlConnection SConn = new SqlConnection("Data Source=(local); Initial Catalog = CaseManager; Integrated Security=true");
SqlDataAdapter da = new SqlDataAdapter("SELECT ID, FirstName, LastName FROM Employee", SConn);
DataSet ds = new DataSet();
DataTableMapping custMap = da.TableMappings.Add("Employee", "MyDatabase");
custMap.ColumnMappings.Add("ID", "Number");
custMap.ColumnMappings.Add("FirstName", "first name");
custMap.ColumnMappings.Add("LastName", "last name");
da.Fill(ds);
}
}

</source>

==SqlDataAdapter.DeleteCommand==

<source lang="csharp">
using System;
using System.Data;
using System.Data.SqlClient;
class PropagateDeletes {
static void Main() {
string connString = "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI";
string qry = @"select * from employee ";
string del = @"delete from employee where id = @id";
SqlConnection conn = new SqlConnection(connString);
try {
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = new SqlCommand(qry, conn);
DataSet ds = new DataSet();
da.Fill(ds, "employee");
DataTable dt = ds.Tables["employee"];
SqlCommand cmd = new SqlCommand(del, conn);
cmd.Parameters.Add("@id",SqlDbType.Int, 4, "id");
string filt = @"firstname = "o" and lastname = "B"";
foreach (DataRow row in dt.Select(filt)) {
row.Delete();
}
da.DeleteCommand = cmd;
da.Update(ds, "employee");
foreach (DataRow row in dt.Rows) {
Console.WriteLine(
"{0} {1}",
row["firstname"].ToString().PadRight(15),
row["lastname"].ToString().PadLeft(25));
}
} catch(Exception e) {
Console.WriteLine("Error: " + e);
} finally {
conn.Close();
}
}
}

</source>

==SqlDataAdapter.Fill==

<source lang="csharp">
using System;
using System.Data;
using System.Data.SqlClient;
class PopulateDataSetUsingSelect
{
public static void Main()
{
SqlConnection mySqlConnection =new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;");
SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
mySqlCommand.rumandText =
"SELECT TOP 5 ID, FirstName, LastName " +
"FROM Employee " +
"ORDER BY ID";
SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
mySqlDataAdapter.SelectCommand = mySqlCommand;
DataSet myDataSet = new DataSet();
mySqlConnection.Open();
Console.WriteLine("Retrieving rows from the Employee table");
int numberOfRows = mySqlDataAdapter.Fill(myDataSet, "Employee");
Console.WriteLine("numberOfRows = " + numberOfRows);
mySqlConnection.Close();
DataTable myDataTable = myDataSet.Tables["Employee"];
foreach (DataRow myDataRow in myDataTable.Rows)
{
Console.WriteLine("ID = " + myDataRow["ID"]);
Console.WriteLine("First Name = " + myDataRow["FirstName"]);
Console.WriteLine("Last Name = " + myDataRow["LastName"]);
}
}
}

</source>

==SqlDataAdapter.InsertCommand==

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

==SqlDataAdapter.RowUpdated==

<source lang="csharp">
using System;
using System.Data;
using System.Data.SqlClient;
class SqlDemo {
static void Main(){
string connString = "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI";
SqlConnection cn = new SqlConnection(connString);
try
{
cn.Open();
SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Employee", cn);

SqlCommandBuilder cb = new SqlCommandBuilder(da);
DataSet ds = new DataSet();
da.Fill(ds, 0, 1, "Employee");
da.RowUpdating += new SqlRowUpdatingEventHandler(OnRowUpdating);
da.RowUpdated += new SqlRowUpdatedEventHandler(OnRowUpdated);
DataTable dt = ds.Tables["Employee"];
dt.Rows[0][1] = "T";
da.Update(ds, "Employee");
da.RowUpdating -= new SqlRowUpdatingEventHandler(OnRowUpdating);
da.RowUpdated -= new SqlRowUpdatedEventHandler(OnRowUpdated);

} catch (SqlException ex) {
Console.WriteLine(ex.Message);
}
finally
{
cn.Close();
}
}

static void OnRowUpdating(object sender, SqlRowUpdatingEventArgs e)
{
Console.WriteLine("OnRowUpdating event");
if (e.Status != UpdateStatus.Continue)
Console.WriteLine("RowStatus = " + e.Status.ToString());
}
static void OnRowUpdated(object sender, SqlRowUpdatedEventArgs e)
{
Console.WriteLine("OnRowUpdating event");
if (e.Status != UpdateStatus.Continue)
Console.WriteLine("RowStatus = " + e.Status.ToString());
}
}

</source>

==SqlDataAdapter.RowUpdating==

<source lang="csharp">
using System;
using System.Data;
using System.Data.SqlClient;
class SqlDemo {
static void Main(){
string connString = "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI";
SqlConnection cn = new SqlConnection(connString);
try
{
cn.Open();
SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Employee", cn);

SqlCommandBuilder cb = new SqlCommandBuilder(da);
DataSet ds = new DataSet();
da.Fill(ds, 0, 1, "Employee");
da.RowUpdating += new SqlRowUpdatingEventHandler(OnRowUpdating);
da.RowUpdated += new SqlRowUpdatedEventHandler(OnRowUpdated);
DataTable dt = ds.Tables["Employee"];
dt.Rows[0][1] = "T";
da.Update(ds, "Employee");
da.RowUpdating -= new SqlRowUpdatingEventHandler(OnRowUpdating);
da.RowUpdated -= new SqlRowUpdatedEventHandler(OnRowUpdated);

} catch (SqlException ex) {
Console.WriteLine(ex.Message);
}
finally
{
cn.Close();
}
}

static void OnRowUpdating(object sender, SqlRowUpdatingEventArgs e)
{
Console.WriteLine("OnRowUpdating event");
if (e.Status != UpdateStatus.Continue)
Console.WriteLine("RowStatus = " + e.Status.ToString());
}
static void OnRowUpdated(object sender, SqlRowUpdatedEventArgs e)
{
Console.WriteLine("OnRowUpdating event");
if (e.Status != UpdateStatus.Continue)
Console.WriteLine("RowStatus = " + e.Status.ToString());
}
}

</source>

==SqlDataAdapter.SelectCommand==

<source lang="csharp">
using System;
using System.Data;
using System.Data.SqlClient;
class SelectIntoDataSet {
public static void Main() {
string connectionString = "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI";
SqlConnection mySqlConnection = new SqlConnection(connectionString);
string selectString = "SELECT TOP 10 ID, FirstName, LastName FROM Employee ORDER BY ID";
SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
mySqlCommand.rumandText = selectString;
SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
mySqlDataAdapter.SelectCommand = mySqlCommand;
DataSet myDataSet = new DataSet();
mySqlConnection.Open();
Console.WriteLine("Retrieving rows from the Employee table");
mySqlDataAdapter.Fill(myDataSet, "Employee");
mySqlConnection.Close();
DataTable myDataTable = myDataSet.Tables["Employee"];
foreach (DataRow myDataRow in myDataTable.Rows)
{
Console.WriteLine("ID = "+ myDataRow["ID"]);
Console.WriteLine("FirstName = "+ myDataRow["FirstName"]);
Console.WriteLine("LastName = "+ myDataRow["LastName"]);
}
}
}

</source>

==SqlDataAdapter.SelectCommand.Parameters.Add==

<source lang="csharp">
using System;
using System.Diagnostics;
using System.Drawing;
using System.Collections;
using System.ruponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
public class Form1 : System.Windows.Forms.Form {
private System.Windows.Forms.Label label1;
private System.Windows.Forms.TextBox txtStateWanted;
private System.Windows.Forms.Button btnFind;
private System.Windows.Forms.DataGrid dataGrid1;
public Form1() {
this.label1 = new System.Windows.Forms.Label();
this.txtStateWanted = new System.Windows.Forms.TextBox();
this.btnFind = new System.Windows.Forms.Button();
this.dataGrid1 = new System.Windows.Forms.DataGrid();
((System.ruponentModel.ISupportInitialize)(this.dataGrid1)).BeginInit();
this.SuspendLayout();
this.label1.Location = new System.Drawing.Point(62, 241);
this.label1.Size = new System.Drawing.Size(32, 23);
this.label1.Text = "State";
this.txtStateWanted.Location = new System.Drawing.Point(102, 241);
this.txtStateWanted.Size = new System.Drawing.Size(64, 20);
this.txtStateWanted.Text = "CA";
this.btnFind.Location = new System.Drawing.Point(206, 241);
this.btnFind.Text = "Fill";
this.btnFind.Click += new System.EventHandler(this.btnFind_Click);
this.dataGrid1.DataMember = "";
this.dataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText;
this.dataGrid1.Location = new System.Drawing.Point(6, 9);
this.dataGrid1.Size = new System.Drawing.Size(280, 224);
this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
this.ClientSize = new System.Drawing.Size(292, 273);
this.Controls.AddRange(new System.Windows.Forms.Control[] {
this.label1,
this.txtStateWanted,
this.btnFind,
this.dataGrid1});
((System.ruponentModel.ISupportInitialize)(this.dataGrid1)).EndInit();
this.ResumeLayout(false);
}
[STAThread]
static void Main() {
Application.Run(new Form1());
}
private void btnFind_Click(object sender, System.EventArgs e) {
try {
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter("SELECT pubid, pubname, city, state FROM publishers where state = @State", "data source=.;database=biblio;uid=admin;pwd=pw");
da.SelectCommand.Parameters.Add("@State", txtStateWanted.Text);
da.Fill(ds, "PublishersIn" + txtStateWanted.Text);
dataGrid1.DataSource = ds.Tables[0];
} catch (SqlException sex) {
Debug.WriteLine(sex.ToString());
}
}
}

</source>

==SqlDataAdapter.TableMappings==

<source lang="csharp">
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.rumon;
class Program {
static void Main(string[] args) {
string cnStr = "uid=sa;pwd=;Initial Catalog=yourDatabase;Data Source=(local)";
DataSet myDS = new DataSet("Cars");
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");
try {
dAdapt.Fill(myDS, "Inventory");
} catch (Exception ex) {
Console.WriteLine(ex.Message);
}
PrintDataSet(myDS);
}
static void PrintDataSet(DataSet ds) {
Console.WriteLine("Tables in "{0}" DataSet.\n", ds.DataSetName);
foreach (DataTable dt in ds.Tables) {
Console.WriteLine("{0} Table.\n", dt.TableName);
for (int curCol = 0; curCol < dt.Columns.Count; curCol++) {
Console.Write(dt.Columns[curCol].ColumnName.Trim() + "\t");
}
for (int curRow = 0; curRow < dt.Rows.Count; curRow++) {
for (int curCol = 0; curCol < dt.Columns.Count; curCol++) {
Console.Write(dt.Rows[curRow][curCol].ToString().Trim() + "\t");
}
Console.WriteLine();
}
}
}
}

</source>

==SqlDataAdapter.TableMappings.Add==

<source lang="csharp">

using System;
using System.Data;
using System.Data.rumon;
using System.Data.SqlClient;

public class UsingDataTableMappings
{
static void Main(string[] args)
{
SqlConnection SConn = new SqlConnection("Data Source=(local); Initial Catalog = CaseManager; Integrated Security=true");
SqlDataAdapter da = new SqlDataAdapter("SELECT ID, Contact, Email FROM CaseInfo", SConn);
DataSet ds = new DataSet();
DataTableMapping custMap = da.TableMappings.Add("CaseInfo", "MyDatabase");
custMap.ColumnMappings.Add("ID", "CaseNumber");
custMap.ColumnMappings.Add("Contact", "MyContact");
custMap.ColumnMappings.Add("Email", "Email Address");
da.Fill(ds);
}
}

</source>

==SqlDataAdapter.Update==

<source lang="csharp">

using System;
using System.Data;
using System.Data.SqlClient;
class PropagateAddsBuilder {
static void Main() {
string connString = "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI";
string qry = @"select * from employee";

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, "employee");
DataTable dt = ds.Tables["employee"];
// Add a row
DataRow newRow = dt.NewRow();
newRow["firstname"] = "y";
newRow["lastname"] = "y";
dt.Rows.Add(newRow);
foreach (DataRow row in dt.Rows){
Console.WriteLine(
"{0} {1}",
row["firstname"].ToString().PadRight(15),
row["lastname"].ToString().PadLeft(25));
}
da.Update(ds, "employee");
} catch(Exception e) {
Console.WriteLine("Error: " + e);
} finally {
conn.Close();
}
}
}

</source>

==SqlDataAdapter.UpdateCommand==

<source lang="csharp">

using System;
using System.Data;
using System.Data.SqlClient;
class PropagateChanges {
static void Main(){
string connString = "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI";
string qry = @"select * from employee ";
string upd = @"update employee set firstname = @firstname where id = @id";
SqlConnection conn = new SqlConnection(connString);
try {
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = new SqlCommand(qry, conn);
DataSet ds = new DataSet();
da.Fill(ds, "employee");
DataTable dt = ds.Tables["employee"];
dt.Rows[0]["firstname"] = "W";
foreach (DataRow row in dt.Rows){
Console.WriteLine(
"{0} {1}",
row["firstname"].ToString().PadRight(15),
row["lastname"].ToString().PadLeft(25));
}
// Update employees
SqlCommand cmd = new SqlCommand(upd, conn);
cmd.Parameters.Add("@firstname",SqlDbType.NVarChar,15, "firstname");
SqlParameter parm = cmd.Parameters.Add("@id",SqlDbType.Int,4,"id");
parm.SourceVersion = DataRowVersion.Original;
da.UpdateCommand = cmd;
da.Update(ds, "employee");
} catch(Exception e) {
Console.WriteLine("Error: " + e);
} finally {
conn.Close();
}
}
}

</source>

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

Csharp/C Sharp by API/System.Data.SqlClient/SqlDataAdapter - История изменений

в 15:31, 26 мая 2010

Admin: 1 версия