现在有个疑点:正常情况下,在执行sql语句时,调用sqlhelper类,通过打开数据库,执行完成sql之后,关闭数据库!如果是两条或以上sql语句都需要执行时,应该是打开数据库,然后这些sql语句都执行完之后,再关闭数据库,这样减少对服务器的压力!现在问题是在sqlhelper中的类应该怎么写!,就这两种情况而言主要是考虑的性能方面的不知道那位做过相关的东西?
解决方案 »
- 如何在字符串中加入字符串?
- |zyciis| 如何通过反般Reflector一个List<UserInfo>对像 第二贴
- 数据库insert into的问题,在线等,谢谢
- 网站上允许 .csv 文件可以下载,在iis哪里设置?
- 关于Petshop中的DALFactory的问题?
- 关于DropDownList问题
- ASP.NET 修改文档属性
- CalendarExtender不能正确赋值给SqlDataSource的问题
- OWC制作图表的问题谁以前做过的帮忙看看,在线等(100分)
- 初学者问,怎样得到 URL 中的参数
- JS与后台的传值加密问题
- 未能加载文件或程序集“System.Windows.Forms, Version=2.0.0.0, Culture=neutral, Publ
你能否提供一下sqlhelper代码?
{
if (connection == null) throw new ArgumentNullException("connection"); // Create a command and prepare it for execution
SqlCommand cmd = new SqlCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection); // Finally, execute the command
int retval = cmd.ExecuteNonQuery(); // Detach the SqlParameters from the command object, so they can be used again
cmd.Parameters.Clear();
if (mustCloseConnection)
connection.Close();
return retval;
}
所有的sql语句都是最终考这个执行,
public static int ExecuteNonQuery(string connectionString, string spName, params object[] parameterValues)
public static object ExecuteScalar(string connectionString, string spName, params object[] parameterValues)
诸如此类的,根据要求选择就好了
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;namespace DAL
{
public static class DBHelper
{ /// <summary>
/// 从web.config中获得数据库连接字符串
/// </summary>
public static string Connstr
{
get { return ConfigurationManager.ConnectionStrings["conn"].ConnectionString; }
}
#region sql语句部分
/// <summary>
/// 执行sql语句返回DataTable
/// </summary>
/// <param name="sql">安全的sql语句</param>
/// <returns>根据sql语句得到所有记录</returns>
public static DataTable GetDataTable(string sql)
{ using (SqlConnection conn = new SqlConnection(Connstr))
{ SqlDataAdapter sda = new SqlDataAdapter(sql, conn);
DataTable dt = new DataTable();
sda.Fill(dt);
return dt;
}
}
/// <summary>
/// 执行带参数的sql语句返回DataTable
/// </summary>
/// <param name="sql">带参数的sql语句</param>
/// <param name="values">参数</param>
/// <returns>根据sql语句得到所有记录</returns>
public static DataTable GetDataTable(string sql, params SqlParameter[] values)
{
using (SqlConnection conn = new SqlConnection(Connstr))
{
SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
cmd.Parameters.AddRange(values);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
sda.Fill(dt);
return dt;
}
}
/// <summary>
/// 执行无参数的sql语句
/// </summary>
/// <param name="sql">sql语句</param>
/// <returns>影响的行数</returns>
public static int ExecuteCommand(string sql)
{
using (SqlConnection conn = new SqlConnection(Connstr))
{
SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
int num = cmd.ExecuteNonQuery();
conn.Close();
return num;
}
}
/// <summary>
/// 执行有参数的sql语句
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="values">参数</param>
/// <returns>影响的行数</returns>
public static int ExecuteCommand(string sql, params SqlParameter[] values)
{
using (SqlConnection conn = new SqlConnection(Connstr))
{
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddRange(values);
conn.Open();
int num = cmd.ExecuteNonQuery();
conn.Close();
return num;
}
}
/// <summary>
/// 执行无参数的sql语句,返回首行首列
/// </summary>
/// <param name="sql">sql语句</param>
/// <returns>返回首行首列</returns>
public static object GetScalar(string sql)
{
using (SqlConnection conn = new SqlConnection(Connstr))
{
SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
object obj = cmd.ExecuteScalar();
conn.Close();
return obj;
}
}
/// <summary>
/// 执行有参数的sql语句,返回首行首列
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="values">参数,是否返回首行首列</param>
/// <returns>返回首行首列</returns>
public static object GetScalar(string sql, params SqlParameter[] values)
{
using (SqlConnection conn = new SqlConnection(Connstr))
{
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddRange(values);
conn.Open();
object obj = cmd.ExecuteScalar();
conn.Close();
return obj;
}
} /// <summary>
/// 带事务的执行多条sql语句
/// </summary>
/// <param name="sqls"></param>
/// <returns></returns>
public static bool ExecuteCommandByTran(params string[] sqls)
{ using (SqlConnection conn = new SqlConnection(Connstr))
{
conn.Open();
//为数据库连接打开事务
SqlTransaction tran = conn.BeginTransaction();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
//为命令对象指定要应用事务
cmd.Transaction = tran;
try
{ for (int i = 0; i < sqls.Length; i++)
{
cmd.CommandText = sqls[i];
cmd.ExecuteNonQuery();
} //没有异常的情况下,提交事务
tran.Commit(); return true;
}
catch
{
//事务滚回去
tran.Rollback(); return false; }
finally
{ conn.Close();
} }
} #endregion #region 存储过程部分
/// <summary>
/// 执行sql语句返回DataTable
/// </summary>
/// <param name="sql">安全的sql语句</param>
/// <returns>根据sql语句得到所有记录</returns>
public static DataTable GetDataTableByProcedure(string sql)
{ using (SqlConnection conn = new SqlConnection(Connstr))
{ SqlCommand cmd = new SqlCommand(sql, conn);
cmd.CommandType = CommandType.StoredProcedure;
conn.Open();
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
sda.Fill(dt);
return dt;
}
}
/// <summary>
/// 执行带参数的sql语句返回DataTable
/// </summary>
/// <param name="sql">带参数的sql语句</param>
/// <param name="values">参数</param>
/// <returns>根据sql语句得到所有记录</returns>
public static DataTable GetDataTableByProcedure(string sql, params SqlParameter[] values)
{
using (SqlConnection conn = new SqlConnection(Connstr))
{
SqlCommand cmd = new SqlCommand(sql, conn);
//指定查询的方式使用存储过程
cmd.CommandType = CommandType.StoredProcedure;
conn.Open();
cmd.Parameters.AddRange(values);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
sda.Fill(dt);
return dt;
}
}
/// <summary>
/// 执行无参数的sql语句
/// </summary>
/// <param name="sql">sql语句</param>
/// <returns>影响的行数</returns>
public static int ExecuteCommandByProcedure(string sql)
{
using (SqlConnection conn = new SqlConnection(Connstr))
{
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.CommandType = CommandType.StoredProcedure;
conn.Open();
int num = cmd.ExecuteNonQuery();
conn.Close();
return num;
}
}
/// <summary>
/// 执行有参数的sql语句
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="values">参数</param>
/// <returns>影响的行数</returns>
public static int ExecuteCommandByProcedure(string sql, params SqlParameter[] values)
{
using (SqlConnection conn = new SqlConnection(Connstr))
{
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(values);
conn.Open();
int num = cmd.ExecuteNonQuery();
conn.Close();
return num;
}
}
/// <summary>
/// 执行无参数的sql语句,返回首行首列
/// </summary>
/// <param name="sql">sql语句</param>
/// <returns>返回首行首列</returns>
public static object GetScalarByProcedure(string sql)
{
using (SqlConnection conn = new SqlConnection(Connstr))
{
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.CommandType = CommandType.StoredProcedure;
conn.Open();
object obj = cmd.ExecuteScalar();
conn.Close();
return obj;
}
}
/// <summary>
/// 执行有参数的sql语句,返回首行首列
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="values">参数,是否返回首行首列</param>
/// <returns>返回首行首列</returns>
public static object ExecuteScalarByProcedure(string sql, params SqlParameter[] values)
{
using (SqlConnection conn = new SqlConnection(Connstr))
{
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(values);
conn.Open();
object obj = cmd.ExecuteScalar();
conn.Close();
return obj;
}
}
#endregion
可以多个绑定成一个传