將C#對SQLserver常用的操作進行封裝，方便後續開發使用！ ...
寫在前面：
在日常的工作中，通常一個項目會大量用的資料庫的各種基本操作，因此小編幾個常見的資料庫的操作封裝成了一個dll方便後續的開發使用。SQLserver資料庫是最為常見的一種資料庫，本文則主要是記錄了C#對SQL的連接、增、刪、改、查的基本操作，如有什麼問題還請各位大佬指教。後續也將對其他幾個常用的資料庫進行相應的整理。話不多說，直接開始碼代碼。
先聲明一個SqlConnection便於後續使用。
private SqlConnection sql_con;//聲明一個SqlConnection
sql打開：
/// <summary> /// SQLserver open /// </summary> /// <param name="link">link statement</param> /// <returns>Success:success; Fail:reason</returns> public string Sqlserver_Open(string link) { try {
sql_con = new SqlConnection(link);
sql_con.Open();
return "success";
}
catch (Exception ex)
{
return ex.Message;
}
}
sql關閉：
/// <summary> /// SQLserver close /// </summary> /// <returns>Success:success Fail:reason</returns> public string Sqlserver_Close() { try { if (sql_con == null) { return "No database connection"; } if (sql_con.State == ConnectionState.Open || sql_con.State == ConnectionState.Connecting) { sql_con.Close(); sql_con.Dispose(); } else { if (sql_con.State == ConnectionState.Closed) { return "success"; } if (sql_con.State == ConnectionState.Broken) { return "ConnectionState:Broken"; } } return "success"; } catch (Exception ex) { return ex.Message; } }
sql的增刪改：
/// <summary> /// SQLserver insert,delete,update /// </summary> /// <param name="sql">insert,delete,update statement</param> /// <returns>Success:success + Number of affected rows; Fail:reason</returns> public string Sqlserver_Insdelupd(string sql) { try { int num = 0; if (sql_con == null) { return "Please open the database connection first"; } if (sql_con.State == ConnectionState.Open) { SqlCommand sqlCommand = new SqlCommand(sql, sql_con); num = sqlCommand.ExecuteNonQuery(); } else { if (sql_con.State == ConnectionState.Closed) { return "Database connection closed"; } if (sql_con.State == ConnectionState.Broken) { return "Database connection is destroyed"; } if (sql_con.State == ConnectionState.Connecting) { return "The database is in connection"; } } return "success" + num; } catch (Exception ex) { return ex.Message.ToString(); } }
sql的查：
/// <summary> /// SQLserver select /// </summary> /// <param name="sql">select statement</param> /// <param name="record">Success:success; Fail:reason</param> /// <returns>select result</returns> public DataSet Sqlserver_Select(string sql, out string record) { try { DataSet dataSet = new DataSet(); if (sql_con == null) { record = "Please open the database connection first"; return dataSet; } if (sql_con.State == ConnectionState.Open) { SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sql, sql_con); sqlDataAdapter.Fill(dataSet, "sample"); sqlDataAdapter.Dispose(); record = "success"; return dataSet; } if (sql_con.State == ConnectionState.Closed) { record = "Database connection closed"; return dataSet; } if (sql_con.State == ConnectionState.Broken) { record = "Database connection is destroyed"; return dataSet; } if (sql_con.State == ConnectionState.Connecting) { record = "The database is in connection"; return dataSet; } record = "ERROR"; return dataSet; } catch (Exception ex) { DataSet dataSet = new DataSet(); record = ex.Message.ToString(); return dataSet; } }
小編髮現以上這種封裝方式還是很麻煩，每次對SQL進行增刪改查的時候還得先打開資料庫，最後還要關閉，實際運用起來比較麻煩。因此對上面兩個增刪改查的方法進行了重載，在每次進行操作時都先打開資料庫，然後關閉資料庫。
/// <summary> /// SQLserver insert,delete,update /// </summary> /// <param name="sql">insert,delete,update statement</param> /// <param name="link">link statement</param> /// <returns>Success:success + Number of affected rows; Fail:reason</returns> public string Sqlserver_Insdelupd(string sql, string link) { try { int num = 0; using (SqlConnection con = new SqlConnection(link)) { con.Open(); SqlCommand cmd = new SqlCommand(sql, con); num = cmd.ExecuteNonQuery(); con.Close(); return "success" + num; } } catch (Exception ex) { return ex.Message.ToString(); } }
/// <summary> /// SQLserver select /// </summary> /// <param name="sql">select statement</param> /// <param name="link">link statement</param> /// <param name="record">Success:success; Fail:reason</param> /// <returns>select result</returns> public DataSet Sqlserver_Select(string sql, string link, out string record) { try { DataSet ds = new DataSet(); using (SqlConnection con = new SqlConnection(link)) { con.Open(); SqlDataAdapter sda = new SqlDataAdapter(sql, con); sda.Fill(ds, "sample"); con.Close(); sda.Dispose(); record = "success"; return ds; } } catch (Exception ex) { DataSet dataSet = new DataSet(); record = ex.Message.ToString(); return dataSet; } }
小編只是對簡單的知識進行整理，通大家分享並方便自己查看，並無惡意，如有侵權，聯繫小編，小編將立即刪除，謝謝。
