62,047
社区成员
发帖
与我相关
我的任务
分享
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;
/// <summary>
/// DataBase 数据层类
/// </summary>
public class DataBase
{
public DataBase()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
//创建连接对象
private SqlConnection con;
// 打开数据库连接
private void open()
{
if (con == null)
{
con = new SqlConnection("Data Source=localhost;DataBase=shujuku;Integrated Security=True");
}
if (con.State == System.Data.ConnectionState.Closed)
con.Open();
}
//关闭数据库链接
private void close()
{
if (con != null)
con.Close();
}
//释放数据库链接资源
public void Dispose()
{
if (con != null)
{
con.Dispose();
con = null;
}
}
// 传入参数并且转换为SqlParameter类型
public SqlParameter MakeInParam(string ParamName, SqlDbType DbType, int Size, object Value)
{
return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
}
public SqlParameter MakeParam(string ParamName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value)
{
SqlParameter param;
if (Size > 0)
param = new SqlParameter(ParamName, DbType, Size);
else
param = new SqlParameter(ParamName, DbType);
param.Direction = Direction;
if (!(Direction == ParameterDirection.Output && Value == null))
param.Value = Value;
return param;
}
//执行参数命令文本(无数据库中数据返回)
public int RunProc(string procName, SqlParameter[] prams)
{
SqlCommand cmd = CreateCommand(procName, prams);
cmd.ExecuteNonQuery();
this.close();
return (int)cmd.Parameters["ReturnValue"].Value;
}
public int RunProc(string procName)
{
this.open();
SqlCommand cmd = new SqlCommand(procName, con);
cmd.ExecuteNonQuery();
this.close();
return 1;
}
//执行参数命令文本(有返回值)
public DataSet RunProcReturn(string procName, SqlParameter[] prams, string tbName)
{
SqlDataAdapter dap = CreateDataAdaper(procName, prams);
DataSet ds = new DataSet();
dap.Fill(ds, tbName);
this.close();
return ds;
}
//得到执行成功返回值
public DataSet RunProcReturn(string procName, string tbName)
{
SqlDataAdapter dap = CreateDataAdaper(procName, null);
DataSet ds = new DataSet();
dap.Fill(ds, tbName);
this.close();
return ds;
}
//将命令文本添加到SqlDataAdapter
private SqlDataAdapter CreateDataAdaper(string procName, SqlParameter[] prams)
{
this.open();
SqlDataAdapter dap = new SqlDataAdapter(procName, con);
dap.SelectCommand.CommandType = CommandType.Text;
if (prams != null)
{
foreach (SqlParameter parameter in prams)
dap.SelectCommand.Parameters.Add(parameter);
}
dap.SelectCommand.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4,
ParameterDirection.ReturnValue, false, 0, 0,
string.Empty, DataRowVersion.Default, null));
return dap;
}
//将命令文本添加到SqlCommand
private SqlCommand CreateCommand(string procName, SqlParameter[] prams)
{
this.open();
SqlCommand cmd = new SqlCommand(procName, con);
cmd.CommandType = CommandType.Text;
if (prams != null)
{
foreach (SqlParameter parameter in prams)
cmd.Parameters.Add(parameter);
}
cmd.Parameters.Add(
new SqlParameter("ReturnValue", SqlDbType.Int, 4,
ParameterDirection.ReturnValue, false, 0, 0,
string.Empty, DataRowVersion.Default, null));
return cmd;
}
}
DataBase db = new DataBase();
db.RunProc("testProc");