Csharp/C Sharp by API/System.Data.SqlClient/SqlDataAdapter
Содержание
- 1 new SqlDataAdapter
- 2 SqlDataAdapter.DeleteCommand
- 3 SqlDataAdapter.Fill
- 4 SqlDataAdapter.InsertCommand
- 5 SqlDataAdapter.RowUpdated
- 6 SqlDataAdapter.RowUpdating
- 7 SqlDataAdapter.SelectCommand
- 8 SqlDataAdapter.SelectCommand.Parameters.Add
- 9 SqlDataAdapter.TableMappings
- 10 SqlDataAdapter.TableMappings.Add
- 11 SqlDataAdapter.Update
- 12 SqlDataAdapter.UpdateCommand
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>