c 连mysql帮助类_一个C#访问数据库的帮助类

using System;

using System.Configuration;

using System.Data;

using System.Data.SqlClient;

using System.Collections;

namespace SQLHelperClass

{

public abstract class SqlHelper

{

public static readonly string ConnectionStringMSSQL = ConfigurationManager.AppSettings["dbPath"].ToString();

private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());

public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)

{

SqlCommand cmd = new SqlCommand();

using (SqlConnection conn = new SqlConnection(connectionString))

{

PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);

int val = cmd.ExecuteNonQuery();

cmd.Parameters.Clear();

return val;

}

}

public static int ExecuteNonQuery(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)

{

SqlCommand cmd = new SqlCommand();

PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);

int val = cmd.ExecuteNonQuery();

cmd.Parameters.Clear();

return val;

}

public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)

{

SqlCommand cmd = new SqlCommand();

PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);

int val = cmd.ExecuteNonQuery();

cmd.Parameters.Clear();

return val;

}

public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)

{

SqlCommand cmd = new SqlCommand();

SqlConnection conn = new SqlConnection(connectionString);

try

{

PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);

SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

cmd.Parameters.Clear();

return rdr;

}

catch

{

conn.Close();

throw;

}

}

public static DataSet ExecuteDataSet(string connectionString, string cmdText)

{

DataSet __ds = new DataSet();

try

{

SqlDataAdapter sda = new SqlDataAdapter(cmdText, connectionString);

sda.Fill(__ds);

}

catch

{

throw;

}

return __ds;

}

public static DataSet ExecuteDataSet(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)

{

DataSet __ds = new DataSet();

SqlCommand cmd = new SqlCommand();

SqlConnection conn = new SqlConnection(connectionString);

SqlDataAdapter sda = new SqlDataAdapter();

try

{

PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);

sda.SelectCommand = cmd;

sda.Fill(__ds);

cmd.Parameters.Clear();

return __ds;

}

catch

{

conn.Close();

throw;

}

}

public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)

{

SqlCommand cmd = new SqlCommand();

using (SqlConnection connection = new SqlConnection(connectionString))

{

PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);

object val = cmd.ExecuteScalar();

cmd.Parameters.Clear();

return val;

}

}

public static object ExecuteScalar(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)

{

SqlCommand cmd = new SqlCommand();

PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);

object val = cmd.ExecuteScalar();

cmd.Parameters.Clear();

return val;

}

public static void CacheParameters(string cacheKey, params SqlParameter[] commandParameters)

{

parmCache[cacheKey] = commandParameters;

}

public static SqlParameter[] GetCachedParameters(string cacheKey)

{

SqlParameter[] cachedParms = (SqlParameter[])parmCache[cacheKey];

if (cachedParms == null)

return null;

SqlParameter[] clonedParms = new SqlParameter[cachedParms.Length];

for (int i = 0, j = cachedParms.Length; i < j; i++)

clonedParms[i] = (SqlParameter)((ICloneable)cachedParms[i]).Clone();

return clonedParms;

}

private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, 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 = cmdType;

if (cmdParms != null)

{

foreach (SqlParameter parm in cmdParms)

cmd.Parameters.Add(parm);

}

}

}

}

分享到：

2009-01-14 11:06

浏览 2294

评论