Working with Data in a Database

To work with databases, there are several SQL commands you must know:

SELECT chooses the fields/columns in the database. The following example returns columns 1 through 3 from the table called MyTable.
```
        SELECT Column1, Column2, Column3 FROM MyTable
```
FROM defines the table. The following example returns all fields from the table named MyTable.
```
        SELECT * FROM MyTable
```
WHERE adds conditions to a statement. The following example returns all the columns/fields from the table called MyTable where Column2 is equal to "Smith".
```
        SELECT * FROM MyTable WHERE Column2 = "Smith"
```
INSERT INTO adds a new row. The following example inserts a new row into the table called MyTable with values.
```
        INSERT INTO MyTable( Column1, Column2, Column3) VALUES(1, Greg, MacBeth)
```
DELETE FROM deletes a row. The following example deletes all rows from the table called MyTable where Column2 is equal to "Smith".
```
        DELETE FROM MyTable WHERE Column2 = "Smith"
```
UPDATE modifies a row in a table. The following example goes through the table called MyTable and sets Column2 to "Lee" only when Column1 = "Robert".
```
        UPDATE MyTable SET Column2 = "Lee" WHERE Column1 = "Robert"
```

You can also use CommandBuilder to insert, update, and delete data in databases. The following examples demonstrate how to perform these tasks with SQL statements and with CommandBuilder, as well as how to use transactions, find rows in a database, use DataTableMappings, and call a stored procedure.

Code Example: Inserting Data into a Database Using SQL Statements

using System;
using System.Data;
using System.Data.SqlClient;

namespace Client.Chapter_13___ADO.NET
{
          class InsertingDataUsingSQLStatements
          {
                static void Main(string[] args)
                {
                       SqlConnection MyConnection =
                         new SqlConnection(@"Data Source=(local);"
                         + "Initial Catalog = CaseManager;"
                         + "Integrated Security=true");
                       MyConnection.Open();
                       String MyString = @"INSERT INTO Test(ID, Contact, Email)"
                         + @"VALUES(2, 'Greg', 'MacBeth')";
                       SqlCommand MyCmd = new SqlCommand(MyString, MyConnection);
                       MyCmd.ExecuteScalar();
                       MyConnection.Close();
                }
         }
}

Code Example: Inserting Data into a Database Using CommandBuilder


using System;
using System.Data;
using System.Data.SqlClient;

namespace Client.Chapter_13___ADO.NET
{
          class InsertingDataUsingCommandBuilder
          {
                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);

                       SqlCommandBuilder MyCmd =
                         new SqlCommandBuilder(MyDataAdapter);
                       DataSet MyDataSet = new DataSet();
                       MyDataAdapter.Fill(MyDataSet);
                       DataRow MyRow = MyDataSet.Tables[0].NewRow();
                       MyRow["ID"] = 200;
                       MyRow["Contact"] = "Greg";
                       MyRow["Email"] = "MacBeth";
                       MyDataSet.Tables[0].Rows.Add(MyRow);
                       MyDataAdapter.Update(MyDataSet);
                }
          }
}

Code Example: Updating Data in a Database Using SQL Statements

using System;
using System.Data;
using System.Data.SqlClient;

namespace Client.Chapter_13___ADO.NET
{
          class UpdatingDataUsingSqlStatements
          {
                static void Main(string[] args)
                {
                       SqlConnection MyConnection =
                         new SqlConnection(@"Data Source=(local);"
                         + "Initial Catalog = CaseManager;"
                         + "Integrated Security=true");
                       MyConnection.Open();
                       String MyString = "UPDATE Test SET Contact = 'Lee'";
                       SqlCommand MyCmd = new SqlCommand(MyString, MyConnection);
                       MyCmd.ExecuteScalar();
                       MyConnection.Close();
                }
          }
}

Code Example: Using Transactions

using System;
using System.Data;
using System.Data.SqlClient;

namespace Client.Chapter_13___ADO.NET
{
          class UpdatingDataUsingTransactions
          {
                static void Main(string[] args)
                {
                       SqlConnection MyConnection =
                         new SqlConnection(@"Data Source=(local);"
                         + "Initial Catalog = CaseManager;"
                         + "Integrated Security=true");
                       MyConnection.Open();
                       String MyString = "INSERT INTO Test(ID, Contact, Email)"
                         + @"VALUES(1, 'Greg', 'Mac')";
                       SqlTransaction MyTransaction =
                         MyConnection.BeginTransaction();
                       SqlCommand MyCmd =
                         new SqlCommand(MyString, MyConnection, MyTransaction);
                       MyCmd.ExecuteScalar();
                       MyTransaction.Commit();
                       MyConnection.Close();
                }
          }
}

Note

