private void LoadData()
{
string strConnection = SQL_CONNECTION_STRING;
// Display a status message saying that we're attempting to connect to SQL Server.
// This only needs to be done the very first time a connection is
// attempted. After we've determined that MSDE or SQL Server is
// installed, this message no longer needs to be displayed.
frmStatus frmStatusMessage = new frmStatus();
frmStatusMessage.Show("Connecting to SQL Server");
// Attempt to connect first to the local SQL server instance,
// if that is not successful try a local
// MSDE installation (with the Northwind DB).
bool IsConnecting = true;
while (IsConnecting)
{
try {
// The SqlConnection class allows you to communicate with SQL Server.
// The constructor accepts a connection string an argument. This
// connection string uses Integrated Security, which means that you
// must have a login in to SQL Server, or be part of the Administrators
// group on your local machine for this to work. No password or user id is
// included in this type of string.
SqlConnection northwindConnection = new SqlConnection(strConnection);
// The SqlDataAdapter is used to populate a Dataset
SqlDataAdapter ProductAdapter = new SqlDataAdapter(
"SELECT * "
+ "FROM products",
northwindConnection);
// Populate the Dataset with the information from the products
// table. Since a Dataset can hold multiple result sets, it's
// a good idea to "name" the result set when you populate the
// DataSet. In this case, the result set is named "Products".
ProductAdapter.Fill(dsProducts, PRODUCT_TABLE_NAME);
//create the dataview; use a constructor to specify
// the sort, filter criteria for performance purposes
dvProducts = new DataView(dsProducts.Tables["products"], "", DEFAULT_SORT, DataViewRowState.OriginalRows);
// Data has been retrieved successfully
// Signal to break out of the loop by setting isConnecting to false.
IsConnecting = false;
//Handle the situation where a connection attempt has failed
} catch
{
if (strConnection == SQL_CONNECTION_STRING)
{
// Couldn't connect to SQL Server. Now try MSDE.
strConnection = MSDE_CONNECTION_STRING;
frmStatusMessage.Show("Connecting to MSDE");
}
else
{
// Unable to connect to SQL Server or MSDE
frmStatusMessage.Close();
MessageBox.Show("To run this sample, you must have SQL " +
"or MSDE with the Northwind database installed. For " +
"instructions on installing MSDE, view Readthis.",
CAPTION_TITLE, CAPTION_BUTTON, CAPTION_ICON);
//quit the program; could not connect to either SQL Server
Application.Exit();
}
}
}
frmStatusMessage.Close();
}
private void InitDataSets()
{
// Display a status message box saying that we are attempting to connect.
// This only needs to be done the first time a connection is attempted.
// After we have determined that MSDE or SQL Server is installed, this
// message no longer needs to be displayed
frmStatus frmStatusMessage = new frmStatus();
if (HasConnected == false)
{
frmStatusMessage.Show("Connecting to SQL Server");
}
// Attempt to connect to SQL server or MSDE
bool IsConnecting = true;
while (IsConnecting)
{
try
{
SqlConnection con = new SqlConnection(Connectionstring);
con.Open();
// Connection successful
IsConnecting = false;
HasConnected = true;
frmStatusMessage.Close();
// Since this the first time a conection is made
// The table being used for this How-To must be created
// Instantiate Command Object to execute SQL Statements
SqlCommand cmInitSQL = new SqlCommand();
// Attach the command to the connection
cmInitSQL.Connection = con;
// Set the command type to Text
cmInitSQL.CommandType = CommandType.Text;
// Drop ProductsDS table if it exists.
cmInitSQL.CommandText = "IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[ProductsDS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) " + "DROP TABLE [dbo].[ProductsDS] ";
// Execute the statement
cmInitSQL.ExecuteNonQuery();
// Drop ProductsTDS table if it exists.
cmInitSQL.CommandText = "IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[ProductsTDS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) " + "DROP TABLE [dbo].[ProductsTDS] ";
// Execute the statement
cmInitSQL.ExecuteNonQuery();
// Create ProductsDS Table
cmInitSQL.CommandText = "CREATE TABLE [dbo].[ProductsDS] ( " + "[ProductID] [int] IDENTITY (1, 1) PRIMARY KEY ," + "[ProductName] [nvarchar] (40) NOT NULL , " + "[SupplierID] [int] NULL , " + "[CategoryID] [int] NULL , " + "[QuantityPerUnit] [nvarchar] (20) NULL , " + "[UnitPrice] [money] NULL , " + "[UnitsInStock] [smallint] NULL , " + "[UnitsOnOrder] [smallint] NULL , " + "[ReorderLevel] [smallint] NULL , " + "[Discontinued] [bit] NOT NULL )";
// Execute the statement
cmInitSQL.ExecuteNonQuery();
// Create ProductsTDS Table
cmInitSQL.CommandText = "CREATE TABLE [dbo].[ProductsTDS] ( " + "[ProductID] [int] IDENTITY (1, 1) PRIMARY KEY ," + "[ProductName] [nvarchar] (40) NOT NULL , " + "[SupplierID] [int] NULL , " + "[CategoryID] [int] NULL , " + "[QuantityPerUnit] [nvarchar] (20) NULL , " + "[UnitPrice] [money] NULL , " + "[UnitsInStock] [smallint] NULL , " + "[UnitsOnOrder] [smallint] NULL , " + "[ReorderLevel] [smallint] NULL , " + "[Discontinued] [bit] NOT NULL )";
// Execute the statement
cmInitSQL.ExecuteNonQuery();
// Insert data into new table from products table in northwind
cmInitSQL.CommandText = "INSERT INTO ProductsDS " + "(ProductName,SupplierID,CategoryID," + "QuantityPerUnit,UnitPrice,UnitsInStock," + "UnitsOnOrder,ReorderLevel, Discontinued) " + "SELECT ProductName,SupplierID,CategoryID," + "QuantityPerUnit,UnitPrice,UnitsInStock," + "UnitsOnOrder,ReorderLevel, Discontinued FROM Products";
cmInitSQL.ExecuteNonQuery();
// Insert data into new table from products table in northwind
cmInitSQL.CommandText = "INSERT INTO ProductsTDS " + "(ProductName,SupplierID,CategoryID," + "QuantityPerUnit,UnitPrice,UnitsInStock," + "UnitsOnOrder,ReorderLevel, Discontinued) " + "SELECT ProductName,SupplierID,CategoryID," + "QuantityPerUnit,UnitPrice,UnitsInStock," + "UnitsOnOrder,ReorderLevel, Discontinued FROM Products";
cmInitSQL.ExecuteNonQuery();
cmInitSQL.Dispose();
// Create command object to pull data for datasets
SqlCommand cmdProductsDS = new SqlCommand("SELECT * FROM ProductsDS", con);
SqlCommand cmdProductsTDS = new SqlCommand("SELECT * FROM ProductsTDS", con);
// create instances of the dataset and typed dataset
tdsNorthwind = new Northwind();
dsNorthwind = new DataSet();
// Use the sqldataadapter to fill datasets
SqlDataAdapter daLocal = new SqlDataAdapter();
// Select command for Typed Dataset
daLocal.SelectCommand = cmdProductsTDS;
// Fill typed Dataset
daLocal.Fill(tdsNorthwind, "ProductsTDS");
// Select command for Untyped Dataset
daLocal.SelectCommand = cmdProductsDS;
// Fill untyped Dataset
daLocal.Fill(dsNorthwind, "ProductsDS");
con.Close();
}
catch (SqlException e)
{
if (Connectionstring == SQL_CONNECTION_STRING)
{
// Couldn't connect to SQL server. Now try MSDE
Connectionstring = MSDE_CONNECTION_STRING;
frmStatusMessage.Show("Connecting to MSDE");
}
else
{
// Unable to connect to SQL Server or MSDE
frmStatusMessage.Close();
MessageBox.Show("To run this sample you must have SQL Server ot MSDE with " + "the Northwind database installed. For instructions on " + "installing MSDE, view the Readme file.", "SQL Server/MSDE not found");
// Quit program if neither connection method was successful.
Application.Exit();
}
}
catch (Exception e)
{
MessageBox.Show(e.Message, "General Error");
}
}
}
// Show frmStatus to display connection status.
private void m_DAL_ConnectionStatusChange(string status)
{
frmStatusMessage = new frmStatus();
frmStatusMessage.Show(status);
}
// This routine creates the XML data document used for the hash comparison.
void CreateOriginalProductsList()
{
// Display a status message saying that the user is attempting to connect.
// This only needs to be done the very first time a connection is
// attempted. After we've determined that MSDE or SQL Server is
// installed, this message no longer needs to be displayed.
frmStatus frmStatusMessage = new frmStatus();
if (!DidPreviouslyConnect)
{
frmStatusMessage.Show("Connecting to SQL Server");
}
// Attempt to connect to the local SQL server instance, and a local
// MSDE installation (with Northwind).
bool IsConnecting = true;
while (IsConnecting)
{
try {
// The SqlConnection class allows you to communicate with SQL Server.
// The constructor accepts a connection string an argument. This
// connection string uses Integrated Security, which means that you
// must have a login in SQL Server, or be part of the Administrators
// group for this to work.
SqlConnection scnnNW = new SqlConnection(strConn);
string strSQL = "SELECT ProductID, ProductName, UnitPrice " +
"FROM Products";
// A SqlCommand object is used to execute the SQL commands.
SqlCommand scmd = new SqlCommand(strSQL, scnnNW);
// A SqlDataAdapter uses the SqlCommand object to fill a DataSet.
SqlDataAdapter sda = new SqlDataAdapter(scmd);
// Create a new Dataset and fill its first DataTable.
DataSet dsProducts = new DataSet();
sda.Fill(dsProducts);
// Persist the Dataset to XML.
try {
dsProducts.WriteXml(@"..\products.xml");
} catch (Exception exp)
{
MessageBox.Show(exp.Message, this.Text, MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
// The data has been successfully retrieved, so break out of the loop
// and close the status form.
IsConnecting = false;
DidPreviouslyConnect = true;
frmStatusMessage.Close();
} catch (SqlException expSql)
{
MessageBox.Show(expSql.Message, this.Text, MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
catch
{
if (strConn == SQL_CONNECTION_STRING)
{
// Couldn't connect to SQL Server. Now try MSDE.
strConn = MSDE_CONNECTION_STRING;
frmStatusMessage.Show("Connecting to MSDE");
}
else
{
// Unable to connect to SQL Server or MSDE
frmStatusMessage.Close();
MessageBox.Show(CONNECTION_ERROR_MSG, this.Text, MessageBoxButtons.OK, MessageBoxIcon.Warning);
Application.Exit();
}
}
}
}
private void BindDataGrid()
{
// Display a status message saying that we're attempting to connect.
// This only needs to be done the very first time a connection is
// attempted. After we've determined that MSDE or SQL Server is
// installed, this message no longer needs to be displayed.
frmStatus frmStatusMessage = new frmStatus();
if (!DidPreviouslyConnect)
{
frmStatusMessage.Show("Connecting to SQL Server");
}
// Attempt to connect to the local SQL server instance, and a local
// MSDE installation (with Northwind).
bool IsConnecting = true;
while (IsConnecting)
{
try {
// The SqlConnection class allows you to communicate with SQL Server.
// The constructor accepts a connection string an argument. This
// connection string uses Integrated Security, which means that you
// must have a login in SQL Server, or be part of the Administrators
// group for this to work.
SqlConnection northwindConnection = new SqlConnection(Connectionstring);
// The SqlDataAdapter is used to move data between SQL Server,
// and a DataSet.
SqlDataAdapter ProductAdapter = new SqlDataAdapter(
"SELECT ProductID, ProductName, UnitPrice, UnitsInStock FROM products", northwindConnection);
// Populate the Dataset with the information from the products
// table. Since a Dataset can hold multiple result sets, it's
// a good idea to "name" the result set when you populate the
// DataSet. In this case, the result set is named "Products".
ProductAdapter.Fill(ProductData, PRODUCT_TABLE_NAME);
// Data has been successfully retrieved, so break out of the loop.
IsConnecting = false;
DidPreviouslyConnect = true;
} catch
{
if (Connectionstring == SQL_CONNECTION_STRING)
{
// Couldn't connect to SQL Server. Now try MSDE.
Connectionstring = MSDE_CONNECTION_STRING;
frmStatusMessage.Show("Connecting to MSDE");
}
else
{
// Unable to connect to SQL Server or MSDE
frmStatusMessage.Close();
MessageBox.Show("To run this sample, you must have SQL " +
"or MSDE with the Northwind database installed. For " +
"instructions on installing MSDE, view the ReadMe file.");
Application.Exit();
}
}
}
frmStatusMessage.Close();
// Bind the DataGrid to the desired table in the DataSet. This
// will cause the product information to display.
grdProducts.DataSource = ProductData.Tables[PRODUCT_TABLE_NAME];
}
private void btnLoad_Click(object sender, System.EventArgs e)
{
frmStatus frmStatusMessage = new frmStatus();
if (!didPreviouslyConnect)
{
frmStatusMessage.Show("Connecting to SQL Server");
}
bool isConnecting = true;
while (isConnecting)
{
try {
// The SqlConnection class allows you to communicate with SQL Server.
// The constructor accepts a connection string an argument. This
// connection string uses Integrated Security, which means that you
// must have a login in SQL Server, or be part of the Administrators
// group for this to work. You must also have Nortiwind installed
// in either SQL Server, or the MSDE sample database. See the
// readme for details.
SqlConnection northwindConnection = new SqlConnection(connectionstring);
// The SqlDataAdapter is used to move data between SQL Server,
// and a DataSet.
SqlDataAdapter ProductAdapter = new SqlDataAdapter(
"select * from products",
northwindConnection);
// Clear out any old data that has been previously loaded into
// the DataSet
ProductData.Clear();
// Populate the Dataset with the information from the products
// table. Since a Dataset can hold multiple result sets, it's
// a good idea to "name" the result set when you populate the
// DataSet. In this case, the result set is named "Products".
ProductAdapter.Fill(ProductData, PRODUCT_TABLE_NAME);
// Bind the DataGrid to the desired table in the DataSet. This
// will cause the product information to display.
grdProducts.DataSource = ProductData.Tables[PRODUCT_TABLE_NAME];
// Now that the grid is populated, let the user filter the results.
btnFilter.Enabled = true;
isConnecting = false;
}
catch
{
if (connectionstring == SQL_CONNECTION_STRING)
{
// Couldn't connect to SQL Server. Now try MSDE.
connectionstring = MSDE_CONNECTION_STRING;
frmStatusMessage.Show("Connecting to MSDE");
}
else
{
// Unable to connect to SQL Server or MSDE
MessageBox.Show(CONNECTION_ERROR_MSG,
"Connection Failed!", MessageBoxButtons.OK,
MessageBoxIcon.Error);
Application.Exit();
}
}
}
frmStatusMessage.Close();
}
// Creates a connection to the database and uses a SqlDataAdapter
// object to fill a DataSet.
protected DataSet GetDataSource()
{
// Display a status message saying that we're attempting to connect.
// This only needs to be done the very first time a connection is
// attempted. After we've determined that MSDE or SQL Server is
// installed, this message no longer needs to be displayed.
frmStatus frmStatusMessage = new frmStatus();
DataSet dsProducts = null;
if (!DidPreviouslyConnect)
{
frmStatusMessage.Show("Connecting to SQL Server");
}
// Attempt to connect to the local SQL server instance, and a local
// MSDE installation (with Northwind).
bool IsConnecting = true;
while (IsConnecting)
{
try {
// The SqlConnection class allows you to communicate with SQL Server.
// The constructor accepts a connection string an argument. This
// connection string uses Integrated Security, which means that you
// must have a login in SQL Server, or be part of the Administrators
// group for this to work.
SqlConnection cnNorthwind = new SqlConnection(connectionstring);
// The SqlDataAdapter is used to move data between SQL Server,
// and a DataSet.
SqlDataAdapter da = new SqlDataAdapter(
"SELECT ProductID, ProductName, UnitPrice, UnitsInStock " +
"FROM products", cnNorthwind);
dsProducts = new DataSet();
// Populate the Dataset with the information from the products
// table. Since a Dataset can hold multiple result sets, it's
// a good idea to "name" the result set when you populate the
// DataSet. In this case, the result set is named "Products".
da.Fill(dsProducts, "Products");
// Data has been successfully retrieved, so break out of the loop
// and close the status form.
IsConnecting = false;
DidPreviouslyConnect = true;
frmStatusMessage.Close();
} catch
{
if (connectionstring == SQL_CONNECTION_STRING)
{
// Couldn't connect to SQL Server. Now try MSDE.
connectionstring = MSDE_CONNECTION_STRING;
frmStatusMessage.Show("Connecting to MSDE");
}
else
{
// Unable to connect to SQL Server or MSDE
frmStatusMessage.Close();
MessageBox.Show("To run this sample, you must have SQL " +
"or MSDE with the Northwind database installed. For " +
"instructions on installing MSDE, view the ReadMe file.",
"Connection Problem", MessageBoxButtons.OK,
MessageBoxIcon.Error);
Application.Exit();
}
}
}
frmStatusMessage.Close();
return(dsProducts);
}
private void frmMain_Load(object sender, System.EventArgs e)
{
string strConnection = SQL_CONNECTION_STRING;
// Display a status message saying that we're attempting to connect to SQL Server.
// This only needs to be done the very first time a connection is
// attempted. After we've determined that MSDE or SQL Server is
// installed, this message no longer needs to be displayed.
frmStatus frmStatusMessage = new frmStatus();
frmStatusMessage.Show("Connecting to SQL Server");
// Attempt to connect first to the local SQL server instance,
// if that is not successful try a local
// MSDE installation (with the Northwind DB).
bool IsConnecting = true;
while (IsConnecting)
{
try
{
// The SqlConnection class allows you to communicate with SQL Server.
// The constructor accepts a connection string an argument. This
// connection string uses Integrated Security, which means that you
// must have a login in to SQL Server, or be part of the Administrators
// group on your local machine for this to work. No password or user id is
// included in this type of string.
SqlConnection northwindConnection = new SqlConnection(strConnection);
// The SqlDataAdapter is used to populate a Dataset
SqlDataAdapter ProductAdapter = new SqlDataAdapter("SELECT ProductName,UnitPrice, UnitsInStock, UnitsOnOrder FROM products", northwindConnection);
// Populate the Dataset with the information from the products
// table. Since a Dataset can hold multiple result sets, it's
// a good idea to "name" the result set when you populate the
// DataSet. In this case, the result set is named "Products".
ProductAdapter.Fill(dsProducts, PRODUCT_TABLE_NAME);
//create the dataview; use a constructor to specify
// the sort, filter criteria for performance purposes
dvProducts = new DataView(dsProducts.Tables["products"], DEFAULT_FILTER, DEFAULT_SORT, DataViewRowState.OriginalRows);
// Data has been retrieved successfully
// Signal to break out of the loop by setting isConnecting to false.
IsConnecting = false;
//Handle the situation where a connection attempt has failed
}
catch (Exception exc)
{
if (strConnection == SQL_CONNECTION_STRING)
{
// Couldn't connect to SQL Server. Now try MSDE.
strConnection = MSDE_CONNECTION_STRING;
frmStatusMessage.Show("Connecting to MSDE");
}
else
{
// Unable to connect to SQL Server or MSDE
frmStatusMessage.Close();
MessageBox.Show("To run this sample, you must have SQL " + "or MSDE with the Northwind database installed. For " + "instructions on installing MSDE, view Readthis.", CAPTION_TITLE);
//quit the program; could not connect to either SQL Server
Application.Exit();
}
}
}
frmStatusMessage.Close();
// Bind the DataGrid to the dataview created above
grdProducts.DataSource = dvProducts;
//Populate the combo box for productName filtering.
// Allow a user to select the first letter of products that they wish to view
cboProducts.Items.AddRange(new Object[] { "<ALL>", "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" });
cboProducts.Text = "<ALL>";
//Populate the combo box for UnitsInStock filtering
// Allow the usual arithmetic comparision operators
cboCompare.Items.AddRange(new Object[] { "<", "<=", "=", ">=", ">" });
cboCompare.Text = "<";
//Display labeling status information
lblRecords.Text = STATUS_MESSAGE + this.dsProducts.Tables[PRODUCT_TABLE_NAME].Rows.Count.ToString();
grdProducts.CaptionText = CAPTION_MESSAGE_ORIG;
lblSort.Text = DEFAULT_SORT;
lblFilter.Text = DEFAULT_FILTER;
}
