菜鸟询问MVC中数据库层问题,高手来~~ 在存储过程中实现,已存在的记录使用UPDATE更新,不存在的使用INSERT添加 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 ========== 写一个 通用的 数据库操作类======================namespace Utilities{ /// <summary> /// DbAccess类,即进行数据库访问时需要调用的类 /// </summary> public class DbaseBS { //取出数据库连接字符串 public static readonly string conStr = ConfigurationSettings.AppSettings["ConnectionString"]; /// <summary> /// 得到数据库连接对象 /// </summary> /// <returns>数据库连接对象</returns> public static SqlConnection GetConObject() { return new SqlConnection(conStr); } /// <summary> /// 执行操作数据库的存储过程 /// </summary> /// <param name="procName">存储过程名称</param> /// <returns>存储过程执行后所影响的行数</returns> public static int ExecuteNonQuery(string procName) { SqlCommand cmd = new SqlCommand(); using (SqlConnection con = GetConObject()) { CreateCommand(cmd, con, null, procName,null); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); cmd.Dispose(); return val; } } /// <summary> /// 执行操作数据库的存储过程 /// </summary> /// <param name="procName">存储过程名称</param> /// <param name="cmdParms">存储过程所使用的参数</param> /// <returns>存储过程执行后所影响的行数</returns> public static int ExecuteNonQuery(string procName, SqlParameter[] cmdParms) { SqlCommand cmd = new SqlCommand(); using (SqlConnection con = GetConObject()) { CreateCommand(cmd, con, null, procName, cmdParms); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); cmd.Dispose(); return val; } } /// <summary> /// 执行操作数据库的存储过程(事务控制) /// </summary> /// <param name="trans">数据库连接所关联的事务对象</param> /// <param name="procName">存储过程名称</param> /// <param name="cmdParms">存储过程所使用的参数</param> /// <returns>存储过程执行后所影响的行数</returns> public static int ExecuteNonQuery(SqlTransaction trans, string procName, SqlParameter[] cmdParms) { SqlCommand cmd = new SqlCommand(); CreateCommand(cmd, trans.Connection, trans, procName, cmdParms); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); cmd.Dispose(); return val; } /// <summary> /// 执行读数据集操作,以DataReader的形式返回 /// </summary> /// <param name="procName">存储过程名称</param> /// <param name="cmdParms">存储过程所使用的参数</param> /// <returns>DataReader对象</returns> public static SqlDataReader ExecuteReader(string procName, SqlParameter[] cmdParms) { SqlCommand cmd = new SqlCommand(); SqlConnection con = GetConObject(); try { CreateCommand(cmd, con, null, procName, cmdParms); SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); cmd.Parameters.Clear(); return dr; } catch(Exception ex) { cmd.Dispose(); Close(con); throw ex; } } /// <summary> /// 执行读数据集操作,以DataReader的形式返回 /// </summary> /// <param name="procName">存储过程名称</param> /// <returns>DataReader对象</returns> public static SqlDataReader ExecuteReader(string procName) { SqlCommand cmd = new SqlCommand(); SqlConnection con = GetConObject(); try { CreateCommand(cmd, con, null, procName, null); SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); cmd.Parameters.Clear(); return dr; } catch(Exception ex) { cmd.Dispose(); Close(con); throw ex; } } /// <summary> /// 执行读数据集操作,以DataSet的形式返回 /// </summary> /// <param name="procName">存储过程名称</param> /// <param name="cmdParms">存储过程所使用的参数</param> /// <returns>DataSet对象</returns> public static DataSet ExecuteDataSet(string procName, SqlParameter[] cmdParms) { SqlCommand cmd = new SqlCommand(); SqlConnection con = GetConObject(); DataSet ds = new DataSet(); try { CreateCommand(cmd, con, null, procName, cmdParms); SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = cmd; da.Fill(ds); cmd.Parameters.Clear(); return ds; } catch(Exception ex) { throw ex; } finally { cmd.Dispose(); Close(con); } } /// <summary> /// 执行读数据集操作,以DataSet的形式返回 /// </summary> /// <param name="procName">存储过程名称</param> /// <returns>DataReader对象</returns> public static DataSet ExecuteDataSet(string procName) { SqlCommand cmd = new SqlCommand(); SqlConnection con = GetConObject(); DataSet ds = new DataSet(); try { CreateCommand(cmd, con, null, procName, null); SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = cmd; da.Fill(ds); cmd.Parameters.Clear(); return ds; } catch(Exception ex) { throw ex; } finally { cmd.Dispose(); Close(con); } } /// <summary> /// 创建数据库执行命令 /// </summary> /// <param name="cmd">数据库执行命令对象</param> /// <param name="con">数据库连接对象</param> /// <param name="trans">数据库事务对象</param> /// <param name="procName">存储过程名称</param> /// <param name="cmdParms">存储过程所使用的参数数组</param> public static void CreateCommand(SqlCommand cmd, SqlConnection con, SqlTransaction trans, string procName, SqlParameter[] cmdParms) { if (con.State != ConnectionState.Open) con.Open(); cmd.Connection = con; cmd.CommandText = procName; if (trans != null) { cmd.Transaction = trans; } cmd.CommandType = CommandType.StoredProcedure; if (cmdParms != null) { foreach(SqlParameter parm in cmdParms) cmd.Parameters.Add(parm); } } } 传一个参数名称和参数值对应的HashTable C#通讯如何提取帧内容 判断字符串为空有几种方法,哪种效率高? C#中怎么样弄安装包 从页面点击下载,数据库中下载数据(格式可能是音频,视频),页面出现保存路径的框体 c#美化界面有没有免费的控件???????? 一个算法题 C#经典好书有哪些?asp.net的呢? [狂难问题求助]有关滚动的关联 由于数据量增大,Access数据的插入操作变得很慢 Form最小化和最大化触发什么事件 .NET 3.0 中操作剪贴板出现CLIPBRD_E_CANT_OPEN错误 请各位高手帮帮忙!!!winform下用treeview绑定xml动态生成特定的树
namespace Utilities
{ /// <summary>
/// DbAccess类,即进行数据库访问时需要调用的类
/// </summary>
public class DbaseBS
{
//取出数据库连接字符串
public static readonly string conStr = ConfigurationSettings.AppSettings["ConnectionString"];
/// <summary>
/// 得到数据库连接对象
/// </summary>
/// <returns>数据库连接对象</returns>
public static SqlConnection GetConObject()
{
return new SqlConnection(conStr);
} /// <summary>
/// 执行操作数据库的存储过程
/// </summary>
/// <param name="procName">存储过程名称</param>
/// <returns>存储过程执行后所影响的行数</returns>
public static int ExecuteNonQuery(string procName)
{
SqlCommand cmd = new SqlCommand(); using (SqlConnection con = GetConObject())
{
CreateCommand(cmd, con, null, procName,null);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
cmd.Dispose();
return val;
}
} /// <summary>
/// 执行操作数据库的存储过程
/// </summary>
/// <param name="procName">存储过程名称</param>
/// <param name="cmdParms">存储过程所使用的参数</param>
/// <returns>存储过程执行后所影响的行数</returns>
public static int ExecuteNonQuery(string procName, SqlParameter[] cmdParms)
{
SqlCommand cmd = new SqlCommand(); using (SqlConnection con = GetConObject())
{
CreateCommand(cmd, con, null, procName, cmdParms);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
cmd.Dispose();
return val;
}
} /// <summary>
/// 执行操作数据库的存储过程(事务控制)
/// </summary>
/// <param name="trans">数据库连接所关联的事务对象</param>
/// <param name="procName">存储过程名称</param>
/// <param name="cmdParms">存储过程所使用的参数</param>
/// <returns>存储过程执行后所影响的行数</returns>
public static int ExecuteNonQuery(SqlTransaction trans, string procName, SqlParameter[] cmdParms)
{
SqlCommand cmd = new SqlCommand();
CreateCommand(cmd, trans.Connection, trans, procName, cmdParms);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
cmd.Dispose();
return val;
} /// <summary>
/// 执行读数据集操作,以DataReader的形式返回
/// </summary>
/// <param name="procName">存储过程名称</param>
/// <param name="cmdParms">存储过程所使用的参数</param>
/// <returns>DataReader对象</returns>
public static SqlDataReader ExecuteReader(string procName, SqlParameter[] cmdParms)
{
SqlCommand cmd = new SqlCommand();
SqlConnection con = GetConObject(); try
{
CreateCommand(cmd, con, null, procName, cmdParms);
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return dr;
}
catch(Exception ex)
{
cmd.Dispose();
Close(con);
throw ex;
}
}
/// <summary>
/// 执行读数据集操作,以DataReader的形式返回
/// </summary>
/// <param name="procName">存储过程名称</param>
/// <returns>DataReader对象</returns>
public static SqlDataReader ExecuteReader(string procName)
{
SqlCommand cmd = new SqlCommand();
SqlConnection con = GetConObject(); try
{
CreateCommand(cmd, con, null, procName, null);
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return dr;
}
catch(Exception ex)
{
cmd.Dispose();
Close(con);
throw ex;
}
}
/// <summary>
/// 执行读数据集操作,以DataSet的形式返回
/// </summary>
/// <param name="procName">存储过程名称</param>
/// <param name="cmdParms">存储过程所使用的参数</param>
/// <returns>DataSet对象</returns>
public static DataSet ExecuteDataSet(string procName, SqlParameter[] cmdParms)
{
SqlCommand cmd = new SqlCommand();
SqlConnection con = GetConObject();
DataSet ds = new DataSet(); try
{
CreateCommand(cmd, con, null, procName, cmdParms);
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
da.Fill(ds);
cmd.Parameters.Clear();
return ds;
}
catch(Exception ex)
{
throw ex;
}
finally
{
cmd.Dispose();
Close(con);
}
}
/// <summary>
/// 执行读数据集操作,以DataSet的形式返回
/// </summary>
/// <param name="procName">存储过程名称</param>
/// <returns>DataReader对象</returns>
public static DataSet ExecuteDataSet(string procName)
{
SqlCommand cmd = new SqlCommand();
SqlConnection con = GetConObject();
DataSet ds = new DataSet(); try
{
CreateCommand(cmd, con, null, procName, null);
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
da.Fill(ds);
cmd.Parameters.Clear();
return ds;
}
catch(Exception ex)
{
throw ex;
}
finally
{
cmd.Dispose();
Close(con);
}
} /// <summary>
/// 创建数据库执行命令
/// </summary>
/// <param name="cmd">数据库执行命令对象</param>
/// <param name="con">数据库连接对象</param>
/// <param name="trans">数据库事务对象</param>
/// <param name="procName">存储过程名称</param>
/// <param name="cmdParms">存储过程所使用的参数数组</param>
public static void CreateCommand(SqlCommand cmd, SqlConnection con, SqlTransaction trans, string procName, SqlParameter[] cmdParms)
{
if (con.State != ConnectionState.Open)
con.Open(); cmd.Connection = con;
cmd.CommandText = procName; if (trans != null)
{
cmd.Transaction = trans;
} cmd.CommandType = CommandType.StoredProcedure; if (cmdParms != null)
{
foreach(SqlParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
}
}