- Letting .NET DO Some of the Heavy LiftingA SQL Command Builder Example
- A SQL Command Builder Example
- All the Work in One-Fourth the Time
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
A SQL Command Builder Example
SqlCommandBuilder is easy to use—as many things in a good framework are. Create a connection, command, and adapter. Associate the SqlCommandBuilder with the adapter during construction. The SqlCommandBuilder in turn uses a sealed internal class (CommandBuilder) and the adapter, and commands access to the schema to generate the SQL for insert, update, and delete commands.
Listing 1 contains a simple console application. First the application assembles the elements to run a SELECT command against the Northwind sample database. Then SqlCommandBuilder is used to dump the UPDATE, INSERT, and DELETE statements (generated by SqlCommandBuilder) to the console. Finally, the generated INSERT statement is used to actually update the database. Notice there is very little literal SQL in my code.
Listing 1 A console application demonstrating the SqlCommandBuilder by displaying the generated SQL and calling the generated INSERT command
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
namespace SqlCommandBuilder
{
class Program
{
static void Main(string[] args)
{
const string connectionString =
"Data Source=LOCALHOST;Initial Catalog=Northwind;Integrated Security=True";
using(SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
SqlCommand command = new SqlCommand("SELECT * FROM Customers", connection);
SqlDataAdapter adapter = new SqlDataAdapter(command);
SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
DataSet dataset = new DataSet();
adapter.Fill(dataset);
string line = new string(’-’, 40) + Environment.NewLine;
Console.WriteLine(builder.GetUpdateCommand().CommandText);
Console.WriteLine(line);
Console.WriteLine(builder.GetDeleteCommand().CommandText);
Console.WriteLine(line);
Console.WriteLine(builder.GetInsertCommand().CommandText);
Console.WriteLine(line);
Console.ReadLine();
// insert a row
SqlCommand insert = builder.GetInsertCommand();
insert.Parameters["@P1"].Value = "PAUKI";
insert.Parameters["@P2"].Value = "PAULY’S PIES";
insert.Parameters["@P3"].Value = "Paul Kimmel";
insert.Parameters["@P4"].Value = "Oh Large One!";
insert.Parameters["@P5"].Value = "1313 Mockingbird Ln.";
insert.Parameters["@P6"].Value = "Okemos";
insert.Parameters["@P7"].Value = "Michigan";
insert.Parameters["@P8"].Value = "48864";
insert.Parameters["@P9"].Value = "USA";
insert.Parameters["@P10"].Value = "(517) 555-1234";
insert.Parameters["@P11"].Value = "(517) 555-1234";
insert.ExecuteNonQuery();
adapter.Fill(dataset);
DataRow[] rows = dataset.Tables[0].Select("CustomerID = ’PAUKI’");
if(rows.Length == 1)
Console.WriteLine(rows[0]["CompanyName"]);
Console.ReadLine();
}
}
}
}