SqlHelper DBHelper

By admin in 4858.com on 2019年5月4日

据他们说自个儿项目标开采需求,整理了1个SqlHelper类

using System;
using System.Collections;
using System.Collections.Specialized;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

柯乐义SQLServer访问类选择C#.NET语言编写,功效庞大。使用柯乐义SQLServer访问类能够飞快便民的访问SQL数据库。柯乐义SQLServer访问类包罗实施不难SQL语句,施行带参数的SQL语句,存款和储蓄进度操作3有的。

 

绝相比较英特网通用的SqlHelper类方法主要有须臾间几点的两样:

namespace LiTianPing.SQLServerDAL //能够修改成实际上项目标命名空间名称
{
 /// <summary>
 /// Copyright (C) 2004-2008 LiTianPing
 /// 数据访问基础类(基于SQLServer)
 /// 用户可以修改满意自个儿项目标须要。
 /// </summary>
 public abstract class DbHelperSQL
 {
  //数据库连接字符串(web.config来布局)
  //<add key=”ConnectionString”
value=”server=127.0.0.1;database=DATABASE;uid=sa;pwd=” />  
  protected static string connectionString =
ConfigurationSettings.AppSettings[“ConnectionString”];
  public DbHelperSQL()
  {   
  }
  
  #region  试行简单SQL语句

上面是类的完全代码:
using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;

应用程序大诸多都以亟需与数据库交互做为支撑的,那么相对数据库的造访格局就卓越重大了,最基础的是经过Ado的情势了;还有正是部分绝对的O凯雷德M框架,如EF,Nhibernate等,后续加入;
相应的命名空间本身对应导入就可以

壹.因为要操作多少个数据库,所以数据库连接字符串没有写死到点子里,作为参数提供出来。

  /// <summary>
  /// 施行SQL语句,再次来到影响的记录数
  /// </summary>
  /// <param name=”SQLString”>SQL语句</param>
  /// <returns>影响的笔录数</returns>
  public static int ExecuteSql(string SQLString)
  {
   using (SqlConnection connection = new
SqlConnection(connectionString))
   {    
    using (SqlCommand cmd = new SqlCommand(SQLString,connection))
    {
     try
     {  
      connection.Open();
      int rows=cmd.ExecuteNonQuery();
      return rows;
     }
     catch(System.Data.SqlClient.SqlException E)
     {     
      connection.Close();
      throw new Exception(E.Message);
     }
    }    
   }
  }
  
  /// <summary>
  /// 实施多条SQL语句,达成数据库事务。
  /// </summary>
  /// <param name=”SQLStringList”>多条SQL语句</param>  
  public static void ExecuteSqlTran(ArrayList SQLStringList)
  {
   using (SqlConnection conn = new SqlConnection(connectionString))
   {
    conn.Open();
    SqlCommand cmd = new SqlCommand();
    cmd.Connection=conn;    
    SqlTransaction tx=conn.BeginTransaction();   
    cmd.Transaction=tx;    
    try
    {     
     for(int n=0;n<SQLStringList.Count;n++)
     {
      string strsql=SQLStringList[n].ToString();
      if (strsql.Trim().Length>1)
      {
       cmd.CommandText=strsql;
       cmd.ExecuteNonQuery();
      }
     }          
     tx.Commit();     
    }
    catch(System.Data.SqlClient.SqlException E)
    {  
     tx.Rollback();
     throw new Exception(E.Message);
    }
   }
  }
  /// <summary>
  /// 推行带四个积存进程参数的的SQL语句。
  /// </summary>
  /// <param name=”SQLString”>SQL语句</param>
  /// <param
name=”content”>参数内容,比如2个字段是格式复杂的稿子,有特殊符号,能够透过那个艺术增多</param>
  /// <returns>影响的笔录数</returns>
  public static int ExecuteSql(string SQLString,string content)
  {    
   using (SqlConnection connection = new
SqlConnection(connectionString))
   {
    SqlCommand cmd = new SqlCommand(SQLString,connection);  
    System.Data.SqlClient.SqlParameter  myParameter = new
System.Data.SqlClient.SqlParameter ( “@content”, SqlDbType.NText);
    myParameter.Value = content ;
    cmd.Parameters.Add(myParameter);
    try
    {
     connection.Open();
     int rows=cmd.ExecuteNonQuery();
     return rows;
    }
    catch(System.Data.SqlClient.SqlException E)
    {    
     throw new Exception(E.Message);
    }
    finally
    {
     cmd.Dispose();
     connection.Close();
    } 
   }
  }  
  /// <summary>
SqlHelper DBHelper。  /// 向数据Curry插入图像格式的字段(和下边意况类似的另一种实例)
  /// </summary>
  /// <param name=”strSQL”>SQL语句</param>
  /// <param
name=”fs”>图像字节,数据库的字段类型为image的场馆</param>
  /// <returns>影响的记录数</returns>
  public static int ExecuteSqlInsertImg(string strSQL,byte[] fs)
  {  
   using (SqlConnection connection = new
SqlConnection(connectionString))
   {
    SqlCommand cmd = new SqlCommand(strSQL,connection); 
    System.Data.SqlClient.SqlParameter  myParameter = new
System.Data.SqlClient.SqlParameter ( “@fs”, SqlDbType.Image);
    myParameter.Value = fs ;
    cmd.Parameters.Add(myParameter);
    try
    {
     connection.Open();
     int rows=cmd.ExecuteNonQuery();
     return rows;
    }
    catch(System.Data.SqlClient.SqlException E)
    {    
     throw new Exception(E.Message);
    }
    finally
    {
     cmd.Dispose();
     connection.Close();
    }    
   }
  }
  
