本文转自：http://xyz610561646.web64.hezuwang.com/html/200712/85/13073.htm 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; namespace ChinaSite.classes { public class DbAccess { SqlConnection conn = null; SqlCommand cmd = null; public DbAccess() { // // TODO: 在此处添加构造函数逻辑 // conn = new SqlConnection(); //conn.ConnectionString = "initial catalog=pubs;data source=.;user id=sa;password="; //conn.ConnectionString = Convert.ToString(System.Configuration.ConfigurationSettings.AppSettings["datasource"]); conn.ConnectionString = Convert.ToString(System.Configuration.ConfigurationManager.AppSettings["datasource"]); cmd = new SqlCommand(); cmd.Connection = conn; } /// <summary> /// 获取数据根据sql语句 /// </summary> /// <param name="sql"></param> /// <returns></returns> public DataTable GetTable(string sql) { DataSet ds = new DataSet(); try { cmd.CommandText = sql; 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> /// <param name="pas"></param> /// <returns></returns> public DataTable GetTable(string sql, params SqlParameter[] pas) { DataSet ds = new DataSet(); try { 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> /// <returns></returns> public bool GetState(string sql) { bool succ = false; try { cmd.CommandText = sql; conn.Open(); succ = cmd.ExecuteNonQuery() > 0 ? (true) : (false); conn.Close(); } 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.CommandText = sql; cmd.Parameters.Clear(); foreach (SqlParameter temppa in pas) { cmd.Parameters.Add(temppa); } conn.Open(); succ = cmd.ExecuteNonQuery() > 0 ? (true) : (false); conn.Close(); } catch (Exception ex) { this.ShowError(ex.Message); return false; } return succ; } /// <summary> /// 根据sql语句返回第一个单元格的数据 /// </summary> /// <param name="sql"></param> /// <returns></returns> public string GetOne(string sql) { string res = ""; try { cmd.CommandText = sql; conn.Open(); res = cmd.ExecuteScalar() == null ? ("") : (Convert.ToString(cmd.ExecuteScalar())); conn.Close(); } 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.CommandText = sql; cmd.Parameters.Clear(); foreach (SqlParameter temppa in pas) { cmd.Parameters.Add(temppa); } conn.Open(); res = cmd.ExecuteScalar() == null ? ("") : (Convert.ToString(cmd.ExecuteScalar())); conn.Close(); } 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 { 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 { 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> 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(); cmd = null; conn = null; } catch (Exception ex) { this.ShowError(ex.Message); return; } } } /// <summary> /// 弹出错误的信息 /// </summary> /// <param name="err"></param> public void ShowError(string err) { System.Web.HttpContext.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("alter('"); sb.Append(mess); sb.Append("');"); sb.Append(loc); sb.Append("</script>"); return sb.ToString(); } } }
本文来自: 天空618(www.sky618.com) 详细出处参考：http://xyz610561646.web64.hezuwang.com/html/200712/85/13073.htm
转载于:https://www.cnblogs.com/feima-lxl/archive/2008/06/26/1230671.html
