SqlHelpForMsSql

最新推荐文章于 2024-04-28 18:00:00 发布

qizhanfeng

最新推荐文章于 2024-04-28 18:00:00 发布

阅读量73

点赞数

文章标签：数据库

using System;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

namespace BsdBaoBei
{
/// <summary> /// SqlHelp 的摘要说明。 /// </summary> public class SqlHelp {

private string _Connstr = null;

public SqlHelp() { //_Connstr = ConfigurationSettings.AppSettings["_Connstr"]; _Connstr = ConfigurationSettings.AppSettings["connString"]; string serverName = ConfigurationSettings.AppSettings["serverName"]; string serverPassword = ConfigurationSettings.AppSettings["serverPassWord"]; string userName = ConfigurationSettings.AppSettings["userName"]; string dbName = ConfigurationSettings.AppSettings["dbName"]; _Connstr = _Connstr.Replace("localhost",serverName); _Connstr = _Connstr.Replace("sa",userName); _Connstr = _Connstr.Replace("pwd",serverPassword); _Connstr = _Connstr.Replace("master",dbName); } public SqlHelp(string Str) { try { this._Connstr = Str;

} catch(Exception ex) { throw ex; } }

/// <summary> /// 能否连上数据库 /// </summary> /// <returns></returns> public static bool IsCanConnect(string connStr) { SqlConnection Conn = new SqlConnection(connStr); try { Conn.Open(); return true; } catch(Exception ex) { string s = ex.Message; return false; } finally { Conn.Close(); Conn.Dispose(); } }

/// <summary> /// 返回连接字符串 /// </summary> /// <returns></returns> public string ReturnConnStr() { return this._Connstr; } /// <summary> /// 返回connection对象 /// </summary> /// <returns></returns> public SqlConnection ReturnConn() { SqlConnection Conn = new SqlConnection(_Connstr); Conn.Open(); return Conn; } public void Dispose(SqlConnection Conn) { if(Conn!=null) { //if (Conn.State != ConnectionState.Closed ) Conn.Close(); Conn.Dispose(); } GC.Collect(); } /// <summary> /// 运行SQL语句 /// </summary> /// <param name="SQL"></param> public void RunProc(string SQL) { SqlConnection Conn; Conn = new SqlConnection(_Connstr); Conn.Open(); SqlCommand Cmd ; Cmd = CreateCmd(SQL, Conn); try { Cmd.ExecuteNonQuery(); } catch { throw new Exception(SQL); } finally { Dispose(Conn); } return; }

/// <summary> /// 运行SQL语句返回DataReader /// </summary> /// <param name="SQL"></param> /// <returns>SqlDataReader对象.</returns> public SqlDataReader RunProcGetReader(string SQL) { SqlConnection Conn; Conn = new SqlConnection(_Connstr); Conn.Open(); SqlCommand Cmd ; Cmd = CreateCmd(SQL, Conn); SqlDataReader rdr; try { rdr = Cmd.ExecuteReader(CommandBehavior.CloseConnection); } catch { throw new Exception(SQL); } return rdr; }

/// <summary> /// 生成Command对象 /// </summary> /// <param name="SQL"></param> /// <param name="Conn"></param> /// <returns></returns> public SqlCommand CreateCmd(string SQL, SqlConnection Conn) { SqlCommand Cmd ; Cmd = new SqlCommand(SQL, Conn); Cmd.CommandTimeout = 600; return Cmd; } /// <summary> /// 返回adapter对象 /// </summary> /// <param name="SQL"></param> /// <param name="Conn"></param> /// <returns></returns> public SqlDataAdapter CreateDa(string SQL) { SqlConnection Conn; Conn = new SqlConnection(_Connstr); Conn.Open(); SqlDataAdapter Da; Da = new SqlDataAdapter(SQL, Conn); return Da; }

/// <summary> /// 运行SQL语句,返回DataSet对象 /// </summary> /// <param name="procName">SQL语句</param> /// <param name="prams">DataSet对象</param> public DataSet RunProc(string SQL ,DataSet Ds) { SqlConnection Conn; Conn = new SqlConnection(_Connstr); Conn.Open(); SqlDataAdapter Da; //Da = CreateDa(SQL, Conn); Da = new SqlDataAdapter(SQL,Conn); try { Da.Fill(Ds); } catch(Exception Err) { throw Err; } Dispose(Conn); return Ds; } /// <summary> /// 运行SQL语句,返回DataSet对象 /// </summary> /// <param name="procName">SQL语句</param> /// <param name="prams">DataSet对象</param> /// <param name="dataReader">表名</param> public DataSet RunProc(string SQL ,DataSet Ds,string tablename) { SqlConnection Conn; Conn = new SqlConnection(_Connstr); Conn.Open(); SqlDataAdapter Da; Da = CreateDa(SQL); try { Da.Fill(Ds,tablename); } catch(Exception Ex) { throw Ex; } finally { Dispose(Conn); } return Ds; }

/// <summary> /// 运行SQL语句,返回DataSet对象 /// </summary> /// <param name="procName">SQL语句</param> /// <param name="prams">DataSet对象</param> /// <param name="dataReader">表名</param> public DataSet RunProc(string SQL , DataSet Ds ,int StartIndex ,int PageSize, string tablename ) { SqlConnection Conn; Conn = new SqlConnection(_Connstr); Conn.Open(); SqlDataAdapter Da ; Da = CreateDa(SQL); try { Da.Fill(Ds, StartIndex, PageSize, tablename); } catch(Exception Ex) { throw Ex; } finally { Dispose(Conn); } return Ds; }

