但问题:1:属于那个包
2:怎么调用最好?
- 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;
- ///
- public class DataBaseHandle
- {
- #region ====== Filed & Constructor ======
- //定义SqlConnection对象
- private SqlConnection con;
- ///
- public DataBaseHandle()
- {
- }
- ///
- ///
- 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 ======
- ///
- /// 传入一个Table
- ///
- ///
- ///
- 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;
- }
- ///
- /// 传入的表
- ///
- ///
- 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;
- }
- ///
- ///
- ///
- ///
- ///
- 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 ======
- ///
- ///
- ///
- ///
- ///
- ///
- 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 ======
- ///
- ///
- ///
- ///
- ///
- ///
- 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;
- }
- ///
- ///
- ///
- ///
- ///
- 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;
- }
- ///
- ///
- ///
- ///
- ///
- 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 ======
- ///
- ///
- ///
- ///
- ///
- ///
- ///
- 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 ======
- }