public static SqlDataAdapter CreateSqlDataAdapter(SqlConnection connection)
{
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
adapter.SelectCommand = new SqlCommand(
"SELECT CustomerID, CompanyName FROM CUSTOMERS", connection);
adapter.InsertCommand = new SqlCommand(
"INSERT INTO Customers (CustomerID, CompanyName) " +
"VALUES (@CustomerID, @CompanyName)", connection);
adapter.UpdateCommand = new SqlCommand(
"UPDATE Customers SET CustomerID = @CustomerID, CompanyName = @CompanyName " +
"WHERE CustomerID = @oldCustomerID", connection);
adapter.DeleteCommand = new SqlCommand(
"DELETE FROM Customers WHERE CustomerID = @CustomerID", connection);
adapter.InsertCommand.Parameters.Add("@CustomerID",
SqlDbType.Char, 5, "CustomerID");
adapter.InsertCommand.Parameters.Add("@CompanyName",
SqlDbType.VarChar, 40, "CompanyName");
adapter.UpdateCommand.Parameters.Add("@CustomerID",
SqlDbType.Char, 5, "CustomerID");
adapter.UpdateCommand.Parameters.Add("@CompanyName",
SqlDbType.VarChar, 40, "CompanyName");
adapter.UpdateCommand.Parameters.Add("@oldCustomerID",
SqlDbType.Char, 5, "CustomerID").SourceVersion =
DataRowVersion.Original;
adapter.DeleteCommand.Parameters.Add("@CustomerID",
SqlDbType.Char, 5, "CustomerID").SourceVersion =
DataRowVersion.Original;
return adapter;
}
string selectSQL =
"SELECT CustomerID, CompanyName FROM Customers " +
"WHERE CountryRegion = ? AND City = ?";
string insertSQL =
"INSERT INTO Customers (CustomerID, CompanyName) " +
"VALUES (?, ?)";
string updateSQL =
"UPDATE Customers SET CustomerID = ?, CompanyName = ? " +
"WHERE CustomerID = ? ";
string deleteSQL = "DELETE FROM Customers WHERE CustomerID = ?";
public static SqlDataAdapter CreateSqlDataAdapter(SqlConnection connection)
{
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
adapter.SelectCommand = new SqlCommand(
"SELECT CustomerID, CompanyName FROM CUSTOMERS", connection);
adapter.InsertCommand = new SqlCommand(
"INSERT INTO Customers (CustomerID, CompanyName) " +
"VALUES (@CustomerID, @CompanyName)", connection);
adapter.UpdateCommand = new SqlCommand(
"UPDATE Customers SET CustomerID = @CustomerID, CompanyName = @CompanyName " +
"WHERE CustomerID = @oldCustomerID", connection);
adapter.DeleteCommand = new SqlCommand(
"DELETE FROM Customers WHERE CustomerID = @CustomerID", connection);
adapter.InsertCommand.Parameters.Add("@CustomerID",
SqlDbType.Char, 5, "CustomerID");
adapter.InsertCommand.Parameters.Add("@CompanyName",
SqlDbType.VarChar, 40, "CompanyName");
adapter.UpdateCommand.Parameters.Add("@CustomerID",
SqlDbType.Char, 5, "CustomerID");
adapter.UpdateCommand.Parameters.Add("@CompanyName",
SqlDbType.VarChar, 40, "CompanyName");
adapter.UpdateCommand.Parameters.Add("@oldCustomerID",
SqlDbType.Char, 5, "CustomerID").SourceVersion =
DataRowVersion.Original;
adapter.DeleteCommand.Parameters.Add("@CustomerID",
SqlDbType.Char, 5, "CustomerID").SourceVersion =
DataRowVersion.Original;
return adapter;
}
OdbcDataAdapter adapter = new OdbcDataAdapter();
OdbcCommand selectCMD = new OdbcCommand(selectSQL, connection);
adapter.SelectCommand = selectCMD;
selectCMD.Parameters.Add("@CountryRegion", OdbcType.VarChar, 15).Value = "UK";
selectCMD.Parameters.Add("@City", OdbcType.VarChar, 15).Value = "London";
DataSet customers = new DataSet();
adapter.Fill(customers, "Customers");