/// <summary> /// 检验是否存在数据 /// </summary> /// <returns></returns> public bool ExistDate(string SQL) { SqlConnection Conn; Conn = new SqlConnection(_Connstr); Conn.Open(); SqlDataReader rdr ; rdr = CreateCmd(SQL,Conn).ExecuteReader(CommandBehavior.CloseConnection); try { if (rdr.Read()) { return true; } else { return false; } } finally { rdr.Close(); Dispose(Conn); } }

/// <summary> /// 返回SQL语句执行结果的第一行第一列 /// </summary> /// <returns>字符串</returns> public string ReturnValue(string SQL) { SqlConnection Conn; Conn = new SqlConnection(_Connstr); Conn.Open(); string result; SqlDataReader rdr ; rdr = CreateCmd(SQL,Conn).ExecuteReader(CommandBehavior.CloseConnection); try { if (rdr.Read()) { result = rdr[0].ToString(); } else { result = ""; } } catch { throw new Exception(SQL); } finally { rdr.Close(); Dispose(Conn); } return result; }

/// <summary> /// 返回SQL语句第一列,第ColumnI列, /// </summary> /// <returns>字符串</returns> public string ReturnValue(string SQL, int ColumnI) { SqlConnection Conn; Conn = new SqlConnection(_Connstr); Conn.Open(); string result; SqlDataReader rdr ; rdr = CreateCmd(SQL,Conn).ExecuteReader(CommandBehavior.CloseConnection); try { if (rdr.Read()) { result = rdr[ColumnI].ToString(); } else { result = ""; } } catch { throw new Exception(SQL); } finally { rdr.Close(); Dispose(Conn); } return result; }

/// <summary> /// 生成一个存储过程使用的sqlcommand. /// </summary> /// <param name="procName">存储过程名.</param> /// <param name="prams">存储过程入参数组.</param> /// <returns>sqlcommand对象.</returns> public SqlCommand CreateCmd(string procName, SqlParameter[] prams) { SqlConnection Conn; Conn = new SqlConnection(_Connstr); Conn.Open(); SqlCommand Cmd = new SqlCommand(procName, Conn); Cmd.CommandTimeout = 600; Cmd.CommandType = CommandType.StoredProcedure; if (prams != null) { foreach (SqlParameter parameter in prams) { if(parameter != null) { 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> /// 为存储过程生成一个SqlCommand对象 /// </summary> /// <param name="procName">存储过程名</param> /// <param name="prams">存储过程参数</param> /// <returns>SqlCommand对象</returns> private SqlCommand CreateCmd(string procName, SqlParameter[] prams,SqlDataReader rdr) { SqlConnection Conn; Conn = new SqlConnection(_Connstr); Conn.Open(); SqlCommand Cmd = new SqlCommand(procName, Conn); Cmd.CommandTimeout = 600; 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> /// <param name="prams">存储过程参数</param> /// <param name="dataReader">SqlDataReader对象</param> public void RunProc(string procName, SqlParameter[] prams,ref SqlDataReader rdr) {

SqlCommand Cmd = CreateCmd(procName, prams, rdr); rdr = Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection); return; }

/// <summary> /// 运行存储过程,返回. /// </summary> /// <param name="procName">存储过程名</param> /// <param name="prams">存储过程参数</param> public string RunProc(string procName, SqlParameter[] prams) { SqlDataReader rdr; SqlCommand Cmd = CreateCmd(procName, prams); rdr = Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection); try { if(rdr.Read()) { return rdr.GetValue(0).ToString(); } else { return ""; } } catch(Exception Ex) { throw Ex; } finally { rdr.Close(); }

} /// <summary> /// 运行存储过程,返回. /// </summary> /// <param name="procName">存储过程名</param> /// <param name="prams">存储过程参数</param> /// <param name="ReturnValue">存储过程参数</param> public void RunProc(string procName, SqlParameter[] prams,ref int ReturnValue) { SqlCommand Cmd = CreateCmd(procName, prams); Cmd.ExecuteNonQuery(); string m = Cmd.Parameters["@ReturnValue"].ToString() ; ReturnValue = Convert.ToInt32( Cmd.Parameters["@ReturnValue"].Value.ToString() ); }

/// <summary> /// 运行存储过程,返回dataset. /// </summary> /// <param name="procName">存储过程名.</param> /// <param name="prams">存储过程入参数组.</param> /// <returns>dataset对象.</returns> public DataSet RunProc(string procName,SqlParameter[] prams,DataSet Ds) { SqlCommand Cmd = CreateCmd(procName,prams); SqlDataAdapter Da = new SqlDataAdapter(Cmd); try { Da.Fill(Ds); } catch(Exception Ex) { throw Ex; } return Ds; }

/// <summary> /// 传入输入参数 /// </summary> /// <param name="ParamName">存储过程名称</param> /// <param name="DbType">参数类型</param></param> /// <param name="Size">参数大小</param> /// <param name="Value">参数值</param> /// <returns>新的 parameter 对象</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>新的 parameter 对象</returns> public SqlParameter MakeReturnParam(string ParamName, SqlDbType DbType, int Size) { return MakeParam(ParamName, DbType, Size, ParameterDirection.ReturnValue, null); } /// <summary> /// 生成存储过程参数 /// </summary> /// <param name="ParamName">存储过程名称</param> /// <param name="DbType">参数类型</param> /// <param name="Size">参数大小</param> /// <param name="Direction">参数方向</param> /// <param name="Value">参数值</param> /// <returns>新的 parameter 对象</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; } }

}

qizhanfeng

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
SqlHelpForMsSql

using System;using System.Data.SqlClient;using System.Data;using System.Configuration;namespace BsdBaoBei{&lt;wbr&gt;/// &amp;lt;summary&amp;gt;&lt;br&gt;&lt;wbr&gt;/// SqlHelp 的摘要说明。&lt;br&g...
复制链接

扫一扫