C#_DBHelper_SQL資料庫操作類.

標籤：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
 1 public class DBHelper 2     { 3         static string connStr = "Workstation id = localhost;" + 4                                "Integrated Security = SSPI;" +  5                                 "Database = 資料庫名字;";//填入SQL資料庫的登入資訊。 6  7         8         static SqlConnection conn = new SqlConnection(connStr); 9 10         public static SqlDataReader GetDataReader(string sql)//傳回值是一個數組，可通過dr[0],dr[1]使用各欄位的值11         {12             SqlConnection myConn = conn;13             SqlDataReader dr = null;14 15             try16             {17                 if (myConn.State == ConnectionState.Closed)18                 {19                     myConn.Open();20                 }21                 SqlCommand cmd = new SqlCommand(sql, myConn);22                23                 dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);24             }25             catch 26             {27                 28                 if (myConn.State == ConnectionState.Open)29                 {30                     myConn.Close();31                 }32 33             }34             return dr;35         }36 37         public static bool ExecuteNonQuery(string sql)  //對於 UPDATE、INSERT 和 DELETE 語句，傳回值為該命令所影響的行數。對於所有其他類型的語句，傳回值為 -1。如果發生復原，傳回值也為 -138         {39             int n = 0;40 41             try42             {43                 if (conn.State == ConnectionState.Closed)44                 {45                     conn.Open();46                 }47                 SqlCommand cmd = new SqlCommand(sql, conn);48                 n = cmd.ExecuteNonQuery();49                 50             }51             catch52             {53                 54                 return false;55             }56             finally57             {58                 if (conn.State == ConnectionState.Open)59                 {60                     conn.Close();61                 }62             }63             return n > 0;64         }65 66         public static Object ExecuteScalar(string sql)//使用ExecuteScalar()，增刪改查如果成功，會返回一個對象，否則會返回一個null；67         {68             Object ob = null;69 70             try71             {72                 if (conn.State == ConnectionState.Closed)73                 {74                     conn.Open();75                 }76                 SqlCommand cmd = new SqlCommand(sql, conn);77                 ob = cmd.ExecuteScalar();78             }79             catch80             {81                 return null;82             }83             finally84             {85                 if (conn.State == ConnectionState.Open)86                 {87                     conn.Close();88                 }89             }90             return ob;91         }92     }93 }

 

C#_DBHelper_SQL資料庫操作類.