操作mysql的类库_DBOP——数据库操作类库 | 学步园

/*

* Created by SharpDevelop.

* User: XiaoQ

* Date: 2009-8-7

* Time: 21:37

*

* To change this template use Tools | Options | Coding | Edit Standard Headers.

*/

using System;

using System.Data;

using System.Configuration;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

using System.Data.SqlClient;

///

/// DBOp 的摘要说明

///

public class DBOP

{

public DBOP()

{

//

// TODO: 在此处添加构造函数逻辑

//

}

private static string _connectionString;

public static string ConnectionString

{

get

{

return _connectionString;

}

set

{

_connectionString = value;

}

}

public static SqlConnection GetConn()

{

ConnectionString=System.Configuration.ConfigurationManager.AppSettings["ConnectionString"];

// ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

SqlConnection SqlConn = new SqlConnection(ConnectionString);

return SqlConn;

}

public static SqlCommand GetCmd(string procName, SqlParameter[] prams)

{

SqlConnection SqlConn = GetConn();

SqlCommand SqlCmd = new SqlCommand();

SqlCmd.Connection = SqlConn;

SqlCmd.CommandType = CommandType.StoredProcedure;

SqlCmd.CommandText = procName;

if (prams != null)

{

foreach (SqlParameter parameter in prams)

{

if (parameter != null)

{

SqlCmd.Parameters.Add(parameter);

}

}

}

return SqlCmd;

}

public static SqlCommand GetCmd(string procName, SqlParameter[] prams,SqlConnection SqlConn)

{

SqlCommand SqlCmd = new SqlCommand();

SqlCmd.Connection = SqlConn;

SqlCmd.CommandType = CommandType.StoredProcedure;

SqlCmd.CommandText = procName;

if (prams != null)

{

foreach (SqlParameter parameter in prams)

{

if (parameter != null)

{

SqlCmd.Parameters.Add(parameter);

}

}

}

return SqlCmd;

}

public static SqlCommand GetCmd(string procName)

{

SqlConnection SqlConn = GetConn();

SqlCommand SqlCmd = new SqlCommand();

SqlCmd.Connection = SqlConn;

SqlCmd.CommandType = CommandType.StoredProcedure;

SqlCmd.CommandText = procName;

return SqlCmd;

}

public static SqlCommand GetCmd(string procName,SqlConnection SqlConn)

{

SqlCommand SqlCmd = new SqlCommand();

SqlCmd.Connection = SqlConn;

SqlCmd.CommandType = CommandType.StoredProcedure;

SqlCmd.CommandText = procName;

return SqlCmd;

}

public static SqlDataReader GetReader(string procName)

{

SqlConnection SqlConn = GetConn();

SqlCommand SqlCmd = GetCmd(procName,SqlConn);

SqlConn.Open();

SqlDataReader SqlDR = SqlCmd.ExecuteReader(CommandBehavior.CloseConnection);

return SqlDR;

}

public static SqlDataReader GetReader(string procName,SqlConnection SqlConn)

{

SqlCommand SqlCmd = GetCmd(procName, SqlConn);

SqlConn.Open();

SqlDataReader SqlDR = SqlCmd.ExecuteReader(CommandBehavior.CloseConnection);

return SqlDR;

}

public static SqlDataReader GetReader(string procName, SqlParameter[] prams)

{

SqlConnection SqlConn = GetConn();

SqlCommand SqlCmd = GetCmd(procName,prams,SqlConn);

SqlConn.Open();

SqlDataReader SqlDR = SqlCmd.ExecuteReader(CommandBehavior.CloseConnection);

return SqlDR;

}

public static SqlDataReader GetReader(string procName, SqlParameter[] prams,SqlConnection SqlConn)

{

SqlCommand SqlCmd = GetCmd(procName, prams, SqlConn);

SqlConn.Open();

SqlDataReader SqlDR = SqlCmd.ExecuteReader(CommandBehavior.CloseConnection);

return SqlDR;

}

public static DataTable GetTable(string procName, SqlParameter[] prams)

{

SqlConnection SqlConn = GetConn();

SqlCommand SqlCmd = GetCmd(procName, prams, SqlConn);

SqlDataAdapter SqlDA = new SqlDataAdapter();

DataSet DS = new DataSet();

SqlDA.SelectCommand = SqlCmd;

SqlDA.Fill(DS);

DataTable DT = DS.Tables[0];

SqlConn.Close();

DS.Dispose();

SqlDA.Dispose();

SqlCmd.Dispose();

SqlConn.Dispose();

return DT;

}

public static DataTable GetTable(string procName, SqlParameter[] prams,SqlConnection SqlConn)

{

SqlCommand SqlCmd = GetCmd(procName, prams, SqlConn);

SqlDataAdapter SqlDA = new SqlDataAdapter();

DataSet DS = new DataSet();

SqlDA.SelectCommand = SqlCmd;

SqlDA.Fill(DS);

DataTable DT = DS.Tables[0];

SqlConn.Close();

DS.Dispose();

SqlDA.Dispose();

SqlCmd.Dispose();

SqlConn.Dispose();

return DT;

}

public static int RunExecute(string procName)

{

SqlConnection SqlConn = GetConn();

SqlCommand SqlCmd = GetCmd(procName, SqlConn);

int intResult;

try

{

SqlConn.Open();

intResult = SqlCmd.ExecuteNonQuery();

}

catch (SqlException e)

{

return -1;

}

finally

{

SqlConn.Close();

SqlCmd.Dispose();

SqlConn.Dispose();

}

return intResult;

}

public static int RunExecute(string procName,SqlConnection SqlConn)

{

SqlCommand SqlCmd = GetCmd(procName, SqlConn);

int intResult;

try

{

SqlConn.Open();

intResult = SqlCmd.ExecuteNonQuery();

}

catch (SqlException e)

{

return -1;

}

finally

{

SqlConn.Close();

SqlCmd.Dispose();

SqlConn.Dispose();

}

return intResult;

}

public static int RunExecute(string procName,SqlParameter[] prams)

{

SqlConnection SqlConn = GetConn();

SqlCommand SqlCmd = GetCmd(procName,prams,SqlConn);

int intResult;

try

{

SqlConn.Open();

intResult = SqlCmd.ExecuteNonQuery();

}

catch (SqlException e)

{

return -1;

}

finally

{

SqlConn.Close();

SqlCmd.Dispose();

SqlConn.Dispose();

}

return intResult;

}

public static int RunExecute(string procName, SqlParameter[] prams,SqlConnection SqlConn)

{

SqlCommand SqlCmd = GetCmd(procName, prams, SqlConn);

int intResult;

try

{

SqlConn.Open();

intResult = SqlCmd.ExecuteNonQuery();

}

catch (SqlException e)

{

return -1;

}

finally

{

SqlConn.Close();

SqlCmd.Dispose();

SqlConn.Dispose();

}

return intResult;

}

public static int RunExecuteScalar(string procName)

{

SqlConnection SqlConn = GetConn();

SqlCommand SqlCmd = GetCmd(procName,SqlConn);

int intResult;

try

{

SqlConn.Open();

intResult = Convert.ToInt32(SqlCmd.ExecuteScalar());

}

catch (SqlException e)

{

return -1;

}

finally

{

SqlConn.Close();

SqlCmd.Dispose();

SqlConn.Dispose();

}

return intResult;

}

public static int RunExecuteScalar(string procName,SqlConnection SqlConn)

{

SqlCommand SqlCmd = GetCmd(procName,SqlConn);

int intResult;

try

{

SqlConn.Open();

intResult = Convert.ToInt32(SqlCmd.ExecuteScalar());

}

catch (SqlException e)

{

return -1;

}

finally

{

SqlConn.Close();

SqlCmd.Dispose();

SqlConn.Dispose();

}

return intResult;

}

public static int RunExecuteScalar(string procName,SqlParameter[] prams)

{

SqlConnection SqlConn = GetConn();

SqlCommand SqlCmd = GetCmd(procName, prams,SqlConn);

int intResult;

try

{

SqlConn.Open();

intResult = Convert.ToInt32(SqlCmd.ExecuteScalar());

}

catch (SqlException e)

{

return -1;

}

finally

{

SqlConn.Close();

SqlCmd.Dispose();

SqlConn.Dispose();

}

return intResult;

}

public static int RunExecuteScalar(string procName, SqlParameter[] prams,SqlConnection SqlConn)

{

SqlCommand SqlCmd = GetCmd(procName, prams,SqlConn);

int intResult;

try

{

SqlConn.Open();

intResult = Convert.ToInt32(SqlCmd.ExecuteScalar());

}

catch (SqlException e)

{

return -1;

}

finally

{

SqlConn.Close();

SqlCmd.Dispose();

SqlConn.Dispose();

}

return intResult;

}

/********************Sql操作，CommandType为Text(直接执行SqlCommand语句)**************************************/

///

/// 获取指定查询语句的SqlCommand

///

/// 要执行的Sql查询语句

/// 数据库连接

/// SqlCommand

public static SqlCommand ReturnCmd(string sqlStr,SqlConnection SqlConn)

{

SqlCommand SqlCmd = new SqlCommand(sqlStr,SqlConn);

return SqlCmd;

}

///

/// 获取指定Sql操作的SqlCommand

///

/// 要执行的Sql查询语句

/// SqlCommand

public static SqlCommand ReturnCmd(string sqlStr)

{

SqlConnection SqlConn = GetConn();

SqlCommand SqlCmd = new SqlCommand(sqlStr, SqlConn);

return SqlCmd;

}

///

/// 获取指定查询语句的SqlDataReader

///

/// 要执行的Sql查询语句

/// 数据库连接

/// SqlDataReader

public static SqlDataReader ReturnReader(string sqlStr,SqlConnection SqlConn)

{

SqlCommand SqlCmd = ReturnCmd(sqlStr,SqlConn);

SqlConn.Open();

SqlDataReader SqlDR = SqlCmd.ExecuteReader(CommandBehavior.CloseConnection);

return SqlDR;

}

///

/// 获取指定查询语句的SqlDataReader

///

/// 要执行的Sql查询语句

/// SqlDataReader

public static SqlDataReader ReturnReader(string sqlStr)

{

SqlConnection SqlConn = GetConn();

SqlCommand SqlCmd = ReturnCmd(sqlStr, SqlConn);

SqlConn.Open();

SqlDataReader SqlDR = SqlCmd.ExecuteReader(CommandBehavior.CloseConnection);

return SqlDR;

}

public static int ExeCmd(string sqlStr)

{

SqlConnection SqlConn = GetConn();

SqlCommand SqlCmd = new SqlCommand(sqlStr, SqlConn);

int intResult;

try

{

SqlConn.Open();

intResult= SqlCmd.ExecuteNonQuery();

}

catch (SqlException e)

{

return -1;

}

finally

{

SqlConn.Close();

SqlCmd.Dispose();

SqlConn.Dispose();

}

return intResult;

}

public static int ExeCmd(string sqlStr,SqlConnection SqlConn)

{

SqlCommand SqlCmd = new SqlCommand(sqlStr, SqlConn);

int intResult;

try

{

SqlConn.Open();

intResult = SqlCmd.ExecuteNonQuery();

}

catch (SqlException e)

{

return -1;

}

finally

{

SqlConn.Close();

SqlCmd.Dispose();

SqlConn.Dispose();

}

return intResult;

}

public static DataTable ReturnTable(string sqlStr)

{

SqlConnection SqlConn = GetConn();

SqlCommand SqlCmd = ReturnCmd(sqlStr, SqlConn);

SqlDataAdapter SqlDA= new SqlDataAdapter(SqlCmd);

DataSet DS = new DataSet();

try

{

SqlDA.Fill(DS);

}

catch (SqlException e)

{

}

finally

{

SqlConn.Close();

}

DataTable DT = DS.Tables[0];

DS.Dispose();

SqlDA.Dispose();

SqlCmd.Dispose();

SqlConn.Dispose();

return DT;

}

}