using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;
class Program
{
static void Main(string[] args)
{
SqlConnection conn = new SqlConnection("data source=localhost; initial catalog=SampleDB; Integrated Security=SSPI;");
conn.Open();
SqlCommand selectCmd = conn.CreateCommand();
selectCmd.CommandText = "SELECT ID, FirstName, LastName, MiddleInitial FROM Employees";
SqlCommand updateCmd = conn.CreateCommand();
updateCmd.CommandText = "UPDATE Employees SET FirstName = @FirstName, LastName = @LastName, MiddleInitial = @MiddleInitial WHERE ID = @ID";
updateCmd.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.VarChar, 50, "FirstName"));
updateCmd.Parameters.Add(new SqlParameter("@LastName", SqlDbType.VarChar, 50, "LastName"));
updateCmd.Parameters.Add(new SqlParameter("@MiddleInitial", SqlDbType.NChar, 1, "MiddleInitial"));
updateCmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int, 4, "ID"));
updateCmd.UpdatedRowSource = UpdateRowSource.None;
SqlCommand insertCommand = conn.CreateCommand();
insertCommand.CommandText = "INSERT INTO Employees(FirstName, LastName, MiddleInitial) VALUES(@FirstName, @LastName, @MiddleInitial)";
insertCommand.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.VarChar, 50, "FirstName"));
insertCommand.Parameters.Add(new SqlParameter("@LastName", SqlDbType.VarChar, 50, "LastName"));
insertCommand.Parameters.Add(new SqlParameter("@MiddleInitial", SqlDbType.NChar, 1, "MiddleInitial"));
insertCommand.UpdatedRowSource = UpdateRowSource.None;
SqlDataAdapter da = new SqlDataAdapter(selectCmd);
da.UpdateCommand = updateCmd;
da.InsertCommand = insertCommand;
da.UpdateBatchSize = 10;
da.AcceptChangesDuringUpdate = true;
DataSet ds = new DataSet();
da.Fill(ds, "Employees");
ds.Tables[0].Rows[0]["FirstName"] = "new";
DataRow newCust = ds.Tables[0].NewRow();
newCust["FirstName"] = "B";
newCust["LastName"] = "C";
newCust["MiddleInitial"] = 'Q';
ds.Tables[0].Rows.Add(newCust);
newCust = ds.Tables[0].NewRow();
newCust["FirstName"] = "B";
newCust["LastName"] = "D";
newCust["MiddleInitial"] = 'J';
ds.Tables[0].Rows.Add(newCust);
da.Update(ds, "Employees");
}
}
|