Csharp/CSharp Tutorial/ADO.Net/SqlDataAdapter

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>