using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Collections;
using System.Data.SqlClient;
public class SqlHelper
{
#region 配置连接
private static SqlConnection connection;
static SqlConnection Connection
{
get
{
string connectionString = ConfigurationManager.ConnectionStrings["数据库连接名"].ConnectionString;
if (connection == null)
{
connection = new SqlConnection(connectionString);
connection.Open();
}
else if (connection.State == System.Data.ConnectionState.Closed)
{
connection.Open();
}
else if (connection.State == System.Data.ConnectionState.Broken)
{
connection.Close();
connection.Open();
}
return connection;
}
}
#endregion
#region 设置SqlCommand
public static SqlCommand GetStoredProcCommond(string storedProcedure)
{
SqlCommand cmd = Connection.CreateCommand();
cmd.CommandText = storedProcedure;
cmd.CommandType = CommandType.StoredProcedure;
return cmd;
}
public static SqlCommand GetSqlStringCommond(string sqlQuery)
{
SqlCommand cmd = Connection.CreateCommand();
cmd.CommandText = sqlQuery;
cmd.CommandType = CommandType.Text;
return cmd;
}
#endregion
#region 增加参数
public static SqlCommand AddParameterCollection(SqlCommand cmd, SqlParameterCollection SqlParameterCollection)
{
foreach (SqlParameter SqlParameter in SqlParameterCollection)
{
cmd.Parameters.Add(SqlParameter);
}
return cmd;
}
public static void AddOutParameter(SqlCommand cmd, string parameterName, DbType dbType, int size)
{
SqlParameter SqlParameter = cmd.CreateParameter();
SqlParameter.DbType = dbType;
SqlParameter.ParameterName = parameterName;
SqlParameter.Size = size;
SqlParameter.Direction = ParameterDirection.Output;
cmd.Parameters.Add(SqlParameter);
}
public static void AddInParameter(SqlCommand cmd, string parameterName, DbType dbType, object value)
{
SqlParameter SqlParameter = cmd.CreateParameter();
SqlParameter.DbType = dbType;
SqlParameter.ParameterName = parameterName;
SqlParameter.Value = value;
SqlParameter.Direction = ParameterDirection.Input;
cmd.Parameters.Add(SqlParameter);
}
public static void AddReturnParameter(SqlCommand cmd, string parameterName, DbType dbType)
{
SqlParameter SqlParameter = cmd.CreateParameter();
SqlParameter.DbType = dbType;
SqlParameter.ParameterName = parameterName;
SqlParameter.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(SqlParameter);
}
public static SqlParameter GetParameter(SqlCommand cmd, string parameterName)
{
return cmd.Parameters[parameterName];
}
#endregion
#region 执行
public static DataSet ExecuteDataSet(SqlCommand cmd)
{
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
DataSet ds = new DataSet();
da.Fill(ds);
cmd.Dispose();
return ds;
}
public static DataTable ExecuteDataTable(SqlCommand cmd)
{
SqlDataAdapter da= new SqlDataAdapter();
da.SelectCommand = cmd;
DataTable dataTable = new DataTable();
da.Fill (dataTable);
cmd.Dispose();
da.Dispose();
return dataTable;
}
public static SqlDataReader ExecuteReader(SqlCommand cmd)
{
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Dispose();
return reader;
}
public static int ExecuteNonQuery(SqlCommand cmd)
{
int ret = cmd.ExecuteNonQuery();
cmd.Connection.Close();
cmd.Dispose();
return ret;
}
public static object ExecuteScalar(SqlCommand cmd)
{
object ret = cmd.ExecuteScalar();
cmd.Connection.Close();
cmd.Dispose();
return ret;
}
#endregion
}
SQLHelper类
最新推荐文章于 2024-05-25 18:00:05 发布