access:
Code
using System;
using System.Data.OleDb;
using System.Data;
using System.Diagnostics;
using System.Collections.Generic;
public class AccessDBUtil
{
private static String connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
System.Web.HttpContext.Current.Server.MapPath(System.Configuration.ConfigurationManager.AppSettings["ConnectString"]);
private AccessDBUtil()
{
}
//执行单条插入语句,并返回id,不需要返回id的用ExceuteNonQuery执行。
public static int ExecuteInsert(string sql,OleDbParameter[] parameters)
{
//Debug.WriteLine(sql);
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
OleDbCommand cmd = new OleDbCommand(sql, connection);
try
{
connection.Open();
if(parameters!=null) cmd.Parameters.AddRange(parameters);
cmd.ExecuteNonQuery();
cmd.CommandText = @"select @@identity";
int value = Int32.Parse(cmd.ExecuteScalar().ToString());
return value;
}
catch (Exception e)
{
throw e;
}
}
}
public static int ExecuteInsert(string sql)
{
return ExecuteInsert(sql,null);
}
//执行带参数的sql语句,返回影响的记录数(insert,update,delete)
public static int ExecuteNonQuery(string sql,OleDbParameter[] parameters)
{
//Debug.WriteLine(sql);
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
OleDbCommand cmd = new OleDbCommand(sql, connection);
try
{
connection.Open();
if(parameters!=null) cmd.Parameters.AddRange(parameters);
int rows = cmd.ExecuteNonQuery();
return rows;
}
catch (Exception e)
{
throw e;
}
}
}
//执行不带参数的sql语句,返回影响的记录数
//不建议使用拼出来SQL
public static int ExecuteNonQuery(string sql)
{
return ExecuteNonQuery(sql,null);
}
//执行单条语句返回第一行第一列,可以用来返回count(*)
public static int ExecuteScalar(string sql,OleDbParameter[] parameters)
{
//Debug.WriteLine(sql);
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
OleDbCommand cmd = new OleDbCommand(sql, connection);
try
{
connection.Open();
if(parameters!=null) cmd.Parameters.AddRange(parameters);
int value = Int32.Parse(cmd.ExecuteScalar().ToString());
return value;
}
catch (Exception e)
{
throw e;
}
}
}
public static int ExecuteScalar(string sql)
{
return ExecuteScalar(sql,null);
}
//执行事务
public static void ExecuteTrans(List<string> sqlList,List<OleDbParameter[]> paraList)
{
//Debug.WriteLine(sql);
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
OleDbCommand cmd = new OleDbCommand();
OleDbTransaction transaction = null;
cmd.Connection = connection;
try
{
connection.Open();
transaction = connection.BeginTransaction();
cmd.Transaction = transaction;
for(int i=0;i<sqlList.Count;i++)
{
cmd.CommandText=sqlList[i];
if(paraList!=null&¶List[i]!=null)
{
cmd.Parameters.Clear();
cmd.Parameters.AddRange(paraList[i]);
}
cmd.ExecuteNonQuery();
}
transaction.Commit();
}
catch (Exception e)
{
try
{
transaction.Rollback();
}
catch
{
}
throw e;
}
}
}
public static void ExecuteTrans(List<string> sqlList)
{
ExecuteTrans(sqlList,null);
}
//执行查询语句,返回dataset
public static DataSet ExecuteQuery(string sql,OleDbParameter[] parameters)
{
//Debug.WriteLine(sql);
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
DataSet ds = new DataSet();
try
{
connection.Open();
OleDbDataAdapter da = new OleDbDataAdapter(sql, connection);
if(parameters!=null) da.SelectCommand.Parameters.AddRange(parameters);
da.Fill(ds,"ds");
}
catch (Exception ex)
{
throw ex;
}
return ds;
}
}
public static DataSet ExecuteQuery(string sql)
{
return ExecuteQuery(sql,null);
}
//执行查询语句返回datareader,使用后要注意close
//这个函数在AccessPageUtils中使用,执行其它查询时最好不要用
public static OleDbDataReader ExecuteReader(string sql)
{
//Debug.WriteLine(sql);
OleDbConnection connection = new OleDbConnection(connectionString);
OleDbCommand cmd = new OleDbCommand(sql, connection);
try
{
connection.Open();
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception e)
{
connection.Close();
throw e;
}
}
}
using System;
using System.Data.OleDb;
using System.Data;
using System.Diagnostics;
using System.Collections.Generic;
public class AccessDBUtil
{
private static String connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
System.Web.HttpContext.Current.Server.MapPath(System.Configuration.ConfigurationManager.AppSettings["ConnectString"]);
private AccessDBUtil()
{
}
//执行单条插入语句,并返回id,不需要返回id的用ExceuteNonQuery执行。
public static int ExecuteInsert(string sql,OleDbParameter[] parameters)
{
//Debug.WriteLine(sql);
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
OleDbCommand cmd = new OleDbCommand(sql, connection);
try
{
connection.Open();
if(parameters!=null) cmd.Parameters.AddRange(parameters);
cmd.ExecuteNonQuery();
cmd.CommandText = @"select @@identity";
int value = Int32.Parse(cmd.ExecuteScalar().ToString());
return value;
}
catch (Exception e)
{
throw e;
}
}
}
public static int ExecuteInsert(string sql)
{
return ExecuteInsert(sql,null);
}
//执行带参数的sql语句,返回影响的记录数(insert,update,delete)
public static int ExecuteNonQuery(string sql,OleDbParameter[] parameters)
{
//Debug.WriteLine(sql);
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
OleDbCommand cmd = new OleDbCommand(sql, connection);
try
{
connection.Open();
if(parameters!=null) cmd.Parameters.AddRange(parameters);
int rows = cmd.ExecuteNonQuery();
return rows;
}
catch (Exception e)
{
throw e;
}
}
}
//执行不带参数的sql语句,返回影响的记录数
//不建议使用拼出来SQL
public static int ExecuteNonQuery(string sql)
{
return ExecuteNonQuery(sql,null);
}
//执行单条语句返回第一行第一列,可以用来返回count(*)
public static int ExecuteScalar(string sql,OleDbParameter[] parameters)
{
//Debug.WriteLine(sql);
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
OleDbCommand cmd = new OleDbCommand(sql, connection);
try
{
connection.Open();
if(parameters!=null) cmd.Parameters.AddRange(parameters);
int value = Int32.Parse(cmd.ExecuteScalar().ToString());
return value;
}
catch (Exception e)
{
throw e;
}
}
}
public static int ExecuteScalar(string sql)
{
return ExecuteScalar(sql,null);
}
//执行事务
public static void ExecuteTrans(List<string> sqlList,List<OleDbParameter[]> paraList)
{
//Debug.WriteLine(sql);
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
OleDbCommand cmd = new OleDbCommand();
OleDbTransaction transaction = null;
cmd.Connection = connection;
try
{
connection.Open();
transaction = connection.BeginTransaction();
cmd.Transaction = transaction;
for(int i=0;i<sqlList.Count;i++)
{
cmd.CommandText=sqlList[i];
if(paraList!=null&¶List[i]!=null)
{
cmd.Parameters.Clear();
cmd.Parameters.AddRange(paraList[i]);
}
cmd.ExecuteNonQuery();
}
transaction.Commit();
}
catch (Exception e)
{
try
{
transaction.Rollback();
}
catch
{
}
throw e;
}
}
}
public static void ExecuteTrans(List<string> sqlList)
{
ExecuteTrans(sqlList,null);
}
//执行查询语句,返回dataset
public static DataSet ExecuteQuery(string sql,OleDbParameter[] parameters)
{
//Debug.WriteLine(sql);
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
DataSet ds = new DataSet();
try
{
connection.Open();
OleDbDataAdapter da = new OleDbDataAdapter(sql, connection);
if(parameters!=null) da.SelectCommand.Parameters.AddRange(parameters);
da.Fill(ds,"ds");
}
catch (Exception ex)
{
throw ex;
}
return ds;
}
}
public static DataSet ExecuteQuery(string sql)
{
return ExecuteQuery(sql,null);
}
//执行查询语句返回datareader,使用后要注意close
//这个函数在AccessPageUtils中使用,执行其它查询时最好不要用
public static OleDbDataReader ExecuteReader(string sql)
{
//Debug.WriteLine(sql);
OleDbConnection connection = new OleDbConnection(connectionString);
OleDbCommand cmd = new OleDbCommand(sql, connection);
try
{
connection.Open();
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception e)
{
connection.Close();
throw e;
}
}
}
sql server:
Code
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
class SqlServerUtil
{
private static String connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectString"];
private SqlServerUtil() { }
#region 查询操作
/// <summary>
/// 执行查询语句,返回dataset
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="parameters">参数</param>
/// <returns>所查询的数据集</returns>
public static DataSet ExecuteQuery(string sql, SqlParameter[] parameters)
{
using (SqlConnection con = new SqlConnection(connectionString))
{
DataSet ds = new DataSet();
try
{
con.Open();
SqlDataAdapter adapter = new SqlDataAdapter(sql, con);
if (parameters != null)
adapter.SelectCommand.Parameters.AddRange(parameters);
adapter.Fill(ds);
}
catch (Exception ex)
{
throw ex;
}
return ds;
}
}
public static DataSet ExecuteQuery(string sql)
{
return ExecuteQuery(sql, null);
}
/// <summary>
/// 此查询操作只返回所查询结果的第一行第一列,速度比使用Adapter快很多
/// </summary>
/// <param name="sql"></param>
/// <param name="parameters"></param>
/// <returns>返回第一行第一列,类型为object</returns>
public static object ExecuteScalar(string sql, SqlParameter[] parameters)
{
using (SqlConnection con = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand(sql,con);
try
{
con.Open();
if (parameters != null)
{
cmd.Parameters.AddRange(parameters);
}
return cmd.ExecuteScalar();
}
catch (Exception ex)
{
throw ex;
}
}
}
public static object ExecuteScalar(string sql)
{
return ExecuteScalar(sql, null);
}
/// <summary>
/// 执行查询语句返回datareader,使用后要注意close
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static SqlDataReader ExecuteReader(string sql)
{
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(sql, con);
try
{
con.Open();
//传递CommandBehavior.CloseConnection 枚举变量,这样在调用SqlDataReader的Close方法时会自动关闭数据库连接
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
con.Close();
throw;
}
}
#endregion
#region 插入、删除、更新操作
/// <summary>
/// 执行带参数的sql语句,返回影响的记录数
/// </summary>
/// <param name="sql"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public static int ExecuteNonQuery(string sql, SqlParameter[] parameters)
{
using (SqlConnection con = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand(sql, con);
try
{
con.Open();
if (parameters != null)
{
cmd.Parameters.AddRange(parameters);
}
return cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
}
}
public static int ExecuteNonQuery(string sql)
{
return ExecuteNonQuery(sql, null);
}
/// <summary>
/// 执行单条插入语句,并返回id,不需要返回id的用ExceuteNonQuery执行。
/// </summary>
/// <param name="sql"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public static int ExecuteInsert(string sql, SqlParameter[] parameters)
{
using (SqlConnection con = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand(sql, con);
try
{
con.Open();
if (parameters != null)
{
cmd.Parameters.AddRange(parameters);
}
cmd.ExecuteNonQuery();
cmd.CommandText = @"select @@identity";
return int.Parse(cmd.ExecuteScalar().ToString());
}
catch (Exception ex)
{
throw ex;
}
}
}
public static int ExecuteInsert(string sql)
{
return ExecuteInsert(sql, null);
}
#endregion
#region 事务处理
public static void ExecuteTrans(List<string> sqlList, List<SqlParameter[]> paraList)
{
using (SqlConnection con = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand();
SqlTransaction transaction = null;
cmd.Connection = con;
try
{
con.Open();
transaction = con.BeginTransaction();
cmd.Transaction = transaction;
for (int i = 0; i < sqlList.Count; i++)
{
cmd.CommandText = sqlList[i];
if (paraList != null && paraList[i] != null)
{
cmd.Parameters.Clear();
cmd.Parameters.AddRange(paraList[i]);
}
cmd.ExecuteNonQuery();
}
transaction.Commit();
}
catch (Exception ex)
{
try
{
transaction.Rollback();
}
catch
{
}
throw ex;
}
}
}
#endregion
}
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
class SqlServerUtil
{
private static String connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectString"];
private SqlServerUtil() { }
#region 查询操作
/// <summary>
/// 执行查询语句,返回dataset
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="parameters">参数</param>
/// <returns>所查询的数据集</returns>
public static DataSet ExecuteQuery(string sql, SqlParameter[] parameters)
{
using (SqlConnection con = new SqlConnection(connectionString))
{
DataSet ds = new DataSet();
try
{
con.Open();
SqlDataAdapter adapter = new SqlDataAdapter(sql, con);
if (parameters != null)
adapter.SelectCommand.Parameters.AddRange(parameters);
adapter.Fill(ds);
}
catch (Exception ex)
{
throw ex;
}
return ds;
}
}
public static DataSet ExecuteQuery(string sql)
{
return ExecuteQuery(sql, null);
}
/// <summary>
/// 此查询操作只返回所查询结果的第一行第一列,速度比使用Adapter快很多
/// </summary>
/// <param name="sql"></param>
/// <param name="parameters"></param>
/// <returns>返回第一行第一列,类型为object</returns>
public static object ExecuteScalar(string sql, SqlParameter[] parameters)
{
using (SqlConnection con = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand(sql,con);
try
{
con.Open();
if (parameters != null)
{
cmd.Parameters.AddRange(parameters);
}
return cmd.ExecuteScalar();
}
catch (Exception ex)
{
throw ex;
}
}
}
public static object ExecuteScalar(string sql)
{
return ExecuteScalar(sql, null);
}
/// <summary>
/// 执行查询语句返回datareader,使用后要注意close
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static SqlDataReader ExecuteReader(string sql)
{
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(sql, con);
try
{
con.Open();
//传递CommandBehavior.CloseConnection 枚举变量,这样在调用SqlDataReader的Close方法时会自动关闭数据库连接
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
con.Close();
throw;
}
}
#endregion
#region 插入、删除、更新操作
/// <summary>
/// 执行带参数的sql语句,返回影响的记录数
/// </summary>
/// <param name="sql"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public static int ExecuteNonQuery(string sql, SqlParameter[] parameters)
{
using (SqlConnection con = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand(sql, con);
try
{
con.Open();
if (parameters != null)
{
cmd.Parameters.AddRange(parameters);
}
return cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
}
}
public static int ExecuteNonQuery(string sql)
{
return ExecuteNonQuery(sql, null);
}
/// <summary>
/// 执行单条插入语句,并返回id,不需要返回id的用ExceuteNonQuery执行。
/// </summary>
/// <param name="sql"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public static int ExecuteInsert(string sql, SqlParameter[] parameters)
{
using (SqlConnection con = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand(sql, con);
try
{
con.Open();
if (parameters != null)
{
cmd.Parameters.AddRange(parameters);
}
cmd.ExecuteNonQuery();
cmd.CommandText = @"select @@identity";
return int.Parse(cmd.ExecuteScalar().ToString());
}
catch (Exception ex)
{
throw ex;
}
}
}
public static int ExecuteInsert(string sql)
{
return ExecuteInsert(sql, null);
}
#endregion
#region 事务处理
public static void ExecuteTrans(List<string> sqlList, List<SqlParameter[]> paraList)
{
using (SqlConnection con = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand();
SqlTransaction transaction = null;
cmd.Connection = con;
try
{
con.Open();
transaction = con.BeginTransaction();
cmd.Transaction = transaction;
for (int i = 0; i < sqlList.Count; i++)
{
cmd.CommandText = sqlList[i];
if (paraList != null && paraList[i] != null)
{
cmd.Parameters.Clear();
cmd.Parameters.AddRange(paraList[i]);
}
cmd.ExecuteNonQuery();
}
transaction.Commit();
}
catch (Exception ex)
{
try
{
transaction.Rollback();
}
catch
{
}
throw ex;
}
}
}
#endregion
}