  /// <summary>
  /// 实行一条计算查询结果语句,重返查询结果(object)。
  /// </summary>
  /// <param name=”SQLString”>总结查询结果语句</param>
  /// <returns>查询结果(object)</returns>
  public static object GetSingle(string SQLString)
  {
   using (SqlConnection connection = new
SqlConnection(connectionString))
   {
    using(SqlCommand cmd = new SqlCommand(SQLString,connection))
    {
     try
     {
      connection.Open();
      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)
     {      
      connection.Close();
      throw new Exception(e.Message);
     } 
    }
   }
  }
  /// <summary>
  /// 实践查询语句,重临SqlDataReader
  /// </summary>
  /// <param name=”strSQL”>查询语句</param>
  /// <returns>SqlDataReader</returns>
  public static SqlDataReader ExecuteReader(string strSQL)
  {
   SqlConnection connection = new SqlConnection(connectionString);   
   SqlCommand cmd = new SqlCommand(strSQL,connection);    
   try
   {
    connection.Open(); 
    SqlDataReader myReader = cmd.ExecuteReader();
    return myReader;
   }
   catch(System.Data.SqlClient.SqlException e)
   {        
    throw new Exception(e.Message);
   }   
   
  }  
  /// <summary>
  /// 实施查询语句,再次来到DataSet
  /// </summary>
  /// <param name=”SQLString”>查询语句</param>
  /// <returns>DataSet</returns>
  public static DataSet Query(string SQLString)
  {
   using (SqlConnection connection = new
SqlConnection(connectionString))
   {
    DataSet ds = new DataSet();
    try
    {
     connection.Open();
     SqlDataAdapter command = new
SqlDataAdapter(SQLString,connection);    
     command.Fill(ds,”ds”);
    }
    catch(System.Data.SqlClient.SqlException ex)
    {    
     throw new Exception(ex.Message);
    }   
    return ds;
   }   
  }

namespace SQLServerDAL.Keleyi.Com
{
/// <summary>
/// Copyright (C) 2009-2013 keleyi.com
/// 柯乐义SQLServer访问类
///
/// </summary>
public abstract class KeleyiSQLHelper
{
/// <summary>
/// 修改连接串适应现实条件
/// </summary>
protected static string connectionString = “Data Source=PC-keleyi-com
;Database=db_keleyi_com;User ID=sa;PWD=123”;
public KeleyiSQLHelper()
{
}
#region 公用方法
public static int GetMaxID(string FieldName, string TableName)
{
string strsql = “select max(” + FieldName + “)+1 from ” + TableName;
object obj = GetSingle(strsql);
if (obj == null)
{
return 1;
}
else
{
return int.Parse(obj.ToString());
}
}
public static bool Exists(string strSql, params SqlParameter[]
cmdParms)
{
object obj = GetSingle(strSql, cmdParms);
int cmdresult;
if ((Object.Equals(obj, null)) || (Object.Equals(obj,
System.DBNull.Value)))
{
cmdresult = 0;
}
else
{
cmdresult = int.Parse(obj.ToString());
}
if (cmdresult == 0)
{
return false;
}
else
{
return true;
}
}
#endregion
#region 实施轻便SQL语句
/// <summary>
/// 实践SQL语句,再次回到影响的记录数
/// </summary>
/// <param name=”SQLString”>SQL语句</param>
/// <returns>影响的笔录数</returns>
public static int ExecuteSql(string SQLString)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand(SQLString, connection))
{
try
{
connection.Open();
int rows = cmd.ExecuteNonQuery();
return rows;
}
catch (System.Data.SqlClient.SqlException E)
{
connection.Close();
throw new Exception(E.Message);
}
}
}
}

 提供贰个web.config中的连接串或app.config

二.因为关乎到多表多库的操作,涉及到数据库品级的东西,以及逻辑业务上的东西,因而,要从最基元的片段就尽量屏蔽难点,进行严酷的东西管理。

