数据库操作类

mac2022-06-30 91

using System;using System.Data;using System.Data.SqlClient;using System.Configuration;using System.Web.UI.WebControls;//using Shaka.FileOperator;namespace Shaka.Database{ /// <summary> /// SQLHelper 的摘要说明。 /// </summary> public class SQLHelper { //数据库连接串 public string ConnString = ConfigurationSettings.AppSettings["ConnString"]; private SqlConnection conn; private SqlCommand cmd; private SqlDataAdapter myAdapter; private DataSet ds; public string filename; public int filesize;

public SQLHelper() { Open(); } /// <summary> /// 打开数据库连接 /// </summary> public void Open() { if (conn == null) { conn = new SqlConnection(ConnString); try { conn.Open(); } catch(Exception e ) { throw e; } } }

/// <summary> /// 关闭数据库连接 /// </summary> public void Close() { if (conn.State == ConnectionState.Open) conn.Close(); conn.Dispose(); conn = null; }

/// <summary> /// 创建命令对象，调用存储过程 /// </summary> /// <param name="procName">存储过程名称.</param> /// <param name="prams">存储过程参数.</param> /// <returns>返回命令对象.</returns> private SqlCommand CreateCommand(string procName, SqlParameter[] prams) { // 确保连接是打开的 Open(); cmd = new SqlCommand(procName, conn); cmd.CommandType = CommandType.StoredProcedure;

// 给存储过程添加参数 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; }

/// <summary> /// 运行存储过程 /// </summary> /// <param name="procName"></param> /// <returns></returns> public int RunProc(string procName) { cmd = CreateCommand(procName, null); cmd.ExecuteNonQuery(); return (int)cmd.Parameters["ReturnValue"].Value; }

/// <summary> /// 运行带参数的存储过程 /// </summary> /// <param name="procName">存储过程名称.</param> /// <param name="prams">存储过程参数.</param> /// <returns>返回值.</returns> public int RunProc(string procName, SqlParameter[] prams) { cmd = CreateCommand(procName, prams); cmd.ExecuteNonQuery(); return (int)cmd.Parameters["ReturnValue"].Value; }

/// <summary> /// 运行存储过程 /// </summary> /// <param name="procName">存储过程名称.</param> /// <param name="dataReader">返回值.</param> public void RunProc(string procName, out SqlDataReader dataReader) { cmd = CreateCommand(procName, null); dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection); }

/// <summary> /// 运行存储过程 /// </summary> /// <param name="procName">存储过程名称.</param> /// <param name="prams">存储过程参数.</param> /// <param name="dataReader">返回值.</param> public void RunProc(string procName, SqlParameter[] prams, out SqlDataReader dataReader) { cmd = CreateCommand(procName, prams); dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection); } public SqlDataAdapter GetAdatper(string procName,SqlParameter[] prams) { cmd = CreateCommand(procName, prams); myAdapter = new SqlDataAdapter(cmd); return myAdapter; }

/// <summary> /// 生成存储过程参数 /// </summary> /// <param name="ParamName">参数名称.</param> /// <param name="DbType">参数类型.</param> /// <param name="Size">参数大小.</param> /// <param name="Direction">参数方向.</param> /// <param name="Value">参数值.</param> /// <returns>返回新参数.</returns> 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; }

/// <summary> /// 生成输入参数 /// </summary> /// <param name="ParamName">参数名称.</param> /// <param name="DbType">参数类型.</param> /// <param name="Size">参数大小.</param> /// <param name="Value">参数值.</param> /// <returns>返回新参数.</returns> public SqlParameter MakeInParam(string ParamName, SqlDbType DbType, int Size, object Value) { return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value); }

/// <summary> /// 生成输出参数. /// </summary> /// <param name="ParamName">参数名称.</param> /// <param name="DbType">参数类型.</param> /// <param name="Size">参数大小.</param> /// <returns>返回新参数.</returns> public SqlParameter MakeOutParam(string ParamName, SqlDbType DbType, int Size) { return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, null); }

/// <summary> /// 执行单条SQL语句 /// </summary> /// <param name="strSql"></param> /// <returns></returns> public bool ExecSql(string strSql) { bool Result = false; Open(); cmd = new SqlCommand(strSql,conn); try { cmd.ExecuteNonQuery(); Result = true; } catch { } cmd.Dispose(); return Result; }

/// <summary> /// 执行多条语句 /// </summary> /// <param name="strSql"></param> /// <returns></returns> public bool ExecSql(string[] strSql) { bool Result = false; if (strSql != null) { Open(); cmd = new SqlCommand(); SqlTransaction tr ; tr = conn.BeginTransaction(); cmd.Connection = conn; cmd.Transaction = tr; try { foreach (string Sql in strSql) { cmd.CommandText = Sql; cmd.ExecuteNonQuery(); } tr.Commit(); Result = true; } catch { tr.Rollback(); Close(); throw; } } return Result; }

/// <summary> /// 得到DateSet记录集 /// </summary> /// <param name="strSQL"></param> /// <returns></returns> public DataSet GetDateSet(string strSQL) { Open(); myAdapter = new SqlDataAdapter(strSQL,conn); ds = new DataSet(); myAdapter.Fill(ds); return ds; }

/// <summary> /// 绑定DataGrid /// </summary> /// <param name="strSQL"></param> /// <param name="mydatagrid"></param> public void BindDataGrid(string strSQL,DataGrid mydatagrid) { ds = GetDateSet(strSQL); mydatagrid.DataSource=ds.Tables[0].DefaultView; mydatagrid.DataBind(); }

/// <summary> /// 得到DataReader /// </summary> /// <param name="strSQL"></param> /// <returns></returns> public SqlDataReader GetReader(string strSQL) { cmd = new SqlCommand(strSQL,conn); return cmd.ExecuteReader(); } /// <summary> /// 得到DataReader关闭数据库连接 /// </summary> /// <param name="strSQL"></param> /// <returns></returns> public SqlDataReader GetReaderCloseDb(string strSQL) { cmd = new SqlCommand(strSQL,conn); return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection); }

/// <summary> /// Get Scalar /// </summary> /// <param name="strSQL"></param> /// <returns></returns> public object GetScalar(string strSQL) { cmd = new SqlCommand(strSQL,conn); return cmd.ExecuteScalar(); }

}}

转载于:https://www.cnblogs.com/antony1029/archive/2005/11/22/282356.html

最新回复(0)