C#連接資料庫並回傳

C#程式常需要連接到資料庫，在此記錄了幾種方式的回傳方法。

*前置動作

0.1首先設定App.config

此為winform的設定，若是撰寫Asp.net 則要設定web.config

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
    <add name ="mssqlserver" connectionString="Data Source=.\SQLEXPRESS;
         Initial Catalog=test;Integrated Security=True"/>
  </connectionStrings>
    <startup> 
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
    </startup>
</configuration>

其中 name 可以設定為自己想要的名稱，如果有不同的connectionString 也可以在此新增多種，在撰寫程式碼 的時候再選擇要用哪一種connectionString 即可。Initial Catalog 是指定要存儲的資料庫，在此的資料庫名稱為test。

0.2增加一類別，名為SqlHelper

1. 使用創建連接資料庫字串 ConnectionString

 private static readonly String connStr = ConfigurationManager.ConnectionStrings["mssqlserver"].ConnectionString;

此時需要新增一個System.Configuration的參考才能使用 ConfigurationManager 存取到App.config。
此時因為剛才在App.config 設定的 name="mssqlserver"，故可看到此時就調用"mssqlserver" 來存取剛才設定的連線字串。

2. 撰寫幾種sql 查詢後回傳種類

2.1執行insert/update/delete，回傳影響的資料列數

public static int ExecueNonQuery(string sql, CommandType cmdType, params SqlParameter[] pms) 
        {
            using (SqlConnection con = new SqlConnection(connStr))
            {
                using (SqlCommand cmd = new SqlCommand(sql, con)) 
                {
                    //設置目前執行的是「存儲過程? 還是帶參數的sql 語句?」
                    cmd.CommandType = cmdType;
                    if (pms != null) 
                    {
                        cmd.Parameters.AddRange(pms);
                    }
                    con.Open();
                    return cmd.ExecuteNonQuery();                
                }                          
            }        
        }

2.2 回傳單一個值

 public static object ExecuteScalar(string sql, CommandType cmdType, params SqlParameter[] pms) 
        {
            using (SqlConnection con = new SqlConnection(connStr))               
            {
                using (SqlCommand cmd = new SqlCommand(sql, con))
                {
                    cmd.CommandType = cmdType;
                    if (pms != null) 
                    {
                        cmd.Parameters.AddRange(pms);

                    
                    }
                    con.Open();
                    //MessageBox.Show("Login sussess!");
                    return cmd.ExecuteScalar();
                
                }
            
            }
        
        }

2.3 回傳SqlDataReader

 public static SqlDataReader ExecuteReader(string sql, CommandType cmdType, params SqlParameter[] pms)
        {
            SqlConnection con = new SqlConnection(connStr);
            using (SqlCommand cmd = new SqlCommand(sql, con))
            {
                cmd.CommandType = cmdType;
                if(pms!=null)
                {
                    cmd.Parameters.AddRange(pms);
                }
                try
                {
                    con.Open();
                    
                    return cmd.ExecuteReader(CommandBehavior.CloseConnection);

                }
                catch
                {
                    con.Close();
                    con.Dispose();
                    throw;
                }
            }

           
        }

2.4 回傳DataTable

public static DataTable ExecuteDataTable(string sql, CommandType cmdType, params SqlParameter[] pms)
        {
            DataTable dt = new DataTable();
            //use SqlDataAdapter ,it will establish Sql connection.So ,it no need to create Connection by yourself.
            using (SqlDataAdapter adapter = new SqlDataAdapter(sql, connStr)) 
            {
                adapter.SelectCommand.CommandType = cmdType;
                if (pms != null)
                {
                    adapter.SelectCommand.Parameters.AddRange(pms);

                }
                adapter.Fill(dt);
                return dt;
            }


        }

3. 調用Sqlhelper 實現比對

假設帳號及密碼都用明文儲存，則可透過以下方式連接資料庫比對

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

        private void btn_Login_Click(object sender, EventArgs e)
        {   //sql 查詢語句，
            string sql="select count(*) from users where loginId=@uid and loginPwd=@pwd";
            SqlParameter[] pms = new SqlParameter[]
            {               
                 //設定變數，帳號去除空白字元
                new SqlParameter("@uid",txtB_ID.Text.Trim()),
                new SqlParameter("@pwd",txtB_PWD.Text) //pwd allowed to use 'space' char
               
            };  
            //因為有聚合函數，回傳的是數值，故可用int轉換
            int n =(int)SqlHelper.ExecuteScalar(sql, CommandType.Text, pms);
            if(n>0)
            {
                MessageBox.Show("Login sussess!");
            }
            else
            {
                MessageBox.Show("Wrong username or password!");
            }
        }
    }
}