【转】c# 数据库的 sql 参数封装类的编写

【转】c# 数据库的 sql 参数封装类的编写 本文转自：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) {