SQLHelper

private SqlConnection GetConnection(string connectionString)
{  //取得conn
    SqlConnection connection = new SqlConnection(txtConnectionString.Text);
    connection.Open();

    return connection;
}

ExecuteReader

1 private void cmdSample1_Click(object sender, System.EventArgs e)
2 {
3     SqlDataReader dr = null;
4     SqlConnection connection = null;
5     try
6     {
7         try
8         {
9             connection = GetConnection(txtConnectionString.Text);
10         }
11         catch
12         {
13             MessageBox.Show("The connection with the database can磘 be established", "Application Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
14             return;
15         }
16
17         // Call ExecuteReader static method of SqlHelper class that returns a SqlDataReader
18         // We pass in database connection string, stored procedure name and a "1" as value of categoryID parameter
19         dr = SqlHelper.ExecuteReader(connection, "getProductsByCategory",  1);
20
21         // display results in textbox on the form.
22         txtResults.Clear();
23
24         // iterate through SqlDataReader
25         while (dr.Read())
26         {
27             // get the value of second column in the datareader (product description)
28             txtResults.Text = txtResults.Text + dr.GetValue(1) + Environment.NewLine;
29         }
30     }
31     catch(Exception ex)
32     {
33         string errMessage = "";
34         for( Exception tempException = ex; tempException != null ; tempException = tempException.InnerException )
35         {
36             errMessage += tempException.Message + Environment.NewLine + Environment.NewLine;
37         }
38
39         MessageBox.Show( string.Format( "There are some problems while trying to use the Data Access Application block, please check the following error messages: {0}"
40             + Environment.NewLine + "This test requires some modifications to the Northwind database. Please make sure the database has been initialized using the SetUpDataBase.bat database script, or from the  Install Quickstart option on the Start menu.", errMessage ),
41             "Application error", MessageBoxButtons.OK, MessageBoxIcon.Error );
42     }
43     finally
44     {
45         if(dr != null)
46             ((IDisposable)dr).Dispose();
47
48         if(connection != null)
49             connection.Dispose();
50     }
51 }

ExecuteDataset

1 private void cmdSample2_Click(object sender, System.EventArgs e)
2 {
3     SqlConnection connection = null;
4     try
5     {
6         try
7         {
8             connection = GetConnection(txtConnectionString.Text);
9         }
10         catch
11         {
12             MessageBox.Show("The connection with the database can磘 be established", "Application Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
13             return;
14         }
15
16         // DataSet that will hold the returned results
17         DataSet ds;
18
19         // Call ExecuteDataset static method of SqlHelper class that returns a Dataset
20         // We pass in database connection string, command type, stored procedure name and categoryID SqlParameter
21         // that has a value of "1"
22         ds = SqlHelper.ExecuteDataset(connection, CommandType.StoredProcedure, "getProductsByCategory", new SqlParameter("@CategoryID", 1) );
23         // Get XML representation of the dataset and display results in text box
24         txtResults.Text = ds.GetXml();
25     }
26     catch(Exception ex)
27     {
28         string errMessage = "";
29         for( Exception tempException = ex; tempException != null ; tempException = tempException.InnerException )
30         {
31             errMessage += tempException.Message + Environment.NewLine + Environment.NewLine;
32         }
33
34         MessageBox.Show( string.Format( "There are some problems while trying to use the Data Access Application block, please check the following error messages: {0}"
35             + Environment.NewLine + "This test requires some modifications to the Northwind database. Please make sure the database has been initialized using the SetUpDataBase.bat database script, or from the  Install Quickstart option on the Start menu.", errMessage ),
36             "Application error", MessageBoxButtons.OK, MessageBoxIcon.Error );
37     }
38     finally
39     {
40         if(connection != null)
41             connection.Dispose();
42     }
43
44 }

ExecuteScalar

1 private void cmdSample4_Click(object sender, System.EventArgs e)
2 {
3     // String variable that will hold the returned result
4     string productName;
5     // SqlConnection that will be used to execute the sql commands
6     SqlConnection connection = null;
7
8     try
9     {
10         try
11         {
12             connection = GetConnection(txtConnectionString.Text);
13         }
14         catch
15         {
16             MessageBox.Show("The connection with the database can磘 be established", "Application Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
17             return;
18         }
19
20         // Call ExecuteScalar static method of SqlHelper class that returns an Object. Then cast the return value to string.
21         // We pass in database connection string, command type, stored procedure name, and 1 as a value for ProductID SqlParameter
22         productName = (string)SqlHelper.ExecuteScalar(connection, CommandType.StoredProcedure, "getProductName", new SqlParameter("@ProductID", 1));
23         txtResults.Text = productName;
24     }
25     catch(Exception ex)
26     {
27         string errMessage = "";
28         for( Exception tempException = ex; tempException != null ; tempException = tempException.InnerException )
29         {
30             errMessage += tempException.Message + Environment.NewLine + Environment.NewLine;
31         }
32
33         MessageBox.Show( string.Format( "There are some problems while trying to use the Data Access Application block, please check the following error messages: {0}"
34             + Environment.NewLine + "This test requires some modifications to the Northwind database. Please make sure the database has been initialized using the SetUpDataBase.bat database script, or from the  Install Quickstart option on the Start menu.", errMessage ),
35             "Application error", MessageBoxButtons.OK, MessageBoxIcon.Error );
36     }
37     finally
38     {
39         if(connection != null)
40             connection.Dispose();
41     }
42 }

SqlTransaction

1 private void cmdSample5_Click(object sender, System.EventArgs e)
2 {
3     // SqlConnection that will be used to execute the sql commands
4     SqlConnection connection = null;
5     try
6     {
7         try
8         {
9             connection = GetConnection(txtConnectionString.Text);
10         }
11         catch
12         {
13             MessageBox.Show("The connection with the database can磘 be established", "Application Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
14             return;
15         }
16
17         using (SqlTransaction trans = connection.BeginTransaction())
18         {
19             // Establish command parameters
20             // @AccountNo (From Account)
21             SqlParameter paramFromAcc = new SqlParameter("@AccountNo", SqlDbType.Char, 20);
22             paramFromAcc.Value = "12345";
23
24             // @AccountNo (To Account)
25             SqlParameter paramToAcc = new SqlParameter("@AccountNo", SqlDbType.Char, 20);
26             paramToAcc.Value = "67890";
27
28             // @Money (Credit amount)
29             SqlParameter paramCreditAmount = new SqlParameter("@Amount", SqlDbType.Money );
30             paramCreditAmount.Value = 500;
31
32             // @Money (Debit amount)
33             SqlParameter paramDebitAmount = new SqlParameter("@Amount", SqlDbType.Money );
34             paramDebitAmount.Value = 500;
35
36             try
37             {
38                 // Call ExecuteNonQuery static method of SqlHelper class for debit and credit operations.
39                 // We pass in SqlTransaction object, command type, stored procedure name, and a comma delimited list of SqlParameters
40
41                 // Perform the debit operation
42                 SqlHelper.ExecuteNonQuery(trans, CommandType.StoredProcedure, "Debit", paramFromAcc, paramDebitAmount );
43
44                 // Perform the credit operation
45                 SqlHelper.ExecuteNonQuery(trans, CommandType.StoredProcedure, "Credit", paramToAcc, paramCreditAmount );
46
47                 trans.Commit();
48                 txtResults.Text = "Transfer Completed";
49
50             }
51             catch (Exception ex)
52             {
53                 // throw exception
54                 trans.Rollback();
55                 txtResults.Text = "Transfer Error";
56                 throw ex;
57             }
58         }
59     }
60     catch(Exception ex)
61     {
62         string errMessage = "";
63         for( Exception tempException = ex; tempException != null ; tempException = tempException.InnerException )
64         {
65             errMessage += tempException.Message + Environment.NewLine + Environment.NewLine;
66         }
67
68         MessageBox.Show( string.Format( "There are some problems while trying to use the Data Access Application block, please check the following error messages: {0}"
69             + Environment.NewLine + "This test requires some modifications to the Northwind database. Please make sure the database has been initialized using the SetUpDataBase.bat database script, or from the  Install Quickstart option on the Start menu.", errMessage ),
70             "Application error", MessageBoxButtons.OK, MessageBoxIcon.Error );
71     }
72     finally
73     {
74         if(connection != null)
75             connection.Dispose();
76     }
77 }

ExecuteXmlReader

1 private void cmdSample6_Click(object sender, System.EventArgs e)
2 {
3     // SqlConnection that will be used to execute the sql commands
4     SqlConnection connection = null;
5     try
6     {
7         try
8         {
9             connection = GetConnection(txtConnectionString.Text);
10         }
11         catch
12         {
13             MessageBox.Show("The connection with the database can磘 be established", "Application Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
14             return;
15         }
16
17         // Call ExecuteXmlReader static method of SqlHelper class that returns an XmlReader
18         // We pass in an open database connection object, command type, and command text
19         XmlReader xreader = SqlHelper.ExecuteXmlReader(connection, CommandType.Text, "SELECT * FROM Products FOR XML AUTO " );
20
21         // read the contents of xml reader and populate the results text box:
22         txtResults.Clear();
23         while (!xreader.EOF)
24         {
25             if(xreader.IsStartElement())
26                 txtResults.Text += xreader.ReadOuterXml() + Environment.NewLine;
27         }
28
29         // close XmlReader
30         xreader.Close();
31     }
32     catch(Exception ex)
33     {
34         string errMessage = "";
35         for( Exception tempException = ex; tempException != null ; tempException = tempException.InnerException )
36         {
37             errMessage += tempException.Message + Environment.NewLine + Environment.NewLine;
38         }
39
40         MessageBox.Show( string.Format( "There are some problems while trying to use the Data Access Application block, please check the following error messages: {0}"
41             + Environment.NewLine + "This test requires some modifications to the Northwind database. Please make sure the database has been initialized using the SetUpDataBase.bat database script, or from the  Install Quickstart option on the Start menu.", errMessage ),
42             "Application error", MessageBoxButtons.OK, MessageBoxIcon.Error );
43     }
44     finally
45     {
46         if(connection != null)
47             connection.Dispose();
48     }
49 }

地火明夷

导航

公告