想做一个SqlHelper类,里面包含显示、添加、修改和删除数据的方法,方便调用。不知怎么写?
类似下面这样:
public class SqlHelper
{
//获取连接字符串
public static readonly string ConnectionString = ConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString;
//用哈希表存储缓存的参数信息
private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
public SqlHelper()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
public static DataSet FillDataSet(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(connectionString);
try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
SqlDataAdapter adp=new SqlDataAdapter ();
adp.SelectCommand = cmd;
DataSet ds=new DataSet ();
adp.Fill(ds);//, "Table1"
conn.Close();
if (ds.Tables[0].Rows.Count > 0)
return ds;
else return null;
}
catch
{
conn.Close ();
throw ;
}
}
还有我想用DataList控件显示数据,在SqlHelper类中能不能像下面这样写?
public bool ecadabind(GridView gv, string sqlstr4)
{
SqlConnection con = this.getcon();
con.Open();
SqlDataAdapter mydataadapter = new SqlDataAdapter(sqlstr4, con);
DataSet mydataset = new DataSet();
mydataadapter.Fill(mydataset);
gv.DataSource = mydataset;
try
{
gv.DataBind();
return true;
}
}
谢谢!!
类似下面这样:
public class SqlHelper
{
//获取连接字符串
public static readonly string ConnectionString = ConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString;
//用哈希表存储缓存的参数信息
private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
public SqlHelper()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
public static DataSet FillDataSet(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(connectionString);
try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
SqlDataAdapter adp=new SqlDataAdapter ();
adp.SelectCommand = cmd;
DataSet ds=new DataSet ();
adp.Fill(ds);//, "Table1"
conn.Close();
if (ds.Tables[0].Rows.Count > 0)
return ds;
else return null;
}
catch
{
conn.Close ();
throw ;
}
}
还有我想用DataList控件显示数据,在SqlHelper类中能不能像下面这样写?
public bool ecadabind(GridView gv, string sqlstr4)
{
SqlConnection con = this.getcon();
con.Open();
SqlDataAdapter mydataadapter = new SqlDataAdapter(sqlstr4, con);
DataSet mydataset = new DataSet();
mydataadapter.Fill(mydataset);
gv.DataSource = mydataset;
try
{
gv.DataBind();
return true;
}
}
谢谢!!
using System;
using System.Collections.Generic;
using System.Text;using System.Data;
using System.Data.SqlClient;
using System.Reflection;
using System.Configuration;namespace DAL
{
public class SqlHelper
{
#region 变量
private static SqlConnection con = null;
private static string assemblyName = "Model";
public static string constr = ConfigurationManager.ConnectionStrings["connData"].ConnectionString;
#endregion #region 属性
public static SqlConnection Con
{
get
{
if (SqlHelper.con == null)
{
SqlHelper.con = new SqlConnection();
}
if (SqlHelper.con.ConnectionString == "")
{
SqlHelper.con.ConnectionString = SqlHelper.constr;
}
return SqlHelper.con;
}
set
{
SqlHelper.con = value;
}
}
#endregion #region 方法
#region 查询单个
public static int ExecuteScalar(string sql, CommandType commandTypeName, params SqlParameter[] p)
{
int count = 0;
using (SqlHelper.Con)
{
using (SqlCommand cmd = new SqlCommand(sql, SqlHelper.Con))
{
try
{
cmd.CommandType = commandTypeName;
if (p != null)
{
cmd.Parameters.AddRange(p);
}
SqlHelper.Con.Open();
count = Convert.ToInt32(cmd.ExecuteScalar());
}
catch (Exception ex)
{
throw ex;
}
}
}
return count;
}
#endregion
#region 增删改
public static int ExecuteNonQuery(string sql, CommandType commandTypeName, params SqlParameter[] p)
{
int result = 0;
using (SqlHelper.Con)
{
using (SqlCommand cmd = new SqlCommand(sql, SqlHelper.Con))
{
cmd.CommandType = commandTypeName;
if (p != null)
{
cmd.Parameters.AddRange(p);
SqlHelper.Con.Open();
result = cmd.ExecuteNonQuery();
}
}
}
return result; }
#endregion
#region 读取结果集
private static T ResultSet<T>(IDataReader reader)
{
T obj = default(T);
try
{
Type type = typeof(T);
obj = (T)Assembly.Load(SqlHelper.assemblyName).CreateInstance(type.FullName);
PropertyInfo[] ps = type.GetProperties(); foreach (PropertyInfo p in ps)
{
for (int i = 0; i < reader.FieldCount; i++)
{
string filedName = reader.GetName(i);//
if (filedName.ToLower() == p.Name.ToLower())
{
Object v = reader[filedName];
if (v != null && v != DBNull.Value)
{
p.SetValue(obj, v, null);
break;
}
}
}
}
}
catch (Exception ex)
{
throw ex;
}
return obj; }
#endregion
#region 查询所有
public static List<T> ExecuteList<T>(string sql, CommandType commandType, params SqlParameter[] param)
{
List<T> list = new List<T>();
using (SqlHelper.Con)
{
using (SqlCommand cmd = new SqlCommand(sql, SqlHelper.Con))
{
try
{
cmd.CommandType = commandType;
if (param != null)
{
cmd.Parameters.AddRange(param);
}
SqlHelper.Con.Open();
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (reader.Read())
{
T obj = SqlHelper.ResultSet<T>(reader);
list.Add(obj);
}
}
catch (Exception ex)
{
throw ex;
}
}
}
return list;
}
#endregion
#region 查询单个数据
public static T ExecuteEntity<T>(string commandText, CommandType commandType, params SqlParameter[] param)
{
T obj = default(T);
using (SqlHelper.Con)
{
using (SqlCommand cmd = new SqlCommand(commandText, SqlHelper.Con))
{
cmd.CommandType = commandType;
if (param != null)
{
cmd.Parameters.AddRange(param);
}
SqlHelper.Con.Open();
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (reader.Read())
{
obj = SqlHelper.ResultSet<T>(reader);
}
} }
return obj;
}
#endregion
#endregion
}
}
页面上的控件直接绑定这个方法
gv.DataSource = FillDataSet();
gv.DataBind();
string UserName = name.Text.Trim();
string PassWord = pwd.Text.Trim();
string sql = "select * from Admin where admin='" + UserName + "' and password='" + PassWord + "'";
DataSet ds = SqlHelper.FillDataSet(SqlHelper.ConnectionString, CommandType.Text, sql, null); if (ds.Tables[0].Rows.Count > 0)
{
Session["adminname"] = this.name.Text;
Response.Write("<script>location.href=('admin_main.htm');</script>");
}
else
{
MessageBox.ShowAndRedirect(this, "用户名或密码错误!", "default.aspx");
}
public class SqlHelper
{
private static SqlConnection cn = null;
private static SqlCommand cmd = null; public SqlHelper()
{ } /// <summary>
/// 判断连接状态
/// </summary>
/// <returns>返回连接状态</returns>
private static SqlConnection GetConn()
{
string ConnStr = M_Common.Helper.ConfigHelper.GetconnMsSql.ToString();
cn = new SqlConnection(ConnStr);
if (cn.State != ConnectionState.Open)
{
cn.Open();
}
return cn;
}
/// <summary>
/// 获取某表的某个字段的最大值
/// </summary>
/// <param name="FieldName">字段名</param>
/// <param name="TableName">表明</param>
/// <returns>返回最大值</returns>
public static int GetMaxID(string FieldName, string TableName)
{
string strsql = "select max(" + FieldName + ")+1 from " + TableName;
object obj = SqlHelper.GetSingle(strsql);
if (obj == null)
{
return 1;
}
else
{
return int.Parse(obj.ToString());
}
}
/// <summary>
/// 执行一条计算查询结果语句,返回查询结果(object)。
/// </summary>
/// <param name="SQLString">计算查询结果语句</param>
/// <returns>查询结果(object)</returns>
public static object GetSingle(string SQLString)
{
using (SqlCommand cmd = new SqlCommand(SQLString, GetConn()))
{
try
{
object obj = cmd.ExecuteScalar();
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
return null;
}
else
{
return obj;
}
}
catch (System.Data.SqlClient.SqlException e)
{
throw e;
}
} } /// <summary>
/// 带参数返回一行一列ExecuteScalar
/// </summary>
/// <param name="cmdtext">存储过程或者SQL语句</param>
/// <param name="para">参数数组</param>
/// <param name="ct">命令类型</param>
/// <returns>返回一行一列value</returns>
public static int ExecuteScalar(string cmdtext, SqlParameter[] para, CommandType ct)
{
int value;
try
{
cmd = new SqlCommand(cmdtext, GetConn());
cmd.CommandType = ct;
cmd.Parameters.AddRange(para);
value = Convert.ToInt32(cmd.ExecuteScalar());
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (cn.State == ConnectionState.Open)
{
cn.Close();
}
}
return value;
}
/// <summary>
/// 执行无参的操作
/// </summary>
/// <param name="cmdtext">SQL语句或存储过程</param>
/// <param name="ct">CMD的类型</param>
/// <returns>处理后的值</returns>
public static int ExecuteNonQuery(string cmdtext, CommandType ct)
{
int value;
try
{
cmd = new SqlCommand(cmdtext, GetConn());
cmd.CommandType = ct;
value = cmd.ExecuteNonQuery() > 0 ? 1 : 0;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (cn.State == ConnectionState.Open)
{
cn.Close();
}
}
return value;
} /// <summary>
/// 执行带参的增.删.改操作
/// </summary>
/// <param name="cmdtext">SQL语句或存储过程</param>
/// <param name="para">参数数组</param>
/// <param name="ct">CMD类型</param>
/// <returns>处理后的值</returns>
public static int ExecuteNonQuery(string cmdtext, SqlParameter[] para, CommandType ct)
{
int value;
using (cmd = new SqlCommand(cmdtext, GetConn()))
{
cmd.CommandType = ct;
cmd.Parameters.AddRange(para);
value = cmd.ExecuteNonQuery() > 0 ? 1 : 0;
}
return value;
} /// <summary>
/// 执行无参的查询 返回DataTable
/// </summary>
/// <param name="cmdtext">存储过程名称或SQL语句</param>
/// <param name="ct">命令类型</param>
/// <returns>返回DataTable</returns>
public static DataTable ReturnDataTable(string cmdtext, CommandType ct)
{
DataTable dt = new DataTable();
cmd = new SqlCommand(cmdtext, GetConn());
//类型
cmd.CommandType = ct;
SqlDataReader dr = null;
//连接池 读完自动释放Connection
using (dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
//用委托填充DataTable
dt.Load(dr);
}
return dt;
} /// <summary>
/// 执行有参的查询 返回DataTable
/// </summary>
/// <param name="cmdtext">存储过程名称或SQL语句</param>
/// <param name="ct">命令类型</param>
/// <param name="para">参数数组</param>
/// <returns>返回DataTable</returns>
public static DataTable ReturnDataTable(string cmdtext, CommandType ct, SqlParameter[] para)
{
DataTable dt = new DataTable();
cmd = new SqlCommand(cmdtext, GetConn());
//类型
cmd.CommandType = ct;
//参数数组
cmd.Parameters.AddRange(para);
SqlDataReader dr = null;
//连接池 读完自动释放Connection
using (dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
//用委托填充DataTable
dt.Load(dr);
}
return dt;
} /// <summary>
/// 执行无参的查询 返回DataSet
/// </summary>
/// <param name="cmdtext">存储过程名称或SQL语句</param>
/// <param name="ct">命令类型</param>
/// <returns>返回DataSet</returns>
public static DataSet ReturnDataSet(string cmdtext, CommandType ct)
{
cmd = new SqlCommand(cmdtext, GetConn());
//类型
cmd.CommandType = ct;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
try
{
da.Fill(ds);
}
catch (Exception err)
{ throw err;
}
finally
{
if (cn.State != ConnectionState.Closed)
{
cn.Close();
}
}
return ds;
}
/// <summary>
/// 执行有参的查询 返回DataSet
/// </summary>
/// <param name="cmdtext">存储过程名称或SQL语句</param>
/// <param name="ct">命令类型</param>
/// <param name="para">参数数组</param>
/// <returns>返回DataSet</returns>
public static DataSet ReturnDataSet(string cmdtext, CommandType ct, SqlParameter[] para)
{
cmd = new SqlCommand(cmdtext, GetConn());
//类型
cmd.CommandType = ct;
//参数数组
cmd.Parameters.AddRange(para);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
try
{
da.Fill(ds);
}
catch (Exception err)
{ throw err;
}
finally
{
if (cn.State != ConnectionState.Closed)
{
cn.Close();
}
}
return ds;
} }没啥问题 最好用ExecuteScalar来判断。
CS0103: 当前上下文中不存在名称“MessageBox”
我加了using System.Windows.Forms;后又提示
“System”中不存在类型或命名空间名称“Windows”(是缺少程序集引用吗?)
原来用ACCESS数据库是没这个提示的哦?
另ExecuteScalar来判断怎么去判断?谢谢!!
string sql = "insert into info(title,classid,nclassid,DefaultPic,pic,author,content,writefrom,address,waddress,flag) values('" + title + "'," + classid + "," + nclassid + ",'" + DefaultPic + "'," + pic + ",'" + author + "','" + content + "','" + writefrom + "','" + address + "','" + waddress + "'," + vflag + ")";
int cmd=SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, CommandType.Text, sql, null);
这样调用行不行?怎么会出现这样的提示:
回发或回调参数无效。在配置中使用 <pages enableEventValidation="true"/>.......