  #endregion

/// <summary>
/// 实践多条SQL语句,达成数据库事务。
/// </summary>
/// <param name=”SQLStringList”>多条SQL语句</param>
public static void ExecuteSqlTran(ArrayList SQLStringList)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
SqlTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
for (int n = 0; n < SQLStringList.Count; n++)
{
string strsql = SQLStringList[n].ToString();
if (strsql.Trim().Length > 1)
{
cmd.CommandText = strsql;
cmd.ExecuteNonQuery();
}
}
tx.Commit();
}
catch (System.Data.SqlClient.SqlException E)
{
tx.Rollback();
throw new Exception(E.Message);
}
}
}
/// <summary>
/// 执行带二个存款和储蓄进程参数的的SQL语句。
/// </summary>
/// <param name=”SQLString”>SQL语句</param>
/// <param
name=”content”>参数内容,举例贰个字段是格式复杂的作品,有特殊符号,能够通过这几个法子增添</param>
/// <returns>影响的记录数</returns>
public static int ExecuteSql(string SQLString, string content)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand(SQLString, connection);
System.Data.SqlClient.SqlParameter myParameter = new
System.Data.SqlClient.SqlParameter(“@content”, SqlDbType.NText);
myParameter.Value = content;
cmd.Parameters.Add(myParameter);
try
{
connection.Open();
int rows = cmd.ExecuteNonQuery();
return rows;
}
catch (System.Data.SqlClient.SqlException E)
{
throw new Exception(E.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
/// <summary>
/// 向数据Curry插入图像格式的字段(和上边情状类似的另壹种实例)
/// </summary>
/// <param name=”strSQL”>SQL语句</param>
/// <param
name=”fs”>图像字节,数据库的字段类型为image的状态</param>
/// <returns>影响的笔录数</returns>
public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand(strSQL, connection);
System.Data.SqlClient.SqlParameter myParameter = new
System.Data.SqlClient.SqlParameter(“@fs”, SqlDbType.Image);
myParameter.Value = fs;
cmd.Parameters.Add(myParameter);
try
{
connection.Open();
int rows = cmd.ExecuteNonQuery();
return rows;
}
catch (System.Data.SqlClient.SqlException E)
{
throw new Exception(E.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}

<connectionStrings>
<!–Oracle 连接串–>
<add name=”oracleconn” connectionString=”Data
Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=ORCL)));User
ID=scott;Password=tiger;Unicode=True”
providerName=”System.Data.OracleClient”/>
<!–sqlserver 连接串–>
<add name=”ConstrSQL”
connectionString=”server=.\MSSQLSEEvoqueVEHaval一;database=数据库名;uid=sa;pwd=*****;Integrated
Security=False;Persist Security Info=False;”
providerName=”System.Data.Sqlclient”/>
</connectionStrings>

办法如下:

  #region 试行带参数的SQL语句

