private void buttonCommand_Click(object sender, EventArgs e) { //1. Make a Connection System.Data.OleDb.OleDbConnection objOleCon; objOleCon = new System.Data.OleDb.OleDbConnection(); objOleCon.ConnectionString = strConnection; objOleCon.Open(); objOleCon.ChangeDatabase("Northwind"); //We will use the Northwind database for this demo... //2. Issue a Command System.Data.OleDb.OleDbCommand objCmd; objCmd = new System.Data.OleDb.OleDbCommand(); objCmd.Connection = objOleCon; objCmd.CommandType = CommandType.Text;//Not normally typed out since Text is the default anyway //3. Process the Results //When you don't need results back, like these two examples, use ExecuteNonQuery() objCmd.CommandText = "Insert Into MyTable Values(1, 'SomeData')"; objCmd.CommandText = "Raiserror('Error Level 11 to 25 are Caught by Try-Catch', 15, 1)"; try { objCmd.ExecuteNonQuery(); // Ex. Inserts, Updates, Deletes SQL commands } catch (Exception ex) { MessageBox.Show(ex.ToString()); } //When you want a single value back use ExecuteScalar() objCmd.CommandText = "Select Count(*) From Products";//Your SQL Code int intResult = (int)objCmd.ExecuteScalar(); //Ex. Capture returning Integer data // string strResult = (string)objCmd.ExecuteScalar(); //Ex. Capture returning Varchar(50) data MessageBox.Show("Number of Products: " + intResult.ToString()); //When you want multiple results back objCmd.CommandText = "Select ProductName From Products"; //Your SQL Code System.Data.OleDb.OleDbDataReader objDR; //Class used to process multiple results objDR = objCmd.ExecuteReader(); MessageBox.Show(string.Format("FieldCount: {0}, RecordsAffected: {1} ", objDR.FieldCount, objDR.RecordsAffected)); objDR.Close(); //You can also submit multiple queries and get multiple result SETS back objCmd.CommandText = @"Select ProductName From Products ; Select OrderId, ProductId From [Order Details]"; //Your SQL Code objDR = objCmd.ExecuteReader(); MessageBox.Show(string.Format("FieldCount: {0}", objDR.FieldCount)); objDR.Read(); objDR.NextResult(); objDR.Read(); MessageBox.Show(string.Format("FieldCount: {0}", objDR.FieldCount)); objDR.Close(); //4. Clean up code objOleCon.Close(); }
private void buttonDataReader_Click(object sender, EventArgs e) { //1. Make a Connection System.Data.OleDb.OleDbConnection objOleCon; objOleCon = new System.Data.OleDb.OleDbConnection(); objOleCon.ConnectionString = strConnection; objOleCon.Open(); objOleCon.ChangeDatabase("Northwind");//We will use the Northwind database for this demo... //2. Issue a Command System.Data.OleDb.OleDbCommand objCmd; objCmd = new System.Data.OleDb.OleDbCommand(); objCmd.Connection = objOleCon; objCmd.CommandText = "Select * From Region";//Your SQL Code //3. Process the Results //When you want multiple results back System.Data.OleDb.OleDbDataReader objDR; //Class used to process multiple results objDR = objCmd.ExecuteReader(); string strData = ""; while (objDR.Read()) { strData += objDR[0] + " , " + objDR["RegionDescription"] + "\n"; comboBox1.Items.Add(objDR["RegionDescription"]); } MessageBox.Show(strData); comboBox1.SelectedIndex = 0; //4. Clean up code objDR.Close(); objOleCon.Close(); }
private void buttonDataAdapter_Click(object sender, EventArgs e) { //1. Make a Connection System.Data.OleDb.OleDbConnection objOleCon; objOleCon = new System.Data.OleDb.OleDbConnection(); objOleCon.ConnectionString = strConnection; objOleCon.Open(); objOleCon.ChangeDatabase("Northwind");//We will use the Northwind database for this demo... //2. Issue a Command System.Data.OleDb.OleDbCommand objCmd; objCmd = new System.Data.OleDb.OleDbCommand(); objCmd.Connection = objOleCon; objCmd.CommandText = "Select * From Region";//Your SQL Code //3. Process the Results System.Data.DataSet objDS = new DataSet(); System.Data.OleDb.OleDbDataAdapter objDA = new System.Data.OleDb.OleDbDataAdapter(); objDA.SelectCommand = objCmd; objDA.InsertCommand = null; objDA.UpdateCommand = null; objDA.DeleteCommand = null; objDA.Fill(objDS, "Regions"); if (objDS.Tables["Regions"].Rows.Count > 0) { string strData = ""; System.Data.DataTableReader objDTR = objDS.CreateDataReader(objDS.Tables[0]); while (objDTR.Read()) { strData += objDTR[0] + " , " + objDTR["RegionDescription"] + "\n"; // comboBox1.Items.Add(objDR["RegionDescription"]); // Not Needed } MessageBox.Show(strData); //Bind the Results to Windows Form controls comboBox2.DataSource = objDS.Tables["Regions"]; comboBox2.DisplayMember = "RegionDescription"; comboBox2.SelectedIndex = 0; } //4. Clean up code objOleCon.Close(); }
private void buttonParameters_Click(object sender, EventArgs e) { /**************************************************** * NOTE: You must create a stored procedure using the code * found in the PrameterDemoCode.sql file before you can run this! ****************************************************/ //1. Make a Connection System.Data.OleDb.OleDbConnection objOleCon; objOleCon = new System.Data.OleDb.OleDbConnection(); objOleCon.ConnectionString = strConnection; objOleCon.Open(); objOleCon.ChangeDatabase("TempDB"); //Since the previous demos used Northwind we need this command. //2. Issue a Command System.Data.OleDb.OleDbCommand objCmd; objCmd = new System.Data.OleDb.OleDbCommand(); objCmd.Connection = objOleCon; objCmd.CommandType = CommandType.StoredProcedure; objCmd.CommandText = "DivideDemo"; //2.1 Create and Add Parameters before you execute the Sproc. #region Input Parameters System.Data.OleDb.OleDbParameter objP1 = new System.Data.OleDb.OleDbParameter(); objP1.Direction = ParameterDirection.Input; objP1.DbType = DbType.Double; objP1.ParameterName = "@p1"; objP1.Value = Convert.ToDouble(textBox1.Text); System.Data.OleDb.OleDbParameter objP2 = new System.Data.OleDb.OleDbParameter(); objP2.Direction = ParameterDirection.Input; objP2.DbType = DbType.Double; objP2.ParameterName = "@p2"; objP2.Value = Convert.ToDouble(textBox2.Text); ; #endregion #region Output Parameter System.Data.OleDb.OleDbParameter objAnswer = new System.Data.OleDb.OleDbParameter(); objAnswer.Direction = ParameterDirection.Output; objAnswer.DbType = DbType.Double; objAnswer.ParameterName = "@answer"; #endregion #region Return Code System.Data.OleDb.OleDbParameter objRC = new System.Data.OleDb.OleDbParameter(); objRC.Direction = ParameterDirection.ReturnValue; objRC.DbType = DbType.Int32; objRC.ParameterName = "@RC"; #endregion //Now, add all to the Parameter Collection /* * NOTE: The order of the parameters used to call the Stored Procedure is Positional! * So, if the Store Procedure's parameter list looks like this... * * Create Proc DivideDemo( @p1 float, @p2 float, @answer float out ) * * And, you execute the Store Procedure in SQL with the following code... * * Exec @RC = DivideDemo 4, 3, @Value out; * *Then the Return Code parameter *MUST* be added first, followed by @p1 parameter, * @p2, and @answer, to the Parameters Collection like this.... * */ objCmd.Parameters.Add(objRC); //1st objCmd.Parameters.Add(objP2); //2nd objCmd.Parameters.Add(objP1); //3rd objCmd.Parameters.Add(objAnswer);//4th /*NOTE: Start a SQL Profiler Trace and show students how the command is submitted */ //3. Process the Results try { object objSelectData = objCmd.ExecuteScalar(); //Use this when there is a Select that selects a single value in the sproc. MessageBox.Show("Select data: " + Convert.ToString(objSelectData)); MessageBox.Show("Output data: " + objCmd.Parameters["@answer"].Value.ToString()); MessageBox.Show("Return Value data: " + objCmd.Parameters["@RC"].Value.ToString()); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } //4. Clean up code objOleCon.Close(); }