筆記（一） C#串連資料庫_使用讀取設定檔的方式

標籤：catch   tostring   static   方式   collect   query   資料庫連接   version   work   

using System;  2 using System.Collections.Generic;  3 using System.Configuration;  4 using System.Data.SqlClient;  5 using System.Linq;  6 using System.Text;  7 using System.Threading.Tasks;  8 using System.Data;  9  10 namespace Students.DAL 11 { 12     public class DBHelper 13     { 14         public static readonly string conn = ConfigurationManager.ConnectionStrings["ClassRoomConnectionString"].ToString(); 15         public static SqlConnection connection = new SqlConnection(DBHelper.conn); 16  17         /// <summary> 18         /// 增刪改資料 19         /// </summary> 20         /// <param name="sql"></param> 21         /// <returns></returns> 22         public static int ExecuteNonQuery(string sql, SqlParameter[] par) 23         { 24             try 25             { 26                 connection.Open();   //開啟資料庫連接 27                 SqlCommand comm = new SqlCommand(sql, connection); 28                 //comm.CommandType = System.Data.CommandType.StoredProcedure; 29                 comm.Parameters.AddRange(par); 30                 return comm.ExecuteNonQuery(); 31             } 32             catch 33             { 34                 throw; 35             } 36             finally 37             { 38                 connection.Close(); 39             } 40         } 41         /// <summary> 42         /// 增刪改資料 43         /// </summary> 44         /// <param name="sql"></param> 45         /// <returns></returns> 46         public static int ExecuteNonQuery(string sql, SqlParameter[] par, string sql2, SqlParameter[] par2) 47         { 48             connection.Open();   //開啟資料庫連接 49             SqlTransaction tra = connection.BeginTransaction(); 50             try 51             { 52                 SqlCommand comm = new SqlCommand(sql, connection); 53                 SqlCommand comm2 = new SqlCommand(sql2, connection); 54                 comm.Parameters.AddRange(par); 55                 comm2.Parameters.AddRange(par2); 56                 comm.Transaction = tra; 57                 comm2.Transaction = tra; 58                 int num1 = comm.ExecuteNonQuery(); 59                 int num2 = comm2.ExecuteNonQuery(); 60                 int num = comm.ExecuteNonQuery() + comm2.ExecuteNonQuery(); 61                 tra.Commit(); 62                 return num; 63             } 64             catch 65             { 66                 tra.Rollback(); 67                 throw; 68             } 69             finally 70             { 71                 connection.Close(); 72             } 73         } 74         /// <summary> 75         /// 查詢資料 76         /// </summary> 77         /// <param name="sql"></param> 78         /// <returns></returns> 79         public static SqlDataReader ExecuteReader(string sql, SqlParameter[] par) 80         { 81             try 82             { 83                 connection.Open();   //開啟資料庫連接 84                 SqlCommand comm = new SqlCommand(sql, connection); 85                 //comm.CommandType = System.Data.CommandType.StoredProcedure; 86                 comm.Parameters.AddRange(par); 87                 return comm.ExecuteReader(CommandBehavior.CloseConnection); 88             } 89             catch 90             { 91                 throw; 92             } 93         } 94         /// <summary> 95         /// 查詢資料 96         /// </summary> 97         /// <param name="sql"></param> 98         /// <returns></returns> 99         public static SqlDataReader ExecuteReader(string sql)100         {101             try102             {103                 connection.Open();   //開啟資料庫連接104                 SqlCommand comm = new SqlCommand(sql, connection);105                 return comm.ExecuteReader(CommandBehavior.CloseConnection);106             }107             catch108             {109                 throw;110             }111         }112         /// <summary>113         /// 返回單個值114         /// </summary>115         /// <param name="sql"></param>116         /// <returns></returns>117         public static object ExecuteScalar(string sql, SqlParameter[] par)118         {119             try120             {121                 connection.Open();   //開啟資料庫連接122                 SqlCommand comm = new SqlCommand(sql, connection);123                 //comm.CommandType = System.Data.CommandType.StoredProcedure;124                 comm.Parameters.AddRange(par);125                 return comm.ExecuteScalar();126             }127             catch128             {129                 throw;130             }131             finally132             {133                 connection.Close();134             }135         }136     }137 }

<?xml version="1.0" encoding="utf-8" ?> 2 <configuration> 3     <startup>  4         <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" /> 5     </startup> 6   <connectionStrings> 7     <add name ="ClassRoomConnectionString" 8            connectionString="Data Source=.;Initial Catalog=StudentDB;User ID=sa;Password=sa" 9            providerName="System.Data.SqlClient"/>10   </connectionStrings>11 </configuration>

筆記（一） C#串連資料庫_使用讀取設定檔的方式