/// <summary>
/// 试行一条计算查询结果语句,重返查询结果(object)。
///
/// </summary>
/// <param name=”SQLString”>计算查询结果语句</param>
/// <returns>查询结果(object)</returns>
public static object GetSingle(string SQLString)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand(SQLString, connection))
{
try
{
connection.Open();
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)
{
connection.Close();
throw new Exception(e.Message);
}
}
}
}
/// <summary>
/// 实行查询语句,重回SqlDataReader
/// </summary>
/// <param name=”strSQL”>查询语句</param>
/// <returns>SqlDataReader</returns>
public static SqlDataReader ExecuteReader(string strSQL)
{
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(strSQL, connection);
try
{
connection.Open();
SqlDataReader myReader = cmd.ExecuteReader();
return myReader;
}
catch (System.Data.SqlClient.SqlException e)
{
throw new Exception(e.Message);
}

布署连接串要放开<configSections>前面,至于这里您本人定

民用认为使用率较高也较为便利的是   ExecuteNonQuery
(增、删、改),ExecuteDataTable(协理存款和储蓄进程和SQL语句询问,重临tadatable)

  /// <summary>
  /// 施行SQL语句,重回影响的记录数
  /// </summary>
  /// <param name=”SQLString”>SQL语句</param>
  /// <returns>影响的记录数</returns>
  public static int ExecuteSql(string SQLString,params SqlParameter[]
cmdParms)
  {
   using (SqlConnection connection = new
SqlConnection(connectionString))
   {    
    using (SqlCommand cmd = new SqlCommand())
    {
     try
     {  
      PrepareCommand(cmd, connection, null,SQLString, cmdParms);
      int rows=cmd.ExecuteNonQuery();
      cmd.Parameters.Clear();
      return rows;
     }
     catch(System.Data.SqlClient.SqlException E)
     {    
      throw new Exception(E.Message);
     }
    }    
   }
  }
  
   
  /// <summary>
  /// 实行多条SQL语句,落成数据库事务。
  /// </summary>
  /// <param
name=”SQLStringList”>SQL语句的哈希表(key为sql语句,value是该语句的SqlParameter[])</param>
  public static void ExecuteSqlTran(Hashtable SQLStringList)
  {   
   using (SqlConnection conn = new SqlConnection(connectionString))
   {
    conn.Open();
    using (SqlTransaction trans = conn.BeginTransaction())
    {
     SqlCommand cmd = new SqlCommand();
     try
     {
      //循环
      foreach (DictionaryEntry myDE in SQLStringList)
      { 
       string  cmdText=myDE.Key.ToString();
       SqlParameter[] cmdParms=(SqlParameter[])myDE.Value;
       PrepareCommand(cmd,conn,trans,cmdText, cmdParms);
       int val = cmd.ExecuteNonQuery();
       cmd.Parameters.Clear();

}
/// <summary>
/// 实行查询语句,再次来到DataSet
///
/// </summary>
/// <param name=”SQLString”>查询语句</param>
/// <returns>DataSet</returns>
public static DataSet Query(string SQLString)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
DataSet ds = new DataSet();
try
{
connection.Open();
SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
command.Fill(ds, “ds”);
}
catch (System.Data.SqlClient.SqlException ex)
{
throw new Exception(ex.Message);
}
return ds;
}
}

#region 其余二种连接情势
//static string conner = @”Data Source=.\MSSQLSE福特ExplorerVE中华V1;Initial
Catalog=数据库名字;Persist Security Info=True;User ID=sa;pwd=***”;
//static string conner =
ConfigurationSettings.AppSettings[“ConstrSQL”].ToString();
//ConfigurationSettings配置节点读取
#endregion
static string conner =
ConfigurationManager.ConnectionStrings[“ConstrSQL”].ConnectionString;
//ConnectionStrings配置文件读取
private static SqlConnection con = new SqlConnection(conner);

里面ExecuteNonQuery中的 iCount 参数正是用来举办事物管理的

       trans.Commit();
      }     
     }
     catch
     {
      trans.Rollback();
      throw;
     }
    }    
   }
  }
 
    
  /// <summary>
  /// 实践一条总括查询结果语句,重回查询结果(object)。
  /// </summary>
  /// <param name=”SQLString”>总结查询结果语句</param>
  /// <returns>查询结果(object)</returns>
  public static object GetSingle(string SQLString,params
SqlParameter[] cmdParms)
  {
   using (SqlConnection connection = new
SqlConnection(connectionString))
   {
    using (SqlCommand cmd = new SqlCommand())
    {
     try
     {
      PrepareCommand(cmd, connection, null,SQLString, cmdParms);
      object obj = cmd.ExecuteScalar();
      cmd.Parameters.Clear();
      if((Object.Equals(obj,null))||(Object.Equals(obj,System.DBNull.Value)))
      {     
       return null;
      }
      else
      {
       return obj;
      }    
     }
     catch(System.Data.SqlClient.SqlException e)
     {    
      throw new Exception(e.Message);
     }     
    }
   }
  }
  
  /// <summary>
  /// 试行查询语句,再次来到SqlDataReader
  /// </summary>
  /// <param name=”strSQL”>查询语句</param>
  /// <returns>SqlDataReader</returns>
  public static SqlDataReader ExecuteReader(string SQLString,params
SqlParameter[] cmdParms)
  {  
   SqlConnection connection = new SqlConnection(connectionString);
   SqlCommand cmd = new SqlCommand();    
   try
   {
    PrepareCommand(cmd, connection, null,SQLString, cmdParms);
    SqlDataReader myReader = cmd.ExecuteReader();
    cmd.Parameters.Clear();
    return myReader;
   }
   catch(System.Data.SqlClient.SqlException e)
   {        
    throw new Exception(e.Message);
   }     
   
  }  
  
  /// <summary>
  /// 实施查询语句,再次来到DataSet
  /// </summary>
  /// <param name=”SQLString”>查询语句</param>
  /// <returns>DataSet</returns>
  public static DataSet Query(string SQLString,params SqlParameter[]
cmdParms)
  {
   using (SqlConnection connection = new
SqlConnection(connectionString))
   {
    SqlCommand cmd = new SqlCommand();
    PrepareCommand(cmd, connection, null,SQLString, cmdParms);
    using( SqlDataAdapter da = new SqlDataAdapter(cmd) )
    {
     DataSet ds = new DataSet(); 
     try
     {            
      da.Fill(ds,”ds”);
      cmd.Parameters.Clear();
     }
     catch(System.Data.SqlClient.SqlException ex)
     {    
      throw new Exception(ex.Message);
     }   
     return ds;
    }    
   }   
  }

#endregion
#region 实施带参数的SQL语句
/// <summary>
/// 试行SQL语句,重返影响的记录数
///
/// </summary>
/// <param name=”SQLString”>SQL语句</param>
/// <returns>影响的记录数</returns>
public static int ExecuteSql(string SQLString, params SqlParameter[]
cmdParms)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand())
{
try
{
PrepareCommand(cmd, connection, null, SQLString, cmdParms);
int rows = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return rows;
}
catch (System.Data.SqlClient.SqlException E)
{
throw new Exception(E.Message);
}
}
}
4858.com,}

