[代码]某人封装的一个ADO.net操作类

using System;
using System.Collections;
using System.Collections.Specialized;
using System.Runtime.Remoting.Messaging;
using System.Data;
using System.Data.SQLClient;
using System.Configuration;
namespace LTP.SQLServerDAL
{
///

public abstract class DbManagerSQL
{
//数据库连接字符串
protected static string connectionString = ConfigurationSettings.AppSettings[“ConnectionString”];
public DbManagerSQL()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
///

/// ///
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)
{
throw new Exception(E.Message);
}
}
}
}
///

/// /// public static void ExecuteSqlTran(string SQLString1,string SQLString2)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection=connection;
SqlTransaction tx=connection.BeginTransaction();
cmd.Transaction=tx;
try
{
cmd.CommandText=SQLString1;
cmd.ExecuteNonQuery();
cmd.CommandText=SQLString2;
cmd.ExecuteNonQuery();
tx.Commit();
}
catch(System.Data.SqlClient.SqlException E)
{
tx.Rollback();
throw new Exception(E.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
///

/// public static void ExecuteSqlTran(string SQLStringList)
{
using (OdbcConnection conn = new OdbcConnection(connectionString))
{
conn.Open();
OdbcCommand cmd = new OdbcCommand();
cmd.Connection=conn;
OdbcTransaction tx=conn.BeginTransaction();
cmd.Transaction=tx;
try
{
string [] split= SQLStringList.Split(new Char [] { ';'});
foreach (string strsql in split)
{
if (strsql.Trim()!=””)
{
cmd.CommandText=strsql;
cmd.ExecuteNonQuery();
}
}
tx.Commit();
}
catch(System.Data.Odbc.OdbcException E)
{
tx.Rollback();
throw new Exception(E.Message);
}
}
}
///

/// /// ///
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();
}
}
}
///

/// /// ///
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();
}
}
}
///

/// ///
public static int GetCount(string strSQL)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand(strSQL,connection);
try
{
connection.Open();
SqlDataReader result = cmd.ExecuteReader();
int i=0;
while(result.Read())
{
i=result.GetInt32(0);
}
result.Close();
return i;
}
catch(System.Data.SqlClient.SqlException e)
{
throw new Exception(e.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
///

/// ///
public static object GetSingle(string SQLString)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
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)
{
throw new Exception(e.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
///

/// ///
public static SqlDataReader ExecuteReader(string strSQL)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand(strSQL,connection);
SqlDataReader myReader;
try
{
connection.Open();
myReader = cmd.ExecuteReader();
return myReader;
}
catch(System.Data.SqlClient.SqlException e)
{
throw new Exception(e.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
///

/// ///
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 存储过程操作
///

/// /// ///
public static SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters )
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlDataReader returnReader;
connection.Open();
SqlCommand command = BuildQueryCommand( connection,storedProcName, parameters );
command.CommandType = CommandType.StoredProcedure;
returnReader = command.ExecuteReader();
//Connection.Close();
return returnReader;
}
}
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;
}
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;
}
}
#endregion
}
}