为了方便,自己写的一个数据库共通操作类。呵呵,还是一样,注释写得挺清楚了,就不一一说了 using System; using System.Data; using System.Configuration; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using System.Data.SqlClient; using System.Windows.Forms; using System.Collections.Generic; /// <summary> /// Summary description for DataBaseHandle /// </summary> public class DataBaseHandle { #region ====== Filed & Constructor ====== //定义SqlConnection对象 private SqlConnection con; /// <summary> /// 构造方法 /// </summary> public DataBaseHandle() { } /// <summary> /// 检测DB连接成功与否 /// </summary> /// <returns></returns> private bool DBState() { bool nReturn = false; try { con = new SqlConnection("在此写上你的sql连接string"); con.Open(); nReturn = true; } catch { con.Close(); nReturn = false; } return nReturn; } #endregion ====== Filed & Constructor ====== #region ====== Insert ====== /// <summary> /// insert方法 /// </summary> /// <param name="datInsert">传入一个Table</param> /// <param name="strTableName">传入要插入的表名</param> /// <returns>0:成功</returns> /// <returns>-1:失败</returns> public int Insert(DataTable datInsert, string strTableName) { int nReturn = -1; if (!DBState() || datInsert == null || datInsert.Rows.Count == 0) { return nReturn; } //如果DB连接失败,或没传入要插入的表,则返回-1; //拼sql string strSql = GetInsertSql(datInsert, strTableName); if (string.IsNullOrEmpty(strSql)) { return nReturn; } try { //用sqlCommand执行sql SqlCommand sqlCom = new SqlCommand(strSql, con); sqlCom.ExecuteNonQuery(); nReturn = 0; } catch (Exception) { return -1; } return nReturn; } /// <summary> /// 取insert 表拼sql /// </summary> /// <param name="datInsert">传入的表</param> /// <param name="strTableName">表名</param> /// <returns>返回拼好的sql语句</returns> private string GetInsertSql(DataTable datInsert, string strTableName) { string strReturnSql = string.Empty; string strHead = " Insert into [" + strTableName + "]"; string strCols = ""; string strTempCol = string.Empty; if (datInsert == null || datInsert.Rows.Count == 0) { return strReturnSql; } for (int i = 0; i < datInsert.Columns.Count; i++) { strTempCol += "," + datInsert.Columns[i].ColumnName.ToString(); } strTempCol = strTempCol.Substring(1); strCols = " (" + strTempCol + ") " + " values "; //int nRowCount = datInsert.Rows.Count; List<string> strArrValues = new List<string>(); //string[] strArrValues = new string[2] {string.Empty,string.Empty}; int nReturn = GetInserValue(datInsert, strArrValues); if (nReturn == -1) return string.Empty; for (int j = 0; j < strArrValues.Count; j++) { strReturnSql += strHead + strCols + " (" + strArrValues[j] + ") "; } return strReturnSql; } /// <summary> /// 拼insertsql /// </summary> /// <param name="datInsert"></param> /// <param name="strArrValues"></param> /// <returns>0:成功</returns> /// <returns>-1:失败</returns> private int GetInserValue(DataTable datInsert, List<string> strArrValues) { if (datInsert == null || datInsert.Rows.Count == 0) { return -1; } string strRowValue = string.Empty; for (int i = 0; i < datInsert.Rows.Count; i++) { strRowValue = string.Empty; for (int j = 0; j < datInsert.Columns.Count; j++) { strRowValue += "," + "'" + datInsert.Rows[i][j].ToString() + "'"; } strArrValues.Add(strRowValue.Substring(1)); } return 0; } #endregion ====== Insert ====== #region ====== Delete ====== /// <summary> /// 删除记录 /// </summary> /// <param name="sqlPara">传入sqlPara以防sql注入</param> /// <param name="strAlreadySql">传入sql语句</param> /// <param name="bUseCustomSql">布尔值决定是否使用sqlPara</param> /// <returns>0:成功</returns> /// <returns>-1:失败</returns> public int Delete(SqlParameter[] sqlPara, string strAlreadySql, bool bUseCustomSql) { int nReturn = -1; if (!DBState()) //如果DB连接失败,则返回-1; { return nReturn; } if (bUseCustomSql) { SqlCommand sqlCom = new SqlCommand(strAlreadySql, con); sqlCom.ExecuteNonQuery(); } else { SqlCommand sqlCommand = new SqlCommand(strAlreadySql, con); //把防sql注入的sql变量加入sqlCommand对象 for (int i = 0; i < sqlPara.Length; i++) { sqlCommand.Parameters.Add(sqlPara[i]); } sqlCommand.ExecuteNonQuery(); } return nReturn; } #endregion ====== Delete ====== #region ====== Update ====== /// <summary> /// 更新记录 /// </summary> /// <param name="datUpdate">传入由要更新的字段及值组成的表</param> /// <param name="strTableName">传入表名</param> /// <param name="strCondition">更新条件</param> /// <returns>0:成功</returns> /// <returns>-1:失败</returns> public int Update(DataTable datUpdate, string strTableName, string[] strCondition) { int nReturn = -1; if (!DBState()) //如果DB连接失败,则返回-1; { return nReturn; } try { //拼update sql string strUpdateSql = GetUpdateSql(datUpdate, strTableName, strCondition); if (string.IsNullOrEmpty(strUpdateSql)) { return -1; } SqlCommand com = new SqlCommand(strUpdateSql, con); com.ExecuteNonQuery(); nReturn = 0; } catch (Exception) { nReturn = -1; } return nReturn; } /// <summary> /// 拼update sql /// </summary> /// <param name="datUpdate">传入要更新的表</param> /// <param name="strTableName">表名</param> /// <param name="strCondition">更新条件,[]对应要更新表的每一行的条件</param> /// <returns>返回拼完成的sql</returns> private string GetUpdateSql(DataTable datUpdate, string strTableName, string[] strCondition) { string strReturn = string.Empty; string strStart = " Update " + strTableName; List<string> strSetValues = new List<string>(); int nReturn = GetSetSql(datUpdate, strSetValues); if (nReturn == -1) { return string.Empty; } for (int i = 0; i < strSetValues.Count; i++) { strReturn += strStart + strSetValues[i] + strCondition[i]; } return strReturn; } /// <summary> /// 拼update sql /// </summary> /// <param name="datUpdate"></param> /// <param name="strSetValues"></param> /// <returns>0:成功</returns> /// <returns>-1:失败</returns> private int GetSetSql(DataTable datUpdate, List<string> strSetValues) { int nReturn = -1; if (datUpdate == null || datUpdate.Rows.Count == 0) { return -1; } try { for (int i = 0; i < datUpdate.Rows.Count; i++) { string str = string.Empty; for (int j = 0; j < datUpdate.Columns.Count; j++) { str += " , " + datUpdate.Columns[j].ColumnName + "=" + "'" + datUpdate.Rows[i][j].ToString() + "'" ; } string strSql = " Set " + " " + str.Substring(2) + " "; strSetValues.Add(strSql); } nReturn = 0; } catch(Exception) { nReturn = -1; } return nReturn; } #endregion ====== Update ====== #region ====== Search ====== /// <summary> /// 查找记录 /// </summary> /// <param name="datReturn">查打到数据用此表回传</param> /// <param name="sqlPara">用sql变量防sql注入</param> /// <param name="strAlreadySql">可以传自己写的sql</param> /// <param name="bUseCustomSql">布尔值定义是否要用自己写的sql</param> /// <returns>0:成功</returns> /// <returns>-1:失败</returns> public int Search(out DataTable datReturn, SqlParameter[] sqlPara, string strAlreadySql, bool bUseCustomSql) { datReturn = new DataTable(); int nReturn = -1; if (!DBState()) //如果DB连接失败,则返回-1; { return nReturn; } try { if (bUseCustomSql)//如果用自己写的sql { SqlCommand sqlCom = new SqlCommand(strAlreadySql, con); SqlDataAdapter dap = new SqlDataAdapter(sqlCom); dap.Fill(datReturn); nReturn = 0; } else //不用自己写的sql { SqlCommand sqlCommand = new SqlCommand(strAlreadySql, con); for (int i = 0; i < sqlPara.Length; i++) { if (sqlPara[i] != null) { sqlCommand.Parameters.Add(sqlPara[i]); } } //取得数据放在表中. SqlDataAdapter sqlDap = new SqlDataAdapter(sqlCommand); sqlDap.Fill(datReturn); nReturn = 0; } } catch(Exception) { nReturn = -1; } return nReturn; } #endregion ====== Search ====== }