#region 旧的
/// <summary>
/// 实行增加和删除改操作
/// </summary>
/// <param name=”sql”></param>
/// <returns></returns>
public static int ExexuteCommand(string sql)
{
try
{
con.Open();
SqlCommand cmd = new SqlCommand(sql, con);
return cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
}

/// <summary>
/// 对数据库执行增、删、改命令
/// </summary>
/// <param name=”sql”>T-Sql语句</param>
/// <param name=”pa”>参数数组</param>
/// <param name=”iCount”>成功景观下影响行数</param>
/// <returns>受影响的笔录数</returns>
public static int ExecuteNonQuery(string connectionString, string sql,
SqlParameter[] pa, int iCount)
{
using (SqlConnection Connection = new SqlConnection(connectionString))
{
Connection.Open();
SqlTransaction trans = Connection.BeginTransaction();
try
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Transaction = trans;
if (pa != null)
{
cmd.Parameters.AddRange(pa);
}

  private static void PrepareCommand(SqlCommand cmd,SqlConnection
conn,SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
  {
   if (conn.State != ConnectionState.Open)
    conn.Open();
   cmd.Connection = conn;
   cmd.CommandText = cmdText;
   if (trans != null)
    cmd.Transaction = trans;
   cmd.CommandType = CommandType.Text;//cmdType;
   if (cmdParms != null)
   {
    foreach (SqlParameter parm in cmdParms)
     cmd.Parameters.Add(parm);
   }
  }

/// <summary>
/// 实行多条SQL语句,达成数据库事务。
/// </summary>
/// <param
name=”SQLStringList”>SQL语句的哈希表(key为sql语句,value是该语句的SqlParameter[])</param>
public static void ExecuteSqlTran(Hashtable SQLStringList)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
using (SqlTransaction trans = conn.BeginTransaction())
{
SqlCommand cmd = new SqlCommand();
try
{
//循环
foreach (DictionaryEntry myDE in SQLStringList)
{
string cmdText = myDE.Key.ToString();
SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;
PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
trans.Commit();
}
}
catch
{
trans.Rollback();
throw;
}
}
}
}

/// <summary>
/// 实施增加和删除改操作
/// </summary>
/// <param name=”sql”></param>
/// <returns></returns>
public static int ExexuteCommand(string sql, params SqlParameter[]
para)
{
try
{
con.Open();
SqlCommand cmd = new SqlCommand(sql, con);
//将参数加多到参数集结中
cmd.Parameters.AddRange(para);
return cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
}

if (Connection.State != ConnectionState.Open)
{
Connection.Open();
}
int result = cmd.ExecuteNonQuery();
if (iCount != 0 && iCount != result)
{
trans.Rollback();
return 0;
}

  #endregion

/// <summary>
/// 实行一条总计查询结果语句,再次来到查询结果(object)。
/// </summary>
/// <param name=”SQLString”>总计查询结果语句</param>
/// <returns>查询结果(object)</returns>
public static object GetSingle(string SQLString, params SqlParameter[]
cmdParms)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand())
{
try
{
PrepareCommand(cmd, connection, null, SQLString, cmdParms);
object obj = cmd.ExecuteScalar();
cmd.Parameters.Clear();
if ((Object.Equals(obj, null)) || (Object.Equals(obj,
System.DBNull.Value)))
{
return null;
}
else
{
return obj;
}
}
catch (System.Data.SqlClient.SqlException e)
{
throw new Exception(e.Message);
}
}
}
}

/// <summary>
/// 再次来到dataReader的询问艺术
/// </summary>
/// <param name=”sql”></param>
/// <returns></returns>
public static SqlDataReader GetReader(string sql)
{
try
{
con.Open();
SqlCommand cmd = new SqlCommand(sql, con);
SqlDataReader reader =
cmd.ExecuteReader(CommandBehavior.CloseConnection);
return reader;
}
catch (Exception ex)
{
throw ex;
}
}

trans.Commit();
return result;
}
catch (Exception ex)
{
trans.Rollback();
return 0;
}
}
}

  #region 存款和储蓄进程操作

