mysql的数据层基类_数据层基类(MySQL、SQLServer、SQLite)

usingSystem;usingSystem.Data;usingSystem.Data.SqlClient;namespaceSP.DBUtility

{///

///SqlServer数据层基类///

///吴剑 2011-08-24 创建///吴剑 2011-08-24 修改///

public abstract classSqlServerHelper

{//SQL语句执行超时(秒)

private const int COMMAND_TIMEOUT = 3600;#region PrepareCommand

///

///Command预设置///

/// MySqlConnection对象

/// MySqlTransaction对象，可为null

/// MySqlCommand对象

/// CommandType，存储过程或命令行

/// SQL语句或存储过程名

/// MySqlCommand参数数组，可为null

private static void PrepareCommand(SqlConnection conn, SqlTransaction trans, SqlCommand cmd, CommandType cmdType, stringcmdText, SqlParameter[] cmdParms)

{if (conn.State !=ConnectionState.Open)

conn.Open();

cmd.Connection=conn;

cmd.CommandText=cmdText;

cmd.CommandTimeout=COMMAND_TIMEOUT;if (trans != null)

cmd.Transaction=trans;

cmd.CommandType=cmdType;if (cmdParms != null)

{foreach (SqlParameter parm incmdParms)

cmd.Parameters.Add(parm);

}

}#endregion

#region ExecuteNonQuery

///

///执行命令///

/// 数据库连接字符串

/// 命令类型(存储过程或SQL语句)

/// SQL语句或存储过程名

/// MySqlCommand参数数组

/// 返回受引响的记录行数

public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, paramsSqlParameter[] cmdParms)

{

SqlCommand cmd= newSqlCommand();using (SqlConnection conn = newSqlConnection(connectionString))

{

PrepareCommand(conn,null, cmd, cmdType, cmdText, cmdParms);int val =cmd.ExecuteNonQuery();

cmd.Parameters.Clear();returnval;

}

}///

///执行命令///

/// Connection对象

/// 命令类型(存储过程或SQL语句)

/// SQL语句或存储过程名

/// MySqlCommand参数数组

/// 返回受引响的记录行数

public static int ExecuteNonQuery(SqlConnection conn, CommandType cmdType, string cmdText, paramsSqlParameter[] cmdParms)

{

SqlCommand cmd= newSqlCommand();

PrepareCommand(conn,null, cmd, cmdType, cmdText, cmdParms);int val =cmd.ExecuteNonQuery();

cmd.Parameters.Clear();returnval;

}///

///执行事务///

/// MySqlTransaction对象

/// 命令类型(存储过程或SQL语句)

/// SQL语句或存储过程名

/// MySqlCommand参数数组

/// 返回受引响的记录行数

public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, paramsSqlParameter[] cmdParms)

{

SqlCommand cmd= newSqlCommand();

PrepareCommand(trans.Connection, trans, cmd, cmdType, cmdText, cmdParms);int val =cmd.ExecuteNonQuery();

cmd.Parameters.Clear();returnval;

}#endregion

#region ExecuteScalar

///

///执行命令，返回第一行第一列的值///

/// 数据库连接字符串

/// 命令类型(存储过程或SQL语句)

/// SQL语句或存储过程名

/// MySqlCommand参数数组

/// 返回Object对象

public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, paramsSqlParameter[] cmdParms)

{

SqlCommand cmd= newSqlCommand();using (SqlConnection connection = newSqlConnection(connectionString))

{

PrepareCommand(connection,null, cmd, cmdType, cmdText, cmdParms);object val =cmd.ExecuteScalar();

cmd.Parameters.Clear();returnval;

}

}///

///执行命令，返回第一行第一列的值///

/// 数据库连接字符串

/// 命令类型(存储过程或SQL语句)

/// SQL语句或存储过程名

/// MySqlCommand参数数组

/// 返回Object对象

public static object ExecuteScalar(SqlConnection conn, CommandType cmdType, string cmdText, paramsSqlParameter[] cmdParms)

{

SqlCommand cmd= newSqlCommand();

PrepareCommand(conn,null, cmd, cmdType, cmdText, cmdParms);object val =cmd.ExecuteScalar();

cmd.Parameters.Clear();returnval;

}#endregion

#region ExecuteReader

///

///执行命令或存储过程，返回MySqlDataReader对象///注意MySqlDataReader对象使用完后必须Close以释放MySqlConnection资源///

/// 数据库连接字符串

/// 命令类型(存储过程或SQL语句)

/// SQL语句或存储过程名

/// MySqlCommand参数数组

///

public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, paramsSqlParameter[] cmdParms)

{

SqlCommand cmd= newSqlCommand();

SqlConnection conn= newSqlConnection(connectionString);try{

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

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

cmd.Parameters.Clear();returndr;

}catch{

conn.Close();throw;

}

}#endregion

#region ExecuteDataSet

///

///执行命令或存储过程，返回DataSet对象///

/// 数据库连接字符串

/// 命令类型(存储过程或SQL语句)

/// SQL语句或存储过程名

/// MySqlCommand参数数组(可为null值)

///

public static DataSet ExecuteDataSet(string connectionString, CommandType cmdType, string cmdText, paramsSqlParameter[] cmdParms)

{

SqlCommand cmd= newSqlCommand();using (SqlConnection conn = newSqlConnection(connectionString))

{

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

SqlDataAdapter da= newSqlDataAdapter(cmd);

DataSet ds= newDataSet();

da.Fill(ds);

conn.Close();

cmd.Parameters.Clear();returnds;

}

}#endregion}

}