Csharp/CSharp Tutorial/ADO.Net/SqlDataAdapter

Adding Data to MDF file with SqlDataAdapter

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();
    }
  }

Create a data adapter from select statement and connection string

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"]);
            }
        }
    }

Delete command with parameters

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();
      }
   }
}

Executing a SQL Server Scalar-Valued Function

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"]);
        }
    }

Executing a SQL Server Table-Valued Function

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]);
            }
        }
    }

Fill DataSet With SqlDataAdapter

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();
    }
  }

Insert command with parameters

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();
      }
   }
}

Persist Adds

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();
            }
        }
    }

Persist Adds Builder

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();
            }
        }
    }

Persist Changes

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();
            }
        }
    }

Persist Deletes

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();
            }
        }
    }

Update command with parameters

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();
      }
   }
}

Update SqlDataAdapter with DataSet

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();
      }
   }
}

Updating Data with SqlDataAdapter and DataSet

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();
    }
  }

Use SqlDataAdapter to deal with the select statement

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();
      }
   }
}