/// <summary>
/// 推行查询语句,重回SqlDataReader
///
/// </summary>
/// <param name=”strSQL”>查询语句</param>
/// <returns>SqlDataReader</returns>
public static SqlDataReader ExecuteReader(string SQLString, params
SqlParameter[] cmdParms)
{
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand();
try
{
PrepareCommand(cmd, connection, null, SQLString, cmdParms);
SqlDataReader myReader = cmd.ExecuteReader();
cmd.Parameters.Clear();
return myReader;
}
catch (System.Data.SqlClient.SqlException e)
{
throw new Exception(e.Message);
}

/// <summary>
/// 再次回到data里德r的询问办法
/// </summary>
/// <param name=”sql”></param>
/// <returns></returns>
public static SqlDataReader GetReader(string sql, params
SqlParameter[] para)
{
try
{
//if (con.State == ConnectionState.Closed)
//{
con.Open();
//}
SqlCommand cmd = new SqlCommand(sql, con);
cmd.Parameters.AddRange(para);
SqlDataReader reader =
cmd.ExecuteReader(CommandBehavior.CloseConnection);
return reader;
}
catch (Exception ex)
{
throw ex;
}
}

/// <summary>
/// 实施钦定数据库连接对象的授命,钦赐期存款款和储蓄进度参数,重回DataTable
/// </summary>
/// <param name=”connectionString”>连接字符串</param>
/// <param
name=”type”>命令类型(T-Sql语句可能存储进程)</param>
/// <param name=”sql”>T-Sql语句只怕存款和储蓄进程的称谓</param>
/// <param name=”pa”>参数数组</param>
/// <returns>结果集DataTable</returns>
public static DataTable ExecuteDataTable(string connectionString,
CommandType type, string sql, params SqlParameter[] pa)
{
using (SqlConnection Connection = new SqlConnection(connectionString))
{
try
{
if (Connection.State != ConnectionState.Open)
Connection.Open();
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.CommandType = type;
if (pa != null)
cmd.Parameters.AddRange(pa);

  /// <summary>
  /// 施行存储进程
  /// </summary>
  /// <param name=”storedProcName”>存款和储蓄进度名</param>
  /// <param name=”parameters”>存储进度参数</param>
  /// <returns>SqlDataReader</returns>
  public static SqlDataReader RunProcedure(string storedProcName,
IDataParameter[] parameters )
  {
   SqlConnection connection = new SqlConnection(connectionString);
   SqlDataReader returnReader;
   connection.Open();
   SqlCommand command = BuildQueryCommand( connection,storedProcName,
parameters );
   command.CommandType = CommandType.StoredProcedure;
   returnReader = command.ExecuteReader();    
   return returnReader;   
  }
  
  
  /// <summary>
  /// 推行存款和储蓄进度
  /// </summary>
  /// <param name=”storedProcName”>存款和储蓄进程名</param>
  /// <param name=”parameters”>存款和储蓄进程参数</param>
  /// <param name=”tableName”>DataSet结果中的表名</param>
  /// <returns>DataSet</returns>
  public static DataSet RunProcedure(string storedProcName,
IDataParameter[] parameters, string tableName )
  {
   using (SqlConnection connection = new
SqlConnection(connectionString))
   {
    DataSet dataSet = new DataSet();
    connection.Open();
    SqlDataAdapter sqlDA = new SqlDataAdapter();
    sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName,
parameters );
    sqlDA.Fill( dataSet, tableName );
    connection.Close();
    return dataSet;
   }
  }

}

/// <summary>
/// 重临dataTable的查询格局
/// </summary>
/// <param name=”sql”></param>
/// <returns></returns>
public static DataTable GetDateTable(string sql)
{
try
{
con.Open();
SqlDataAdapter myAdapter = new SqlDataAdapter(sql, con);
DataSet ds = new DataSet();
myAdapter.Fill(ds);
return ds.Tables[0];
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
}

SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
if (ds.Tables != null && ds.Tables.Count > 0)
{
return ds.Tables[0];
}
}
catch (Exception ex)
{
return null;
}
return null;
}
}

  
  /// <summary>
  /// 营造 SqlCommand 对象(用来回到3个结出集,而不是2个整数值)
  /// </summary>
  /// <param name=”connection”>数据库连接</param>
  /// <param name=”storedProcName”>存款和储蓄进程名</param>
  /// <param name=”parameters”>存款和储蓄进度参数</param>
  /// <returns>SqlCommand</returns>
  private static SqlCommand BuildQueryCommand(SqlConnection
connection,string storedProcName, IDataParameter[] parameters)
  {   
   SqlCommand command = new SqlCommand( storedProcName, connection );
   command.CommandType = CommandType.StoredProcedure;
   foreach (SqlParameter parameter in parameters)
   {
    command.Parameters.Add( parameter );
   }
   return command;   
  }
  
  /// <summary>
  /// 实施存款和储蓄进度,再次回到影响的行数  
  /// </summary>
  /// <param name=”storedProcName”>存款和储蓄进度名</param>
  /// <param name=”parameters”>存款和储蓄进度参数</param>
  /// <param name=”rowsAffected”>影响的行数</param>
  /// <returns></returns>
  public static int RunProcedure(string storedProcName,
IDataParameter[] parameters, out int rowsAffected )
  {
   using (SqlConnection connection = new
SqlConnection(connectionString))
   {
    int result;
    connection.Open();
    SqlCommand command = BuildIntCommand(connection,storedProcName,
parameters );
    rowsAffected = command.ExecuteNonQuery();
    result = (int)command.Parameters[“ReturnValue”].Value;
    //Connection.Close();
    return result;
   }
  }
  
  /// <summary>
  /// 成立 SqlCommand 对象实例(用来回到三个整数值) 
  /// </summary>
  /// <param name=”storedProcName”>存款和储蓄进程名</param>
  /// <param name=”parameters”>存款和储蓄进程参数</param>
  /// <returns>SqlCommand 对象实例</returns>
  private static SqlCommand BuildIntCommand(SqlConnection
connection,string storedProcName, IDataParameter[] parameters)
  {
   SqlCommand command = BuildQueryCommand(connection,storedProcName,
parameters );
   command.Parameters.Add( new SqlParameter ( “ReturnValue”,
    SqlDbType.Int,4,ParameterDirection.ReturnValue,
    false,0,0,string.Empty,DataRowVersion.Default,null ));
   return command;
  }
  #endregion 

/// <summary>
/// 实践查询语句,重临DataSet
/// </summary>
/// <param name=”SQLString”>查询语句</param>
/// <returns>DataSet</returns>
public static DataSet Query(string SQLString, params SqlParameter[]
cmdParms)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, connection, null, SQLString, cmdParms);
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
DataSet ds = new DataSet();
try
{
da.Fill(ds, “ds”);
cmd.Parameters.Clear();
}
catch (System.Data.SqlClient.SqlException ex)
{
throw new Exception(ex.Message);
}
return ds;
}
}
}

