/* * C# Programmers Pocket Consultant * Author: Gregory S. MacBeth * Email: gmacbeth@comporium.net * Create Date: June 27, 2003 * Last Modified Date: * Version: 1 */ using System; using System.Data; using System.Data.SqlClient; namespace Client.Chapter_13___ADO.NET { public class UpdatingDataUsingCommondBuilder { static void Main(string[] args) { SqlConnection MyConnection = new SqlConnection(@"Data Source=(local); Initial Catalog = CaseManager; Integrated Security=true"); SqlDataAdapter MyDataAdapter = new SqlDataAdapter("SELECT ID, Contact, Email FROM Test", MyConnection); DataSet MyDataSet = new DataSet(); MyDataAdapter.Fill(MyDataSet); MyDataSet.Tables[0].Rows[0][0] = 55; SqlCommandBuilder MyCmd = new SqlCommandBuilder(MyDataAdapter); MyDataAdapter.Update(MyDataSet); } } }
Database ADO.net
Update A DataSource
/* * C# Programmers Pocket Consultant * Author: Gregory S. MacBeth * Email: gmacbeth@comporium.net * Create Date: June 27, 2003 * Last Modified Date: * Version: 1 */ using System; using System.Data; using System.Data.OleDb; namespace Client.Chapter_13___ADO.NET { public class UpdatingADataSource { static void Main(string[] args) { OleDbConnection MyConnection = new OleDbConnection(@"Provider=Microsft.Jet.OLEDB.4.0; Data Source = c:MyAccessDB.mdb"); OleDbDataAdapter MyAdapter = new OleDbDataAdapter("SELECT Column1, Column2, Column3 FROM MyTable", MyConnection); DataSet MyDataSet = new DataSet(); MyAdapter.Fill(MyDataSet, "MyTable"); MyDataSet.Tables[0].Rows[3]["Column3"] = "Test"; OleDbCommandBuilder MyBuilder = new OleDbCommandBuilder(MyAdapter); MyAdapter.Update(MyDataSet.Tables[0]); } } }
Using Data Table Mappings
/* * C# Programmers Pocket Consultant * Author: Gregory S. MacBeth * Email: gmacbeth@comporium.net * Create Date: June 27, 2003 * Last Modified Date: * Version: 1 */ using System; using System.Data; using System.Data.Common; using System.Data.SqlClient; namespace Client.Chapter_13___ADO.NET { 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); } } }
Creating Data Tables and Populating Them
/*
* C# Programmers Pocket Consultant
* Author: Gregory S. MacBeth
* Email: gmacbeth@comporium.net
* Create Date: June 27, 2003
* Last Modified Date:
* Version: 1
*/
using System;
using System.Data;
using System.Data.SqlClient;
namespace Client.Chapter_13___ADO.NET
{
public class CreatingDataTablesandPopulatingThem
{
static void Main(string[] args)
{
SqlConnection MyConnection = new SqlConnection(@”Data Source=(local); Initial Catalog = CaseManager; Integrated Security=true”);
SqlDataAdapter MyAdapter = new SqlDataAdapter(“SELECT * FROM CaseInfo”, MyConnection);
DataSet MyDataSet = new DataSet();
//Create a new DataTable
DataTable MyTable2 = MyDataSet.Tables.Add(“My2ndTable”);
//Adding Columns and Rows
DataColumn myColumn = new DataColumn();
myColumn.DataType = System.Type.GetType(“System.Decimal”);
myColumn.AllowDBNull = false;
myColumn.Caption = “Price”;
myColumn.ColumnName = “Price”;
myColumn.DefaultValue = 25;
// Add the column to the table.
MyTable2.Columns.Add(myColumn);
// Add 10 rows and set values.
DataRow myRow;
for (int i = 0; i < 10; i++) { myRow = MyTable2.NewRow(); myRow[0] = i + 1; // Be sure to add the new row to the DataRowCollection. MyTable2.Rows.Add(myRow); } SqlCommandBuilder Builder = new SqlCommandBuilder(MyAdapter); MyAdapter.Update(MyDataSet, "My2ndTable"); } } } [/csharp]
Illustrates how to perform INSERT, UPDATE, and DELETE statements using ADO.NET
/* Mastering Visual C# .NET by Jason Price, Mike Gunderloy Publisher: Sybex; ISBN: 0782129110 */ /* Example23_2.cs illustrates how to perform INSERT, UPDATE, and DELETE statements using ADO.NET */ using System; using System.Data; using System.Data.SqlClient; public class Example23_2 { public static void DisplayDataTable( SqlDataAdapter mySqlDataAdapter, DataSet myDataSet, DataTable myDataTable ) { // use the Clear() method of the DataSet object // to remove all the rows in the DataSet myDataSet.Clear(); // use the Fill() method of the SqlDataAdapter object // to synchronize any changes made to the database // with the DataSet object mySqlDataAdapter.Fill(myDataSet, "Customers"); // display the columns for each row in the DataTable, // using a DataRow object to access each row in the DataTable foreach (DataRow myDataRow in myDataTable.Rows) { Console.WriteLine("CustomerID = " + myDataRow["CustomerID"]); Console.WriteLine("CompanyName = " + myDataRow["CompanyName"]); Console.WriteLine("ContactName = " + myDataRow["ContactName"]); Console.WriteLine("Address = " + myDataRow["Address"]); } } public static void AddRow( SqlConnection mySqlConnection, SqlDataAdapter mySqlDataAdapter, DataSet myDataSet ) { Console.WriteLine(" Adding a new row with CustomerID of 'T1COM'"); // step 1: formulate a string containing the SQL statement string insertString = "INSERT INTO Customers (" + " CustomerID, CompanyName, ContactName, Address" + ") VALUES (" + " @CustomerID, @CompanyName, @ContactName, @Address" + ")"; // step 2: create a SqlCommand object to hold the SQL statement SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); // step 3: set the CommandText property of the SqlCommand object to // the SQL string mySqlCommand.CommandText = insertString; // step 4: use the Add() method through the Parameters property // of the SqlCommand object to add the parameters to the SqlCommand // object mySqlCommand.Parameters.Add("@CustomerID", SqlDbType.NChar, 5); mySqlCommand.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40); mySqlCommand.Parameters.Add("@ContactName", SqlDbType.NVarChar, 30); mySqlCommand.Parameters.Add("@Address", SqlDbType.NVarChar, 60); // step 5: set the parameters to specified values using the // Value property mySqlCommand.Parameters["@CustomerID"].Value = "T1COM"; mySqlCommand.Parameters["@CompanyName"].Value = "T1 Company"; mySqlCommand.Parameters["@ContactName"].Value = "Jason Price"; mySqlCommand.Parameters["@Address"].Value = "1 Main Street"; // step 6: use the ExecuteNonQuery() method to run the // SQL statement mySqlCommand.ExecuteNonQuery(); } public static void ModifyRow( SqlConnection mySqlConnection, SqlDataAdapter mySqlDataAdapter, DataSet myDataSet ) { Console.WriteLine(" Modifying the new row"); // step 1: formulate the SQL statement string updateString = "UPDATE Customers " + "SET " + " CompanyName = @CompanyName, " + " ContactName = @ContactName, " + " Address = @Address " + "WHERE CustomerID = @CustomerID"; // step 2: create a SqlCommand object to hold the SQL statement SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); // step 3: set the CommandText property of the SqlCommand object to // the SQL string mySqlCommand.CommandText = updateString; // step 4: use the Add() method through the Parameters property // to add the parameters mySqlCommand.Parameters.Add("@CustomerID", SqlDbType.NChar, 5); mySqlCommand.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40); mySqlCommand.Parameters.Add("@ContactName", SqlDbType.NVarChar, 30); mySqlCommand.Parameters.Add("@Address", SqlDbType.NVarChar, 60); // step 5: set the parameters to values using the Value property mySqlCommand.Parameters["@CustomerID"].Value = "T1COM"; mySqlCommand.Parameters["@CompanyName"].Value = "Widgets Inc."; mySqlCommand.Parameters["@ContactName"].Value = "John Smith"; mySqlCommand.Parameters["@Address"].Value = "1 Any Street"; // step 6: use the ExecuteNonQuery() method to run the // SQL statement mySqlCommand.ExecuteNonQuery(); } public static void RemoveRow( SqlConnection mySqlConnection, SqlDataAdapter mySqlDataAdapter, DataSet myDataSet ) { Console.WriteLine(" Removing the new row"); // step 1: formulate the SQL statement string deleteString = "DELETE FROM Customers " + "WHERE CustomerID = @CustomerID"; // step 2: create a SqlCommand object to hold the SQL statement SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); // step 3: set the CommandText property of the SqlCommand object to // the SQL string mySqlCommand.CommandText = deleteString; // step 4: use the Add() method through the Parameters property // to add the parameter mySqlCommand.Parameters.Add("@CustomerID", SqlDbType.NChar, 5); // step 5: set the parameters to values using the Value property mySqlCommand.Parameters["@CustomerID"].Value = "T1COM"; // step 6: use the ExecuteNonQuery() method to run the // SQL statement mySqlCommand.ExecuteNonQuery(); } public static void Main() { // formulate a string containing the details of the // database connection string connectionString = "server=localhost;database=Northwind;uid=sa;pwd=sa"; // create a SqlConnection object to connect to the // database, passing the connection string to the constructor SqlConnection mySqlConnection = new SqlConnection(connectionString); // formulate a SELECT statement to retrieve the // CustomerID, CompanyName, ContactName, and Address // columns for rows from the Customers table string selectString = "SELECT CustomerID, CompanyName, ContactName, Address " + "FROM Customers " + "WHERE CustomerID IN ('ALFKI', 'T1COM')"; // create a SqlCommand object to hold the SELECT statement SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); // set the CommandText property of the SqlCommand object to // the SELECT string mySqlCommand.CommandText = selectString; // create a SqlDataAdapter object SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(); // set the SelectCommand property of the SqlAdapter object // to the SqlCommand object mySqlDataAdapter.SelectCommand = mySqlCommand; // create a DataSet object to store the results of // the SELECT statement DataSet myDataSet = new DataSet(); // open the database connection using the // Open() method of the SqlConnection object mySqlConnection.Open(); // use the Fill() method of the SqlDataAdapter object to // retrieve the rows from the table, storing the rows locally // in a DataTable of the DataSet object string dataTableName = "Customers"; Console.WriteLine("Retrieving a row from the Customers table"); mySqlDataAdapter.Fill(myDataSet, dataTableName); // get the DataTable object from the DataSet object DataTable myDataTable = myDataSet.Tables[dataTableName]; // display the rows in the DataTable object DisplayDataTable(mySqlDataAdapter, myDataSet, myDataTable); // add a new row AddRow(mySqlConnection, mySqlDataAdapter, myDataSet); DisplayDataTable(mySqlDataAdapter, myDataSet, myDataTable); // modify the new row ModifyRow(mySqlConnection, mySqlDataAdapter, myDataSet); DisplayDataTable(mySqlDataAdapter, myDataSet, myDataTable); // remove the new row RemoveRow(mySqlConnection, mySqlDataAdapter, myDataSet); DisplayDataTable(mySqlDataAdapter, myDataSet, myDataTable); // close the database connection using the Close() method // of the SqlConnection object mySqlConnection.Close(); } }
Update table using SqlDataAdapter
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(); } } }
Use DataTable to update table in Database
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(); } } }