C SqlHelper Application Development Learning

In this paper, we share the application skills of C # SqlHelper for your reference. The specific contents are as follows

Use the App. config configuration file to encapsulate the connection string for easy reuse

--- > Add App. conifg configuration file
--- > Add : ConnectionString:
--- > Add Reference

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
 <startup> 
 <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
 </startup>
 <connectionStrings>
 <add name="conStr" connectionString="Data Source=.;Initial Catalog=;User ID=;Password="/>
 </connectionStrings>
</configuration>

Encapsulates 1 SQLHelper class for easy use

using System.Configuration;
using System.Data;//DatSet..Table SqlDataAdapter
using System.Data.SqlClient;//SqlConnection Command DataReader
namespace Common
{
 public class SqlHelper
 {
 // Connection string 
 //1 Add a reference  2 Import namespaces   In order to use ConfigurationManager
 private static string conStr = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;

 // Addition, deletion, modification and investigation 
 // Find data  ExecuteScalar() Return to the first row and first column  ExecuteReader() DataTable


 /// <summary>
 ///  Return DataTable
 /// </summary>
 /// <param name="sql"> Used sql Statement </param>
 /// <param name="param"> Variable, can pass parameters or not pass parameters </param>
 /// <returns></returns>
 public static DataTable ExecuteDataTable(string sql, params SqlParameter[] param)
 {
 DataTable dt = new DataTable();
 using (SqlConnection con = new SqlConnection(conStr))
 {
 using (SqlDataAdapter adapter = new SqlDataAdapter(sql, con))
 { 
  // Add parameters 
  adapter.SelectCommand.Parameters.AddRange(param);
  //1. Open the link, if the link is not open, it will open for you; If you open it, forget it 
  //2. To execute sql Statement to read the database 
  //3.sqlDataReader, Fill the read data into the memory table 
  adapter.Fill(dt);
 }
 }
 return dt;
 }

 /// <summary>
 ///  Execute the query and return the first row and column 
 /// </summary>
 /// <param name="sql"></param>
 /// <param name="param"></param>
 /// <returns></returns>
 public static object ExecuteScalar(string sql, params SqlParameter[] param)
 {
 object o = null;
 using (SqlConnection con = new SqlConnection(conStr))
 {
 using (SqlCommand cmd = new SqlCommand(sql, con))
 {
  cmd.Parameters.AddRange(param);
  con.Open();

  o = cmd.ExecuteScalar();
 }
 }
 return o;
 }


 /// <summary>
 ///  Execute the query and return SqlDataReader Object 
 /// </summary>
 /// <param name="sql"></param>
 /// <param name="param"></param>
 /// <returns></returns>
 public static SqlDataReader ExecuteReader(string sql, params SqlParameter[] param)
 {
 SqlDataReader reader = null;
 using (SqlConnection con = new SqlConnection(conStr))
 {
 using (SqlCommand cmd = new SqlCommand(sql, con))
 {
  cmd.Parameters.AddRange(param);
  con.Open();

  reader = cmd.ExecuteReader();
 }
 }
 return reader;
 }

 /// <summary>
 ///  Perform additions, deletions and changes to return the number of affected rows 
 /// </summary>
 /// <param name="sql"></param>
 /// <param name="param"></param>
 /// <returns></returns>
 public static int ExecuteNonQuery(string sql, params SqlParameter[] param)
 {
 int n = -1;
 using (SqlConnection con = new SqlConnection(conStr))
 {
 using (SqlCommand cmd = new SqlCommand(sql, con))
 {
  cmd.Parameters.AddRange(param);
  con.Open();
  n = cmd.ExecuteNonQuery();
 }
 }
 return n;
 }

 }
}

C # using 3 Usage Methods: https://www.ofstack.com/article/102855. htm

C # namespace: To prevent duplicate naming.
For example, you can have Student classes in two different namespaces.
This namespace scope allows you to organize your code and gives you a way to create globally only 1 types.

Usage of Sqlparamater in C #: https://www.ofstack.com/article/101015. htm
The following is the application of sqlHelper and ComboBox to show the linkage between provinces and cities:

 public partial class Form1 : Form
 {
 public Form1()
 {
 InitializeComponent();
 }

 private void Form1_Load(object sender, EventArgs e)
 {
 // When the application loads,   Go to the database to find the provincial data, give cboPro
 DataTable dt = SqlHelper.ExecuteDataTable("select * from promary");
 // The that will be returned DataTable As cboPro Data source of 
 // Jean cboPro Display proName The value of this field, 1 It is generally displayed to customers 
 cboPro.DisplayMember = "proName";
 // Jean valueMemberID Is bound to the corresponding value, and the binding handler identifies the   For programmers. 
 cboPro.ValueMember = "proID";
 cboPro.DataSource = dt;
 }

 private void cboPro_SelectedIndexChanged(object sender, EventArgs e)
 {
 // Previous writing 
 // MessageBox.Show(cboPro.Text);// Get in cbo Select text in 
 //MessageBox.Show(cboPro.SelectedValue.ToString());// Get the associated data 

 //string sql = "select * from city where proID="+cboPro.SelectedValue.ToString();
 // Parameterized sql Statement 
 string sql = "select * from city where proID=@proID";
 // Prepare 1 A sql Parameter 
 SqlParameter p = new SqlParameter("@proID", cboPro.SelectedValue.ToString());
 // Settings cboCity Data to display 
 cboCity.DisplayMember = "cityName";
 // According to sql Statement queries the dataset 
 cboCity.DataSource = SqlHelper.ExecuteDataTable(sql, p);
 }
 }