/// <summary>
/// 重临dataTable的询问办法
/// </summary>
/// <param name=”sql”></param>
/// <returns></returns>
public static DataTable GetDateTable(string sql, params SqlParameter[]
para)
{
try
{
con.Open();
//SqlDataAdapter myAdapter = new SqlDataAdapter(sql, con);
SqlCommand cmd = new SqlCommand(sql, con);
cmd.Parameters.AddRange(para);
SqlDataAdapter myAdapter = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
myAdapter.Fill(ds);
return ds.Tables[0];
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
}

/// <summary>
/// 查询结果聚集第二行第二列的值
/// </summary>
/// <param name=”connectionString”>连接字符串</param>
/// <param name=”sql”>T-Sql语句</param>
/// <param name=”pa”>参数数组</param>
/// <returns>第贰行第叁列的值</returns>
public static int ExecuteScalar(string connectionString, string sql,
SqlParameter[] pa)
{
using (SqlConnection Connection = new SqlConnection(connectionString))
{
if (Connection.State != ConnectionState.Open)
Connection.Open();
SqlCommand cmd = new SqlCommand(sql, Connection);
if (pa != null)
cmd.Parameters.AddRange(pa);
int result = Convert.ToInt32(cmd.ExecuteScalar());
return result;
}
}

 }
}

private static void PrepareCommand(SqlCommand cmd, SqlConnection conn,
SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
{
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (trans != null)
cmd.Transaction = trans;
cmd.CommandType = CommandType.Text;//cmdType;
if (cmdParms != null)
{
foreach (SqlParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
}
#endregion
#region 柯乐义存款和储蓄进度操作
/// <summary>
/// 推行存款和储蓄进度
/// </summary>
/// <param name=”storedProcName”>存款和储蓄进程名</param>
/// <param name=”parameters”>存款和储蓄进度参数</param>
/// <returns>SqlDataReader</returns>
public static SqlDataReader RunProcedure(string storedProcName,
IDataParameter[] parameters)
{
SqlConnection connection = new SqlConnection(connectionString);
SqlDataReader returnReader;
connection.Open();
SqlCommand command = BuildQueryCommand(connection, storedProcName,
parameters);
command.CommandType = CommandType.StoredProcedure;
returnReader = command.ExecuteReader();
return returnReader;
}
//

/// <summary>
/// 重临单值的询问办法
/// </summary>
/// <param name=”sql”></param>
/// <returns></returns>
public static object GetScalar(string sql)
{
try
{
con.Open();
SqlCommand cmd = new SqlCommand(sql, con);
return cmd.ExecuteScalar();
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
}

/// <summary>
/// 创制数量读取器
/// </summary>
/// <param name=”connectionString”>连接字符串</param>
/// <param name=”sql”>T-Sql语句</param>
/// <param name=”pa”>参数数组</param>
/// <param name=”Connection”>数据库连接</param>
/// <returns>数据读取器</returns>
public static SqlDataReader ExecuteReader(string connectionString,
string sql, SqlParameter[] pa, SqlConnection conn)
{
if (conn.State != ConnectionState.Open)
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
if (pa != null)
cmd.Parameters.AddRange(pa);
SqlDataReader reader = cmd.ExecuteReader();
return reader;
}

/// <summary>
/// 推行存款和储蓄进程
/// </summary>
/// <param name=”storedProcName”>存储进度名</param>
/// <param name=”parameters”>存款和储蓄进度参数</param>
/// <param name=”tableName”>DataSet结果中的表名</param>
/// <returns>DataSet</returns>
public static DataSet RunProcedure(string storedProcName,
IDataParameter[] parameters, string tableName)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
DataSet dataSet = new DataSet();
connection.Open();
SqlDataAdapter sqlDA = new SqlDataAdapter();
sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName,
parameters);
sqlDA.Fill(dataSet, tableName);
connection.Close();
return dataSet;
}
}

/// <summary>
/// 重临单值的询问方式(有参数的询问语句)
/// </summary>
/// <param name=”sql”></param>
/// <returns></returns>
public static object GetScalar(string sql, params SqlParameter[]
para)
{
try
{
con.Open();
SqlCommand cmd = new SqlCommand(sql, con);
cmd.Parameters.AddRange(para);
return cmd.ExecuteScalar();
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
}

 

/// <summary>
/// 构建 SqlCommand 对象(用来回到3个结出集,而不是3个整数值)
/// </summary>
/// <param name=”connection”>数据库连接</param>
/// <param name=”storedProcName”>存款和储蓄进度名</param>
/// <param name=”parameters”>存款和储蓄进度参数</param>
/// <returns>SqlCommand</returns>
private static SqlCommand BuildQueryCommand(SqlConnection connection,
string storedProcName, IDataParameter[] parameters)
{
SqlCommand command = new SqlCommand(storedProcName, connection);
command.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
return command;
}

#region 存储进程调用方法
/// <summary>
/// 调用施行增加和删除改的有参数的蕴藏进度
/// </summary>
/// <param name=”name”></param>
/// <param name=”values”></param>
/// <returns></returns>
public static int ExectueCommandStoredProcedure(string name, params
SqlParameter[] values)
{
//SqlConnection conn = new SqlConnection(connection);

/// <summary>
/// 试行存款和储蓄进度,重返影响的行数
/// </summary>
/// <param name=”storedProcName”>存款和储蓄进程名</param>
/// <param name=”parameters”>存款和储蓄进程参数</param>
/// <param name=”rowsAffected”>影响的行数</param>
/// <returns></returns>
public static int RunProcedure(string storedProcName, IDataParameter[]
parameters, out int rowsAffected)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
int result;
connection.Open();
SqlCommand command = BuildIntCommand(connection, storedProcName,
parameters);
rowsAffected = command.ExecuteNonQuery();
result = (int)command.Parameters[“ReturnValue”].Value;
//Connection.Close();
return result;
}
}

try
{
//connection.Open();
SqlCommand comm = new SqlCommand();
comm.Connection = con;
comm.CommandType = CommandType.StoredProcedure;
comm.CommandText = name;
comm.Parameters.AddRange(values);
return comm.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
}
/// <summary>
/// 调用无参的蕴藏进度的措施
/// </summary>
/// <param name=”name”></param>
/// <returns></returns>
public static int ExectueCommandStoredProcedure(string name)
{

/// <summary>
/// 成立 SqlCommand 对象实例(用来回到3个整数值)
/// </summary>
/// <param name=”storedProcName”>存款和储蓄进程名</param>
/// <param name=”parameters”>存款和储蓄进程参数</param>
/// <returns>SqlCommand 对象实例</returns>
private static SqlCommand BuildIntCommand(SqlConnection connection,
string storedProcName, IDataParameter[] parameters)
{
SqlCommand command = BuildQueryCommand(connection, storedProcName,
parameters);
command.Parameters.Add(new SqlParameter(“ReturnValue”,
SqlDbType.Int, 4, ParameterDirection.ReturnValue,
false, 0, 0, string.Empty, DataRowVersion.Default, null));
return command;
}
#endregion
}
}

