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.Text; using System.Collections; namespace domain.SQLDB {
public class DbAccess { SqlConnection conn = null; SqlCommand cmd = null; public DbAccess() { // // TODO: 在此处添加构造函数逻辑 // conn = new SqlConnection(); conn.ConnectionString = Convert.ToString(System.Configuration.ConfigurationSettings.AppSettings["datasource"]); //conn.ConnectionString = "initial catalog=idyan_new;data source=.;user id=bt;password=btbtbtbt;Connect Timeout=5000"; cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandTimeout = 0; } public DbAccess(string constr) { // // TODO: 在此处添加构造函数逻辑 // conn = new SqlConnection(); conn.ConnectionString = constr;// "initial catalog=idyan_new;data source=.;user id=bt;password=btbtbtbt"; cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandTimeout = 0; } /// <summary> /// 获取数据根据sql语句 /// </summary> /// <param name="sql"></param> /// <returns></returns> public DataTable GetTable(string sql) { DataSet ds = new DataSet(); try { cmd.CommandText = sql; cmd.CommandType = CommandType.Text; SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = cmd; da.Fill(ds); } catch (Exception ex) {
this.ShowError(ex.Message); return null;
} return ds.Tables[0] ?? new DataTable(); } /// <summary> /// 获取数据根据sql语句 /// </summary> /// <param name="sql"></param> /// <returns></returns> public DataSet GetDataSet(string sql) { DataSet ds = new DataSet();
try { cmd.CommandText = sql; cmd.CommandType = CommandType.Text; SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = cmd; da.Fill(ds); } catch (Exception ex) {
this.ShowError(ex.Message); return null;
} return ds; } /// <summary> /// 获取数据根据sql语句 /// </summary> /// <param name="sql"></param> /// <returns></returns> public DataSet GetDataSet(string sql,SqlParameter[] pas) { DataSet ds = new DataSet();
try { cmd.Parameters.Clear(); cmd.CommandText = sql; foreach (SqlParameter pa in pas) { cmd.Parameters.Add(pa); } cmd.CommandType = CommandType.Text; SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = cmd; da.Fill(ds); } catch (Exception ex) {
this.ShowError(ex.Message); return null;
} return ds; } /// <summary> /// 获取数据根据sql语句 带参数 的 /// </summary> /// <param name="sql"></param> /// <param name="pas"></param> /// <returns></returns> public DataTable GetTable(string sql, params SqlParameter[] pas) { DataSet ds = new DataSet(); try { cmd.CommandText = sql; cmd.CommandType = CommandType.Text; SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = cmd; cmd.Parameters.Clear();
foreach (SqlParameter temppa in pas) { cmd.Parameters.Add(temppa); }
da.Fill(ds); } catch (Exception ex) {
this.ShowError(ex.Message); return null; } return ds.Tables[0] ?? new DataTable(); } /// <summary> /// 获取数据根据sql语句 带参数 的 /// </summary> /// <param name="sql"></param> /// <param name="pas"></param> /// <returns></returns> public DataTable GetProcTable(string procname, params SqlParameter[] pas) { DataSet ds = new DataSet(); try { cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = procname; //cmd.CommandText = sql; SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = cmd; cmd.Parameters.Clear();
foreach (SqlParameter temppa in pas) { cmd.Parameters.Add(temppa); }
da.Fill(ds); } catch (Exception ex) {
this.ShowError(ex.Message); return null; } return ds.Tables[0] ?? new DataTable(); } /// <summary> /// 获取数据根据sql语句 带参数 的 /// </summary> /// <param name="sql"></param> /// <param name="pas"></param> /// <returns></returns> public DataTable GetProcCursorTable(string procname, params SqlParameter[] pas) { DataSet ds = new DataSet(); try { cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = procname; //cmd.CommandText = sql; SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = cmd; cmd.Parameters.Clear();
foreach (SqlParameter temppa in pas) { cmd.Parameters.Add(temppa); }
da.Fill(ds); } catch (Exception ex) {
this.ShowError(ex.Message); return null; } return ds.Tables[1] ?? new DataTable(); } /// <summary> /// 获取数据根据sql语句 带参数 的 /// </summary> /// <param name="sql"></param> /// <param name="pas"></param> /// <returns></returns> public int GetProcState(string procname, params SqlParameter[] pas) { int state = 0; try { OpenConn(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = procname; cmd.Parameters.Clear(); foreach (SqlParameter temppa in pas) { cmd.Parameters.Add(temppa); } cmd.ExecuteNonQuery(); CloseConn(); state = Convert.ToInt32(pas[pas.Length - 1].Value); } catch { return 0; } return state; } /// <summary> /// 获取数据根据sql语句 带参数 的 /// </summary> /// <param name="sql"></param> /// <param name="pas"></param> /// <returns></returns> public int GetProcStateNo(string procname, params SqlParameter[] pas) { int state = 0; try { OpenConn(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = procname; cmd.Parameters.Clear(); foreach (SqlParameter temppa in pas) { cmd.Parameters.Add(temppa); } cmd.ExecuteNonQuery(); CloseConn(); state = 1; // state = Convert.ToInt32(pas[pas.Length - 1].Value);
} catch(Exception ex) {
return 0; } return state; } /// <summary> /// 根据sql语句返回跟新状态 /// </summary> /// <param name="sql"></param> /// <returns></returns> public bool GetState(string sql) { bool succ = false; try { cmd.CommandType = CommandType.Text; cmd.CommandText = sql; OpenConn(); succ = cmd.ExecuteNonQuery() > 0 ? (true) : (false); CloseConn(); } catch (Exception ex) {
this.ShowError(ex.Message); return false; } return succ;
} /// <summary> /// 根据sql语句返回跟新状态带参数的 /// </summary> /// <param name="sql">sql语句</param> /// <param name="pas">参数的集合</param> /// <returns></returns> public bool GetState(string sql, params SqlParameter[] pas) { bool succ = false; try { cmd.CommandType = CommandType.Text; cmd.CommandText = sql; cmd.Parameters.Clear();
foreach (SqlParameter temppa in pas) { cmd.Parameters.Add(temppa); } OpenConn(); succ = cmd.ExecuteNonQuery() > 0 ? (true) : (false); CloseConn(); } catch (Exception ex) {
// this.ShowError(ex.Message); //using (System.IO.StreamWriter sw = new System.IO.StreamWriter("D:\\error2008.txt")) //{ // sw.Write(ex.Message); // sw.Flush(); //} return false; } return succ;
} /// <summary> /// 根据sql语句返回第一个单元格的数据 /// </summary> /// <param name="sql"></param> /// <returns></returns> public string GetOne(string sql) { string res = ""; try { cmd.CommandType = CommandType.Text; cmd.CommandText = sql; OpenConn(); res = cmd.ExecuteScalar() == null ? ("") : (Convert.ToString(cmd.ExecuteScalar())); CloseConn(); } catch (Exception ex) {
this.ShowError(ex.Message); return null; } return res; } /// <summary> /// 根据sql语句返回第一个单元格的数据带参数的 /// </summary> /// <param name="sql"></param> /// <param name="pas"></param> /// <returns></returns> public string GetOne(string sql, params SqlParameter[] pas) { string res = ""; try { cmd.CommandType = CommandType.Text; cmd.CommandText = sql; cmd.Parameters.Clear();
foreach (SqlParameter temppa in pas) { cmd.Parameters.Add(temppa); } OpenConn(); res = cmd.ExecuteScalar() == null ? ("") : (Convert.ToString(cmd.ExecuteScalar())); CloseConn(); } catch (Exception ex) {
this.ShowError(ex.Message); return null; } return res; } /// <summary> /// 返回数据的DataReader /// </summary> /// <param name="sql"></param> /// <returns></returns> public SqlDataReader GetDataReader(string sql) { SqlDataReader dr = null; try { cmd.CommandType = CommandType.Text; conn.Open(); cmd.CommandText = sql; dr = cmd.ExecuteReader(); } catch (Exception ex) {
this.ShowError(ex.Message); return null; } return dr; } /// <summary> /// 返回数据的DataReader带参数的 /// </summary> /// <param name="sql"></param> /// <param name="pas"></param> /// <returns></returns> public SqlDataReader GetDataReader(string sql, params SqlParameter[] pas) { SqlDataReader dr = null; try { cmd.CommandType = CommandType.Text; conn.Open(); cmd.Parameters.Clear();
foreach (SqlParameter temppa in pas) { cmd.Parameters.Add(temppa); }
cmd.CommandText = sql; dr = cmd.ExecuteReader(); } catch (Exception ex) {
this.ShowError(ex.Message); return null; } return dr; } /// <summary> /// 事务处理函数 /// </summary> /// <param name="al"></param> /// <returns></returns> public bool GetTranState(ArrayList al) { cmd.CommandType = CommandType.Text; OpenConn(); SqlTransaction tran = conn.BeginTransaction(); cmd.Transaction = tran; try { for (int i = 0; i < al.Count; i++) { cmd.CommandText = Convert.ToString(al[i]); cmd.ExecuteNonQuery(); } tran.Commit(); CloseConn(); } catch (Exception ex) {
tran.Rollback(); return false; } return true;
} /// <summary> /// 事务处理函数 /// </summary> /// <param name="al"></param> /// <returns></returns> public bool GetTranStateParameter(ArrayList al) { cmd.CommandType = CommandType.Text; OpenConn(); SqlTransaction tran = conn.BeginTransaction(); cmd.Transaction = tran; try { for (int i = 0; i < al.Count; i++) { SqlTranModel stm = (SqlTranModel)al[i]; cmd.CommandText = Convert.ToString(stm.Sql); SqlParameter[] pas = stm.Pas; cmd.Parameters.Clear(); foreach (SqlParameter temp in pas) { cmd.Parameters.Add(temp); } cmd.ExecuteNonQuery(); }
tran.Commit(); CloseConn(); } catch (Exception ex) {
tran.Rollback(); return false; } return true;
} /// <summary> /// 分页函数 /// </summary> /// <param name="pagesize"></param> /// <param name="columns"></param> /// <param name="tablename"></param> /// <param name="pid"></param> /// <param name="order"></param> /// <param name="current"></param> /// <returns></returns> public DataTable GetPageData(int current,int pagesize,string columns,string tablename,string pid,string where,string order) { current = current-1>=0?(current-1):(0); string sql = string.Format("select top {0} {1} from {2} where 1=1 and {3} not in(select top {4}{3} from {2} where 1=1{5} order by {6}){5} order by {6}", pagesize, columns, tablename, pid, current * pagesize,where, order); return GetTable(sql); } /// <summary> /// 分页存储过程的调用 /// </summary> /// <param name="current"></param> /// <param name="pagesize"></param> /// <param name="columns"></param> /// <param name="tablename"></param> /// <param name="pid"></param> /// <param name="where"></param> /// <param name="order"></param> /// <returns></returns> public DataTable GetProcPageData(int current, int pagesize, string columns, string tablename, string pid, string where, string order,string ordertype) { SqlParameter[] pas = { new SqlParameter("@PageIndex", current), new SqlParameter("@PageSize", pagesize), new SqlParameter("@Columns", columns), new SqlParameter("@Tablename", tablename), new SqlParameter("@Where", where), new SqlParameter("@Order", order), new SqlParameter("@OrderType", ordertype), new SqlParameter("@Pid", pid)}; return GetProcTable("Pages", pas); //current = current - 1 >= 0 ? (current - 1) : (0); //string sql = string.Format("select top {0} {1} from {2} where 1=1 and {3} not in(select top {4}{3} from {2} where 1=1{5} order by {6}){5} order by {6}", pagesize, columns, tablename, pid, current * pagesize, where, order); //return GetTable(sql); } /// <summary> /// 分页存储过程的调用 /// </summary> /// <param name="current"></param> /// <param name="pagesize"></param> /// <param name="columns"></param> /// <param name="tablename"></param> /// <param name="pid"></param> /// <param name="where"></param> /// <param name="order"></param> /// <returns></returns> public DataTable GetProcData(int current, int pagesize, string columns, string tablename, string pid, string where, string order,string resultCount, string distinct) { SqlParameter[] pas = { new SqlParameter("@TableNames", SqlDbType.NVarChar, 200), new SqlParameter("@PrimaryKey", SqlDbType.NVarChar, 100), new SqlParameter("@Order", SqlDbType.NVarChar, 200), new SqlParameter("@CurrentPage", SqlDbType.Int), new SqlParameter("@PageSize", SqlDbType.Int), new SqlParameter("@Fields", SqlDbType.NVarChar, 800), new SqlParameter("@Filter", SqlDbType.NVarChar, 1000), new SqlParameter("@ResultCount", SqlDbType.NVarChar, 12), new SqlParameter("@distinct", SqlDbType.NVarChar, 12) }; pas[0].Value = tablename; pas[1].Value = pid; pas[2].Value = order; pas[3].Value = current; pas[4].Value = pagesize; pas[5].Value = columns; pas[6].Value = where; pas[7].Value = resultCount; pas[8].Value = distinct; return this.GetProcTable("Pages", pas); //SqlParameter[] parameters = { // new SqlParameter("@TableNames",SqlDbType.VarChar,8000), // new SqlParameter("@PrimaryKey", SqlDbType.VarChar,8000), // new SqlParameter("@Order", SqlDbType.VarChar,8000), // new SqlParameter("@CurrentPage",SqlDbType.Int), // new SqlParameter("@PageSize", SqlDbType.Int), // new SqlParameter("@Fields",SqlDbType.VarChar,8000), // new SqlParameter("@Filter", SqlDbType.VarChar,8000), // new SqlParameter("@Group", SqlDbType.VarChar,8000) // }; //parameters[0].Value = tablename; //parameters[1].Value = pid; //parameters[2].Value = order; //parameters[3].Value = current; //parameters[4].Value = pagesize; //parameters[5].Value = columns; //parameters[6].Value = where; //parameters[7].Value = ""; //DataSet ds = DbHelperSQL.RunProcedure("Pages", parameters, "data");
//return ds.Tables["data"];// //return GetProcTable("Pages", pas); //current = current - 1 >= 0 ? (current - 1) : (0); //string sql = string.Format("select top {0} {1} from {2} where 1=1 and {3} not in(select top {4}{3} from {2} where 1=1{5} order by {6}){5} order by {6}", pagesize, columns, tablename, pid, current * pagesize, where, order); //return GetTable(sql); } /// <summary> /// 分页存储过程的调用 /// </summary> /// <param name="current"></param> /// <param name="pagesize"></param> /// <param name="columns"></param> /// <param name="tablename"></param> /// <param name="pid"></param> /// <param name="where"></param> /// <param name="order"></param> /// <returns></returns> public DataTable GetProcAdminData(int current, int pagesize, string columns, string tablename, string pid, string where, string order,string resultCount, string distinct) { SqlParameter[] pas = { new SqlParameter("@TableNames", SqlDbType.NVarChar, 200), new SqlParameter("@PrimaryKey", SqlDbType.NVarChar, 100), new SqlParameter("@Order", SqlDbType.NVarChar, 200), new SqlParameter("@CurrentPage", SqlDbType.Int), new SqlParameter("@PageSize", SqlDbType.Int), new SqlParameter("@Fields", SqlDbType.NVarChar, 800), new SqlParameter("@Filter", SqlDbType.NVarChar, 200), new SqlParameter("@ResultCount", SqlDbType.NVarChar, 24), new SqlParameter("@Distinct", SqlDbType.NVarChar, 12) }; pas[0].Value = tablename; pas[1].Value = pid; pas[2].Value = order; pas[3].Value = current; pas[4].Value = pagesize; pas[5].Value = columns; pas[6].Value = where; pas[7].Value = resultCount; pas[8].Value = distinct; return this.GetProcTable("Pages", pas); } /// <summary> /// 打开连接 /// </summary> public void OpenConn() { if (conn.State != ConnectionState.Open) { try { conn.Open(); } catch (Exception ex) {
this.ShowError(ex.Message); return; } } } /// <summary> /// 关闭连接 /// </summary> public void CloseConn() { if (conn.State != ConnectionState.Closed) { try { conn.Close(); } catch (Exception ex) {
this.ShowError(ex.Message); return; } } } public void DisposeConn() { try { if (conn.State != ConnectionState.Closed) { try { conn.Close();
} catch (Exception ex) {
this.ShowError(ex.Message); return; } } } catch { } } /// <summary> /// 弹出错误的信息 /// </summary> /// <param name="err"></param> public void ShowError(string err) { // System.Web..Current.Response.Write(Script(err, "")); } /// <summary> /// 显示信息 /// </summary> /// <param name="err"></param> public void ShowMessage(string mes, string loc) { // System.Web.HttpContext.Current.Response.Write(Script(mes, loc)); } /// <summary> /// javascript脚本 /// </summary> /// <param name="mess"></param> /// <param name="loc"></param> /// <returns></returns> public string Script(string mess, string loc) { StringBuilder sb = new StringBuilder(); sb.Append("<script language='javascript'>"); sb.Append("alert('"); sb.Append(mess); sb.Append("');"); sb.Append(loc); sb.Append("</script>"); return sb.ToString();
} /// <summary> /// 弹出错误的信息 /// </summary> /// <param name="err"></param> public static void ShowErrorstatic(string err) { // System.Web.HttpContext.Current.Response.Write(DbAccess.Scriptstatic(err, "")); } /// <summary> /// 显示信息 /// </summary> /// <param name="err"></param> public static void ShowMessagestatic(string mes, string loc) { //System.Web.HttpContext.Current.Response.Write(DbAccess.Scriptstatic(mes, loc)); } //<summary> //javascript脚本 //</summary> //<param name="mess"></param> //<param name="loc"></param> //<returns></returns> public static string Scriptstatic(string mess, string loc) { StringBuilder sb = new StringBuilder(); sb.Append("<script language='javascript'>"); sb.Append("alert('"); sb.Append(mess); sb.Append("');"); sb.Append(loc); sb.Append("</script>"); return sb.ToString();
} public SqlParameter[] MakeParameters(params string[] pas) { SqlParameter[] tempPas = new SqlParameter[pas.Length / 2]; if (pas.Length % 2 == 0) { for (int i = 0; i < pas.Length / 2; i++) { tempPas[i] = new SqlParameter(pas[i * 2], pas[i * 2 + 1]); } } return tempPas;
} } public class SqlTranModel { private string _sql; public string Sql { set { this._sql = value; } get { return this._sql; } } private SqlParameter[] _pas; public SqlParameter[] Pas { set { this._pas = value; } get { return this._pas; } } } }
|
请发表评论