using System;
using System.Collections.Generic;
using System.Collections;
using System.Collections.Specialized;
using System.Runtime.Remoting.Messaging;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Text;
public abstract class DBUtility
{
//数据库连接字符串
protected static string connectionstring = ConfigurationSettings.AppSettings["conStr"];
public static string conStr
{
get
{
return connectionstring;
}
}
public DBUtility()
{
//
// todo: 在此处添加构造函数逻辑
//
}
/// <summary>
/// 执行sql语句,返回影响的记录数
/// </summary>
/// <param name="sqlstring"></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)
{
throw new Exception(e.Message);
}
}
}
}
/// <summary>
/// 执行两条sql语句,实现数据库事务。
/// </summary>
/// <param name="sqlstring1"></param>
/// <param name="sqlstring2"></param>
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();
}
}
}
/// <summary>
/// 执行多条sql语句,实现数据库事务,每条语句以“;”分割。
/// </summary>
/// <param name="sqlstringlist"></param>
public static void ExecuteSqlTran(string sqlstringlist)
{
using (SqlConnection conn = new SqlConnection(connectionstring))
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection=conn;
SqlTransaction tr =conn.BeginTransaction();
cmd.Transaction=tr;
try
{
string [] split= sqlstringlist.Split(new char [] {‘;‘});
foreach (string strsql in split)
{
if (strsql.Trim()!="")
{
cmd.CommandText=strsql;
cmd.ExecuteNonQuery();
}
}
tr.Commit();
}
catch(System.Data.SqlClient.SqlException e)
{
tr.Rollback();
throw new Exception(e.Message);
}
}
}
/// <summary>
/// 向数据库里插入图像格式的字段
/// </summary>
/// <param name="strsql"></param>
/// <param name="fs"></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>
/// 执行一条计算查询结果语句,返回查询结果(整数)。
/// </summary>
/// <param name="strsql"></param>
/// <returns></returns>
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();
}
}
}
/// <summary>
/// 执行一条计算查询结果语句,返回查询结果(object)。
/// </summary>
/// <param name="sqlstring"></param>
/// <returns></returns>
public static object GetSingleObj(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();
}
}
}
/// <summary>
/// 执行查询语句,返回SqlDataReader
/// </summary>
/// <param name="strsql"></param>
/// <returns></returns>
public static SqlDataReader ExecuteReader(string strsql)
{
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 SqlDataReader ExecuteReader(string strsql, IDataParameter[] parameters)
{
SqlConnection connection = new SqlConnection(connectionstring);
{
SqlCommand cmd = new SqlCommand(strsql, connection);
cmd.Parameters.AddRange(parameters);
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();
//}
}
}
/// <summary>
/// 执行查询语句,返回dataset
/// </summary>
/// <param name="sqlstring"></param>
/// <returns></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 存储过程操作
/// <summary>
/// 运行存储过程
/// </summary>
/// <param name="storedprocname"></param>
/// <param name="parameters"></param>
/// <returns></returns>
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;
}
}
public static SqlDataReader RunProcedure(string storedprocname)
{
using (SqlConnection connection = new SqlConnection(connectionstring))
{
SqlDataReader returnreader;
connection.Open();
SqlCommand command = new SqlCommand(storedprocname, connection);
command.CommandType = CommandType.StoredProcedure;
returnreader = command.ExecuteReader();
//connection.Close();
return returnreader;
}
}
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;
}
}
public static int GetProcedureReturnValue(string storedprocname, IDataParameter[] parameters)
{
using (SqlConnection connection = new SqlConnection(connectionstring))
{
connection.Open();
SqlCommand command = BuildQueryCommand(connection, storedprocname, parameters);
SqlParameter rtnval = command.Parameters.Add("rval", SqlDbType.Int);
rtnval.Direction = ParameterDirection.ReturnValue;
command.ExecuteNonQuery();
return (int)rtnval.Value;
}
}
public static string GetProcedureOutputValue(string storedprocname, IDataParameter[] parameters, string outputParaName)
{
using (SqlConnection connection = new SqlConnection(connectionstring))
{
connection.Open();
SqlCommand command = BuildQueryCommand(connection, storedprocname, parameters);
SqlParameter rtnval = command.Parameters.Add(outputParaName, SqlDbType.VarChar, 500);
rtnval.Direction = ParameterDirection.Output;
command.ExecuteNonQuery();
return rtnval.Value.ToString();
}
}
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;
}
#endregion
}
.NET:一个非常好用的SQL数据库处理类,布布扣,bubuko.com
.NET:一个非常好用的SQL数据库处理类