try
{
//connection.Open();
SqlCommand comm = new SqlCommand();
comm.Connection = con;
comm.CommandType = CommandType.StoredProcedure;
comm.CommandText = name;
//comm.Parameters.AddRange(values);
return comm.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
}
/// <summary>
/// 重返DataTable型的蕴藏进度的调用方法
/// </summary>
/// <param name=”name”></param>
/// <returns></returns>
public static DataTable GetTableByStoredProcedure(string name)
{

正文转发自柯乐义

//SqlConnection conn = new SqlConnection(connection.ConnectionString);
try
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
DataSet ds = new DataSet();
SqlCommand comm = new SqlCommand();
comm.Connection = con;
comm.CommandType = CommandType.StoredProcedure;
comm.CommandText = name;
SqlDataAdapter da = new SqlDataAdapter(comm);
ds.Clear();
da.Fill(ds);
return ds.Tables[0];
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
}
/// <summary>
/// 再次回到DataTable型的囤积进度的调用方法(含参)
/// </summary>
/// <param name=”name”></param>
/// <returns></returns>
public static DataTable GetTableByStoredProcedure(string name, params
SqlParameter[] valuse)
{
//SqlConnection conn = new SqlConnection(connection.ConnectionString);
try
{
if (con.State == ConnectionState.Closed)
{
con.Open();

}
//conn.Open();
DataSet ds = new DataSet();
SqlCommand comm = new SqlCommand();
comm.Connection = con;
comm.CommandType = CommandType.StoredProcedure;
comm.CommandText = name;
comm.Parameters.AddRange(valuse);
SqlDataAdapter da = new SqlDataAdapter(comm);
da.Fill(ds);
return ds.Tables[0];
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
}
/// <summary>
/// 再次回到reader型的无参的调用存款和储蓄进程的章程
/// </summary>
/// <param name=”name”></param>
/// <returns></returns>
public static SqlDataReader GetReaderByStoredProcedure(string name)
{
try
{

SqlCommand comm = new SqlCommand();
comm.Connection = con;
comm.CommandType = CommandType.StoredProcedure;
comm.CommandText = name;

SqlDataReader reader =
comm.ExecuteReader(CommandBehavior.CloseConnection);
return reader;
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 重临reader型的(含参)的调用存储过程的不二等秘书技
/// </summary>
/// <param name=”name”></param>
/// <param name=”values”></param>
/// <returns></returns>
public static SqlDataReader GetReaderByStoredProcedure(string name,
params SqlParameter[] values)
{
try
{
con.Open();
SqlCommand comm = new SqlCommand();
comm.Connection = con;
comm.CommandType = CommandType.StoredProcedure;
comm.CommandText = name;
comm.Parameters.AddRange(values);

SqlDataReader reader =
comm.ExecuteReader(CommandBehavior.CloseConnection);
return reader;
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 重回单值类型(无参)
/// </summary>
/// <param name=”name”></param>
/// <returns></returns>
public static Object GetScalarByStoredProcedure(string name)
{
try
{
con.Open();
SqlCommand comm = new SqlCommand();
comm.Connection = con;
comm.CommandType = CommandType.StoredProcedure;
comm.CommandText = name;

return comm.ExecuteScalar();
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
}
/// <summary>
/// 再次回到单值类型(含参)
/// </summary>
/// <param name=”name”></param>
/// <returns></returns>
public static Object GetScalarByStoredProcedure(string name, params
SqlParameter[] values)
{
try
{
con.Open();
SqlCommand comm = new SqlCommand();
comm.Connection = con;
comm.CommandType = CommandType.StoredProcedure;
comm.CommandText = name;
comm.Parameters.AddRange(values);

return comm.ExecuteScalar();
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
}
#endregion
#endregion

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图
Copyright @ 2010-2019 美高梅手机版4858 版权所有