This is a useful shortcut that allows you to avoid using the SQL statements when you are working with just one table.

Code Example: Updating Data in a Database Using CommandBuilder

using System;
using System.Data;
using System.Data.SqlClient;

namespace Client.Chapter_13___ADO.NET
{
          class UpdatingDataUsingCommandBuilder
          {
                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);
                }
          }
}

Code Example: Deleting Data from a Database Using SQL Statements


using System;
using System.Data;
using System.Data.SqlClient;

namespace Client.Chapter_13___ADO.NET
{
          class DeletingDataUsingSQLStatements
          {
                static void Main(string[] args)
                {
                       SqlConnection MyConnection =
                         new SqlConnection(@"Data Source=(local);"
                         + @"Initial Catalog = CaseManager; "
                         + "Integrated Security=true");
                       MyConnection.Open();
                       String MyString = "DELETE Test";
                       SqlCommand MyCmd = new SqlCommand(MyString, MyConnection);
                       MyCmd.ExecuteScalar();
                       MyConnection.Close();
                }
          }
}

Code Example: Deleting Data from a Database Using a CommandBuilder

using System;
using System.Data;
using System.Data.SqlClient;

namespace Client.Chapter_13___ADO.NET
{
          class DeletingDataUsingCommandBuilder
          {
                static void Main(string[] args)
                {
                       SqlConnection MyConnection =
                         new SqlConnection(@"Data Source=(local);"
                         + "Initial Catalog = CaseManager;"
                         + "Integrated Security=true");
                       SqlDataAdapter MyDataAdapter =
                         new SqlDataAdapter("SELECT * FROM Test", MyConnection);
                       SqlCommandBuilder MyCmd =
                         new SqlCommandBuilder(MyDataAdapter);
                       DataSet MyDataSet = new DataSet();
                       MyDataAdapter.Fill(MyDataSet);
                       DataColumn[] MyKey = new DataColumn[1];
                       MyKey[0] = MyDataSet.Tables[0].Columns[0];
                       MyDataSet.Tables[0].PrimaryKey = MyKey;
                       DataRow FindMyRow = MyDataSet.Tables[0].Rows.Find(1);

                       FindMyRow.Delete();
                       MyDataAdapter.Update(MyDataSet);
                }
          }
}

Code Example: Finding Rows in a Database


using System;
using System.Data;
using System.Data.SqlClient;

namespace Client.Chapter_13___ADO.NET
{
          class FindingRowsInData
          {
                public static void Main()
                {
                       SqlConnection MyConnection =
                         new SqlConnection(@"Data Source=(local);"
                         + "Initial Catalog = CaseManager;"
                         + "Integrated Security=true");
                       SqlDataAdapter MyDataAdapter =
                       new SqlDataAdapter("SELECT * FROM Test", MyConnection);
                       SqlCommandBuilder MyCmd =
                         new SqlCommandBuilder(MyDataAdapter);
                       DataSet MyDataSet = new DataSet();
                       MyDataAdapter.Fill(MyDataSet);
                       DataColumn[] MyKey = new DataColumn[1];
                       MyKey[0] = MyDataSet.Tables[0].Columns[0];
                       MyDataSet.Tables[0].PrimaryKey = MyKey;
                       DataRow FindMyRow = MyDataSet.Tables[0].Rows.Find(1);
                }
          }
}

Code Example: Using DataTableMappings

using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;

namespace Client.Chapter_13___ADO.NET
{
          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);
                 }
          }
}

Code Example: Calling a Stored Procedure


using System;
using System.Data;
using System.Data.SqlClient;

namespace Client.Chapter_13___ADO.NET
{
          class Client
          {
                static void Main(string[] args)
                {
                       SqlConnection cn = new SqlConnection(
                         @"Data Source=(local); Initial Catalog = MyDatabase;" +
                         "User ID=sa;Password=");
                       SqlCommand cmd = new SqlCommand("MyStoredProcedure", cn);
                       cmd.CommandType = CommandType.StoredProcedure;
                       SqlParameter param =
                         new SqlParameter("@ReturnValue", SqlDbType.Int);
                       cmd.Parameters.Add(param);
                       cmd.Parameters.Add("MyFirstParameter", SqlDbType.Int);
                       cmd.Parameters.Add("MySecondParameter",
                          SqlDbType.Int).Direction =
                          ParameterDirection.Output;
                         SqlDataAdapter da = new SqlDataAdapter(cmd);
                }
          }
}

Here is how the stored procedure in this example was created:

CREATE PROCEDURE MyStoredProcedure(
                                    @MyFirstParameter int,
                                    @MySecondParameter int OUTPUT)
AS
SELECT @MySecondParameter =
(SELECT Count FROM MyDB where ID LIKE @MyFirstParameter)
RETURN 1