简单通用数据库访问代码

2023年6月21日发(作者：)

简单通⽤数据库访问代码创建连接，执⾏语句，执⾏存储过程等等using System;using c;using ;using tions;using ;using ent;using uration;namespace EXP_Data{ public class C_SQL { #region 通⽤⽅法 // 数据连接池

private SqlConnection con; ///

/// 返回数据库连接字符串

///

///

public static String GetSqlConnection() { String conn = "Data Source=10.0.1.201;Initial Catalog=Somitech;user id=autopo_user;password=4esz5rdx$%;"; return conn; } #endregion #region 执⾏sql字符串 ///

/// 执⾏不带参数的SQL语句

///

///

///

public static int ExecuteSql(String Sqlstr) { String ConnStr = GetSqlConnection(); using (SqlConnection conn = new SqlConnection(ConnStr)) { SqlCommand cmd = new SqlCommand(); tion = conn; dText = Sqlstr; (); eNonQuery(); (); return 1; } } ///

/// 执⾏带参数的SQL语句

///

/// SQL语句

/// 参数对象数组

///

public static int ExecuteSql(String Sqlstr, SqlParameter[] param) { String ConnStr = GetSqlConnection(); using (SqlConnection conn = new SqlConnection(ConnStr)) { SqlCommand cmd = new SqlCommand(); tion = conn; dText = Sqlstr; ge(param); (); eNonQuery(); (); return 1; } } ///

/// 返回DataReader

///

///

///

public static SqlDataReader ExecuteReader(String Sqlstr) { String ConnStr = GetSqlConnection(); SqlConnection conn = new SqlConnection(ConnStr);//返回DataReader时,是不可以⽤using()的

try { SqlCommand cmd = new SqlCommand(); tion = conn; dText = Sqlstr; (); return eReader(onnection);//关闭关联的Connection

} catch //(Exception ex)

{ return null; } } ///

/// 执⾏SQL语句并返回数据表

///

/// SQL语句

///

public static DataTable ExecuteDt(String Sqlstr) { String ConnStr = GetSqlConnection(); using (SqlConnection conn = new SqlConnection(ConnStr)) { SqlDataAdapter da = new SqlDataAdapter(Sqlstr, conn); DataTable dt = new DataTable(); (); (dt); (); return dt; } } ///

/// 执⾏SQL语句并返回DataSet

///

/// SQL语句

///

public static DataSet ExecuteDs(String Sqlstr) { String ConnStr = GetSqlConnection(); using (SqlConnection conn = new SqlConnection(ConnStr)) { SqlDataAdapter da = new SqlDataAdapter(Sqlstr, conn); DataSet ds = new DataSet(); (); (ds); (); return ds; } } #endregion #region 操作存储过程 ///

/// 运⾏存储过程(已重载)

///

/// 存储过程的名字

/// 存储过程的返回值

public int RunProc(string procName) { SqlCommand cmd = CreateCommand(procName, null); eNonQuery(); (); return (int)ters["ReturnValue"].Value; } ///

/// 运⾏存储过程(已重载)

///

/// 存储过程的名字

/// 存储过程的输⼊参数列表

/// 存储过程的返回值

public int RunProc(string procName, SqlParameter[] prams) { SqlCommand cmd = CreateCommand(procName, prams); eNonQuery(); (); return (int)ters[0].Value; } ///

/// 运⾏存储过程(已重载)

///

/// 存储过程的名字

/// 结果集

public void RunProc(string procName, out SqlDataReader dataReader) { SqlCommand cmd = CreateCommand(procName, null); dataReader = eReader(onnection); } ///

/// 运⾏存储过程(已重载) ///

/// 存储过程的名字

/// 存储过程的输⼊参数列表

/// 结果集

public void RunProc(string procName, SqlParameter[] prams, out SqlDataReader dataReader) { SqlCommand cmd = CreateCommand(procName, prams); dataReader = eReader(onnection); } ///

/// 创建Command对象⽤于访问存储过程

///

/// 存储过程的名字

/// 存储过程的输⼊参数列表

/// Command对象

private SqlCommand CreateCommand(string procName, SqlParameter[] prams) { // 确定连接是打开的

Open(); //command = new SqlCommand( sprocName, new SqlConnection( nectionString ) );

SqlCommand cmd = new SqlCommand(procName, con); dType = Procedure; // 添加存储过程的输⼊参数列表

if (prams != null) { foreach (SqlParameter parameter in prams) (parameter); } // 返回Command对象

return cmd; } ///

/// 创建输⼊参数

///

/// 参数名

/// 参数类型

/// 参数⼤⼩

/// 参数值

/// 新参数对象

public SqlParameter MakeInParam(string ParamName, SqlDbType DbType, int Size, object Value) { return MakeParam(ParamName, DbType, Size, , Value); } ///

/// 创建输出参数

///

/// 参数名

/// 参数类型

/// 参数⼤⼩

/// 新参数对象

public SqlParameter MakeOutParam(string ParamName, SqlDbType DbType, int Size) { return MakeParam(ParamName, DbType, Size, , null); } ///

/// 创建存储过程参数

///

/// 参数名

/// 参数类型

/// 参数⼤⼩

/// 参数的⽅向(输⼊/输出)

/// 参数值

/// 新参数对象

public SqlParameter MakeParam(string ParamName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value) { SqlParameter param; if (Size > 0) { param = new SqlParameter(ParamName, DbType, Size); } else { param = new SqlParameter(ParamName, DbType); } ion = Direction; if (!(Direction == && Value == null)) { = Value; } return param; } #endregion #region 数据库连接和关闭 ///

/// 打开连接池

///

private void Open() { // 打开连接池

if (con == null) { //这⾥不仅需要using uration;还要在引⽤⽬录⾥添加

con = new SqlConnection(GetSqlConnection()); (); } } ///

/// 关闭连接池

///

public void Close() { if (con != null) (); } ///

/// 释放连接池

///

public void Dispose() { // 确定连接已关闭

if (con != null) { e(); con = null; } } #endregion }}

2023年6月21日发(作者：)

简单通⽤数据库访问代码创建连接，执⾏语句，执⾏存储过程等等using System;using c;using ;using tions;using ;using ent;using uration;namespace EXP_Data{ public class C_SQL { #region 通⽤⽅法 // 数据连接池

private SqlConnection con; ///

/// 返回数据库连接字符串

///

///

public static String GetSqlConnection() { String conn = "Data Source=10.0.1.201;Initial Catalog=Somitech;user id=autopo_user;password=4esz5rdx$%;"; return conn; } #endregion #region 执⾏sql字符串 ///

/// 执⾏不带参数的SQL语句

///

///

///

public static int ExecuteSql(String Sqlstr) { String ConnStr = GetSqlConnection(); using (SqlConnection conn = new SqlConnection(ConnStr)) { SqlCommand cmd = new SqlCommand(); tion = conn; dText = Sqlstr; (); eNonQuery(); (); return 1; } } ///

/// 执⾏带参数的SQL语句

///

/// SQL语句

/// 参数对象数组

///

public static int ExecuteSql(String Sqlstr, SqlParameter[] param) { String ConnStr = GetSqlConnection(); using (SqlConnection conn = new SqlConnection(ConnStr)) { SqlCommand cmd = new SqlCommand(); tion = conn; dText = Sqlstr; ge(param); (); eNonQuery(); (); return 1; } } ///

/// 返回DataReader

///

///

///

public static SqlDataReader ExecuteReader(String Sqlstr) { String ConnStr = GetSqlConnection(); SqlConnection conn = new SqlConnection(ConnStr);//返回DataReader时,是不可以⽤using()的

try { SqlCommand cmd = new SqlCommand(); tion = conn; dText = Sqlstr; (); return eReader(onnection);//关闭关联的Connection

} catch //(Exception ex)

{ return null; } } ///

/// 执⾏SQL语句并返回数据表

///

/// SQL语句

///

public static DataTable ExecuteDt(String Sqlstr) { String ConnStr = GetSqlConnection(); using (SqlConnection conn = new SqlConnection(ConnStr)) { SqlDataAdapter da = new SqlDataAdapter(Sqlstr, conn); DataTable dt = new DataTable(); (); (dt); (); return dt; } } ///

/// 执⾏SQL语句并返回DataSet

///

/// SQL语句

///

public static DataSet ExecuteDs(String Sqlstr) { String ConnStr = GetSqlConnection(); using (SqlConnection conn = new SqlConnection(ConnStr)) { SqlDataAdapter da = new SqlDataAdapter(Sqlstr, conn); DataSet ds = new DataSet(); (); (ds); (); return ds; } } #endregion #region 操作存储过程 ///

/// 运⾏存储过程(已重载)

///

/// 存储过程的名字

/// 存储过程的返回值

public int RunProc(string procName) { SqlCommand cmd = CreateCommand(procName, null); eNonQuery(); (); return (int)ters["ReturnValue"].Value; } ///

/// 运⾏存储过程(已重载)

///

/// 存储过程的名字

/// 存储过程的输⼊参数列表

/// 存储过程的返回值

public int RunProc(string procName, SqlParameter[] prams) { SqlCommand cmd = CreateCommand(procName, prams); eNonQuery(); (); return (int)ters[0].Value; } ///

/// 运⾏存储过程(已重载)

///

/// 存储过程的名字

/// 结果集

public void RunProc(string procName, out SqlDataReader dataReader) { SqlCommand cmd = CreateCommand(procName, null); dataReader = eReader(onnection); } ///

/// 运⾏存储过程(已重载) ///

/// 存储过程的名字

/// 存储过程的输⼊参数列表

/// 结果集

public void RunProc(string procName, SqlParameter[] prams, out SqlDataReader dataReader) { SqlCommand cmd = CreateCommand(procName, prams); dataReader = eReader(onnection); } ///

/// 创建Command对象⽤于访问存储过程

///

/// 存储过程的名字

/// 存储过程的输⼊参数列表

/// Command对象

private SqlCommand CreateCommand(string procName, SqlParameter[] prams) { // 确定连接是打开的

Open(); //command = new SqlCommand( sprocName, new SqlConnection( nectionString ) );

SqlCommand cmd = new SqlCommand(procName, con); dType = Procedure; // 添加存储过程的输⼊参数列表

if (prams != null) { foreach (SqlParameter parameter in prams) (parameter); } // 返回Command对象

return cmd; } ///

/// 创建输⼊参数

///

/// 参数名

/// 参数类型

/// 参数⼤⼩

/// 参数值

/// 新参数对象

public SqlParameter MakeInParam(string ParamName, SqlDbType DbType, int Size, object Value) { return MakeParam(ParamName, DbType, Size, , Value); } ///

/// 创建输出参数

///

/// 参数名

/// 参数类型

/// 参数⼤⼩

/// 新参数对象

public SqlParameter MakeOutParam(string ParamName, SqlDbType DbType, int Size) { return MakeParam(ParamName, DbType, Size, , null); } ///

/// 创建存储过程参数

///

/// 参数名

/// 参数类型

/// 参数⼤⼩

/// 参数的⽅向(输⼊/输出)

/// 参数值

/// 新参数对象

public SqlParameter MakeParam(string ParamName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value) { SqlParameter param; if (Size > 0) { param = new SqlParameter(ParamName, DbType, Size); } else { param = new SqlParameter(ParamName, DbType); } ion = Direction; if (!(Direction == && Value == null)) { = Value; } return param; } #endregion #region 数据库连接和关闭 ///

/// 打开连接池

///

private void Open() { // 打开连接池

if (con == null) { //这⾥不仅需要using uration;还要在引⽤⽬录⾥添加

con = new SqlConnection(GetSqlConnection()); (); } } ///

/// 关闭连接池

///

public void Close() { if (con != null) (); } ///

/// 释放连接池

///

public void Dispose() { // 确定连接已关闭

if (con != null) { e(); con = null; } } #endregion }}