我有一个数据库访问类:
public class SqlHelper
{ private static SqlConnection connection = null; private static String conString = "server=192.168.0.1;User ID=sa;Password=;database=HrSysTool;Connection Reset=FALSE"; public static SqlConnection Connection
{
get
{
if (connection == null)
{
//重新创建
connection = SqlHelper.ConnectionDB();
}
if (connection != null && connection.State == ConnectionState.Closed)
{
//重新连接
connection.Open();
}
return connection;
}
} public static SqlConnection ConnectionDB()
{
SqlConnection conn = null;
try
{
conn = new SqlConnection(conString);
conn.Open();
SqlHelper.connection = conn;
}
catch
{
throw new Exception("未配置数据源连接信息!");
SqlHelper.connection = null;
} return conn;
} /// <summary>
/// 关闭数据连接
/// </summary>
/// <returns></returns>
public static bool DisConnectionDB()
{
try
{
if (SqlHelper.connection != null && SqlHelper.connection.State != ConnectionState.Closed)
{
//关闭数据源 SqlHelper.connection.Close();
}
SqlHelper.connection = null;
return true;
}
catch (Exception)
{
return false;
}
} /// <summary>
/// 查询方法,适用于返回DataSet数据的SQL语句,使用当前系统的Connection
/// </summary>
/// <param name="cmdType">Command类型</param>
/// <param name="cmdText">SQL脚本</param>
/// <param name="commandParameters">SQL参数集合</param>
/// <returns>返回DataSet数据集</returns>
public static DataSet FillDataSet(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = SqlHelper.Connection;
DataSet dataSet = new DataSet(); try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
dataAdapter.Fill(dataSet);
cmd.Parameters.Clear();
return dataSet;
}
catch
{
throw;
}
} /// <summary>
/// 执行方法,适用于不返回数据的SQL语句,使用当前系统的Connection
/// </summary>
/// <param name="cmdType">Command类型</param>
/// <param name="cmdText">SQL脚本</param>
/// <param name="commandParameters">SQL参数集合</param>
/// <returns>返回影响的行数</returns>
public static int ExecuteNonQuery(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{ SqlCommand cmd = new SqlCommand(); using (SqlConnection conn = SqlHelper.Connection)
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
} /// <summary>
/// 执行方法,适用于不返回数据的SQL语句,通过链接字符串创建的Connection
/// </summary>
/// <param name="connectionString">数据库链接字符串</param>
/// <param name="cmdType">Command类型</param>
/// <param name="cmdText">SQL脚本</param>
/// <param name="commandParameters">SQL参数集合</param>
/// <returns>返回影响的行数</returns>
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;
}
}.................
}
代码比较长,希望不要晕,现在的问题是,当我当前数据库链接的的方式系统一切正常,但只要我用过一次ExecuteNonQuery(String connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters),就是通过其他途径 如:链接字符串、其他的SqlConnection,或者其他SqlConnection的SqlTransaction 创建的 SqlConnection,那么再用 当前数据库链接的时候,就会报错:InvalidOperationException: ConnectionString 属性尚未初始化,也就是说经过这样一个动作,SqlConnection的链接字符串被搞丢了,请分析一下,这个代码的漏洞在哪儿???
public class SqlHelper
{ private static SqlConnection connection = null; private static String conString = "server=192.168.0.1;User ID=sa;Password=;database=HrSysTool;Connection Reset=FALSE"; public static SqlConnection Connection
{
get
{
if (connection == null)
{
//重新创建
connection = SqlHelper.ConnectionDB();
}
if (connection != null && connection.State == ConnectionState.Closed)
{
//重新连接
connection.Open();
}
return connection;
}
} public static SqlConnection ConnectionDB()
{
SqlConnection conn = null;
try
{
conn = new SqlConnection(conString);
conn.Open();
SqlHelper.connection = conn;
}
catch
{
throw new Exception("未配置数据源连接信息!");
SqlHelper.connection = null;
} return conn;
} /// <summary>
/// 关闭数据连接
/// </summary>
/// <returns></returns>
public static bool DisConnectionDB()
{
try
{
if (SqlHelper.connection != null && SqlHelper.connection.State != ConnectionState.Closed)
{
//关闭数据源 SqlHelper.connection.Close();
}
SqlHelper.connection = null;
return true;
}
catch (Exception)
{
return false;
}
} /// <summary>
/// 查询方法,适用于返回DataSet数据的SQL语句,使用当前系统的Connection
/// </summary>
/// <param name="cmdType">Command类型</param>
/// <param name="cmdText">SQL脚本</param>
/// <param name="commandParameters">SQL参数集合</param>
/// <returns>返回DataSet数据集</returns>
public static DataSet FillDataSet(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = SqlHelper.Connection;
DataSet dataSet = new DataSet(); try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
dataAdapter.Fill(dataSet);
cmd.Parameters.Clear();
return dataSet;
}
catch
{
throw;
}
} /// <summary>
/// 执行方法,适用于不返回数据的SQL语句,使用当前系统的Connection
/// </summary>
/// <param name="cmdType">Command类型</param>
/// <param name="cmdText">SQL脚本</param>
/// <param name="commandParameters">SQL参数集合</param>
/// <returns>返回影响的行数</returns>
public static int ExecuteNonQuery(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{ SqlCommand cmd = new SqlCommand(); using (SqlConnection conn = SqlHelper.Connection)
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
} /// <summary>
/// 执行方法,适用于不返回数据的SQL语句,通过链接字符串创建的Connection
/// </summary>
/// <param name="connectionString">数据库链接字符串</param>
/// <param name="cmdType">Command类型</param>
/// <param name="cmdText">SQL脚本</param>
/// <param name="commandParameters">SQL参数集合</param>
/// <returns>返回影响的行数</returns>
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;
}
}.................
}
代码比较长,希望不要晕,现在的问题是,当我当前数据库链接的的方式系统一切正常,但只要我用过一次ExecuteNonQuery(String connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters),就是通过其他途径 如:链接字符串、其他的SqlConnection,或者其他SqlConnection的SqlTransaction 创建的 SqlConnection,那么再用 当前数据库链接的时候,就会报错:InvalidOperationException: ConnectionString 属性尚未初始化,也就是说经过这样一个动作,SqlConnection的链接字符串被搞丢了,请分析一下,这个代码的漏洞在哪儿???
解决方案 »
- C# 写的api hook 谁帮我看看为什么获取不到数据,断点地方都没有运行
- office 编程 GC 有意思问题
- 用Visaul C#怎么写 打开其他某某程序后会连带运行自己的程序
- c#中怎样调用外部编写的dll
- 如何定做一个数据库公共连接类
- 急寻Microsoft Visual Studio .NET 2003 引导程序插件
- 急啊. .net 中水晶报表的 的控件 .怎么用啊。
- ????在线等待!
- 在ArrayList里,是否有函数可以直接查找某个取值范围内的数的个数?
- c#有趣数学公式小问题,求解。
- 多表查询的ds.Table["xxx"].rows.count 判断!急!!
- 请教List排序问题
要关闭连接
{}
我这样用是不是 系统自动回收呢??
不过你的编码好像很随意,最起码数据库应该尽量晚开,尽量早关。
连接字符串为什么不写在web.config里?
{
//重新连接
connection.Open();
}改成 //重新创建
connection = SqlHelper.ConnectionDB();虽然可以解决问题,但会频繁的链接主数据库
System.Data.SqlClient.SqlConnection conn1 = null;
using (conn1 = new System.Data.SqlClient.SqlConnection(connstr))
{
conn1.Open();
Console.WriteLine(conn1.ConnectionString);
}
Console.WriteLine(conn1.ConnectionString);
Console.ReadLine();
因为sqlconnection关闭后其ConnectionString属性会被清空。