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"); } } }
// Close the frmStatusMessage when the connection is completed, regardless // of success. private void m_DAL_ConnectionCompleted(string success) { frmStatusMessage.Close(); }
// 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; }