在我的程序中需要对一个数据库进行多种操作。
我创建了一个SqlConnection但是创建了多个SqlCommand。
我每当执行SqlCommand操作的时候打开SqlConnection执行完了在关闭SqlConnection的连接,我认为这样是可以的。
但是数据库从2000升级到2005好像就不行了。
必须把SqlCommand对象干掉执行一个SqlCommand.Dispose()才能保证后面的SqlCommand使用正常。
不知道大家有什么高见。
我创建了一个SqlConnection但是创建了多个SqlCommand。
我每当执行SqlCommand操作的时候打开SqlConnection执行完了在关闭SqlConnection的连接,我认为这样是可以的。
但是数据库从2000升级到2005好像就不行了。
必须把SqlCommand对象干掉执行一个SqlCommand.Dispose()才能保证后面的SqlCommand使用正常。
不知道大家有什么高见。
我觉的创库 一个SqlCommand 对象就可以了,执行完成之后,再重新设置他的CommandText 然后再执行就可以了
没什么问题嘛或者 你可以创建多个SqlCommand对象,一起执行,最后再关闭 SqlConnection就可以了,不知道你的代码有什么问题,还是贴出来看看吧
using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
namespace SqlDAL
{
public abstract class SQLHelper
{
public static readonly string ConnString = System.Configuration.ConfigurationSettings.AppSettings["consql"].ToString();
private static Hashtable ht = Hashtable.Synchronized(new Hashtable());
/// <summary>
/// 删除,增加,修改操作
/// /// </summary>
/// <param name="conn">连接</param>
/// <param name="cmdType">类型</param>
/// <param name="cmdText">sql语句</param>
/// <param name="cmdParams">参数</param>
/// <returns></returns>
public static int ExecuteNonQuery(SqlConnection conn,CommandType cmdType,string cmdText,SqlTransaction trans,params SqlParameter[] cmdParams)
{
SqlCommand comm = new SqlCommand();
PrepareCommand(comm,conn,trans,cmdType,cmdText,cmdParams);
try
{
int count = comm.ExecuteNonQuery();
comm.Parameters.Clear();
return count;
}
catch(Exception ex)
{
ex.Message.ToString();
return -1;
}
}
public static int ExecuteNonQuery(SqlConnection conn,CommandType cmdType,string cmdText,SqlTransaction trans)
{
SqlCommand comm = new SqlCommand(); if(conn.State != ConnectionState.Open)
{
conn.Open();
}
comm.CommandText = cmdText;
comm.CommandType = cmdType;
comm.Connection = conn; if(trans != null)
{
comm.Transaction = trans;
} try
{
int count = comm.ExecuteNonQuery();
return count;
}
catch(Exception ex)
{
ex.Message.ToString();
return -1;
}
} public static object ExecuteScalar(SqlConnection conn,CommandType cmdType,string cmdText,params SqlParameter[] cmdParams)
{
try
{
if(conn.State != ConnectionState.Open)
{
conn.Open();
}
SqlCommand comm = new SqlCommand();
PrepareCommand(comm,conn,null,cmdType,cmdText,cmdParams); string d =comm.ExecuteScalar().ToString();
return d;
}
catch(Exception es)
{
es.Message.ToString();
return -1;
}
finally
{
//conn.Close();
}
}
//给SqlCommand对象赋值
private static void PrepareCommand(SqlCommand comm,SqlConnection conn,SqlTransaction trans,CommandType cmdType,string cmdText,SqlParameter[] cmdParams)
{
if(conn.State != ConnectionState.Open)
{
conn.Open();
}
comm.CommandText = cmdText;
comm.CommandType = cmdType;
comm.Connection = conn; if(trans != null)
{
comm.Transaction = trans;
}
if(cmdParams != null)
{
foreach( SqlParameter op in cmdParams )
{
comm.Parameters.Add(op);
}
} }
/// <summary>
/// 从hs表里提sql语句的参数
/// </summary>
/// <param name="cacheKey">hs键值</param>
/// <returns></returns>
public static SqlParameter[] GetCachedParameters(string cacheKey)
{
SqlParameter[] cachedParms = (SqlParameter[])ht[cacheKey];
if (cachedParms == null)
return null;
SqlParameter[] clonedParms = new SqlParameter[cachedParms.Length];
for (int i = 0, j = cachedParms.Length; i < j; i++)
clonedParms[i] = (SqlParameter)((ICloneable)cachedParms[i]).Clone(); return clonedParms;
}
/// <summary>
/// 将sql语句的参数存入hs表里面
/// </summary>
/// <param name="cacheKey">hs键值</param>
/// <param name="param">参数</param>
public static void Cacheparameters(string cacheKey,params SqlParameter[] param)
{
ht[cacheKey] = param;
}
public static SqlDataReader ExeDataReader(SqlConnection conn,CommandType cmdType,string cmdText,params SqlParameter[] cmdParams)
{
try
{
SqlCommand comm = new SqlCommand();
PrepareCommand(comm,conn,null,cmdType,cmdText,cmdParams);
SqlDataReader odr = comm.ExecuteReader();
return odr;
}
catch(Exception ex)
{
ex.Message.ToString();
return null;
}
} public static DataTable GetDataTable(CommandType cmdType,string cmdText,params SqlParameter[] cmdParams)
{
try
{
SqlConnection conn = new SqlConnection(SQLHelper.ConnString);
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = cmdText;
cmd.CommandType = cmdType;
if(cmdParams != null)
{
foreach( SqlParameter op in cmdParams )
{
cmd.Parameters.Add(op);
}
}
DataSet ds = new DataSet();
SqlDataAdapter data = new SqlDataAdapter(cmd);
data.Fill(ds); return ds.Tables[0]; }
catch(Exception ex)
{
ex.Message.ToString();
return null;
}
} public static DataTable GetData(string tablename,CommandType cmdType,string cmdText)
{
try
{
SqlConnection conn = new SqlConnection(SQLHelper.ConnString);
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = cmdText;
cmd.CommandType = cmdType; DataTable dtb = new DataTable(tablename);
SqlDataAdapter data = new SqlDataAdapter(cmd);
data.Fill(dtb); return dtb; }
catch(Exception ex)
{
ex.Message.ToString();
return null;
}
} public static DataTable GetProcData(CommandType cmdType,string cmdText, SqlParameter[] cmdParams,SqlCommand cmd,SqlConnection conn)
{
try
{
cmd.Connection = conn;
cmd.CommandText = cmdText;
cmd.CommandType = cmdType;
if(cmdParams != null)
{
foreach( SqlParameter op in cmdParams )
{
cmd.Parameters.Add(op);
}
}
DataSet ds = new DataSet();
SqlDataAdapter data = new SqlDataAdapter(cmd);
data.Fill(ds); return ds.Tables[0]; }
catch(Exception ex)
{
ex.Message.ToString();
return null;
}
} }
}
using System.Data.SqlClient;
using System.Data;
using System.Windows.Forms;
namespace mpshortageanalyze
{
/// <summary>
/// conndatabase 的摘要说明。
/// </summary>
public class conndatabase
{
private SqlConnection Connstr;
private SqlDataAdapter ConnAdapter;
private SqlCommand Strcommand;
string mConnString;
public conndatabase(string cserver, string cdatabase, string cuid, string cpwd)
{
//
// TODO: 在此处添加构造函数逻辑
//
Connstr = new SqlConnection(); mConnString = "server=" + cserver + ";database=" + cdatabase + ";uid=" + cuid + ";pwd=" + cpwd + ""; Connstr.ConnectionString = mConnString;
ConnAdapter = new SqlDataAdapter();
Strcommand = new SqlCommand();
}
public void Open()
{
try
{
if (Connstr.State != ConnectionState.Open)
Connstr.Open();
}
catch (System.Exception e)
{
throw new System.Exception("不能打开" + e.Message);
}
} public bool ExecuteSql(string SQL)
{
try
{
if (Connstr.State != ConnectionState.Open)
{
Connstr.Open();
}
Strcommand = Connstr.CreateCommand();
Strcommand.CommandText = SQL;
int i = Strcommand.ExecuteNonQuery();
Connstr.Close();
return true;
}
catch (System.Exception)
{
if (Connstr.State != ConnectionState.Closed)
Connstr.Close();
return false;
}
} public SqlDataReader ExecuteReaderSqls(string SQL)
{
try
{ if (Connstr.State != ConnectionState.Open)
Connstr.Open();
SqlCommand sqlExectueCommand = new SqlCommand();
sqlExectueCommand.CommandText = SQL;
sqlExectueCommand.Connection = Connstr;
SqlDataReader myDataReader = sqlExectueCommand.ExecuteReader();
return myDataReader;
}
catch (System.Exception e)
{
if (Connstr.State != ConnectionState.Closed)
Connstr.Close();
throw new SystemException("不能打开" + e.Message);
}
}
public void close()
{
if (Connstr.State != ConnectionState.Closed)
{
Connstr.Close();
} }
}
}
string mConnString = "server=" + Form10.cserver.ToString() + ";database=" + Form10.cdatabase.ToString() + ";uid=" + Form10.cuser.ToString() + ";pwd=" + Form10.cpwd.ToString() + ""; SqlConnection conn = new SqlConnection(mConnString);
SqlDataAdapter da = new SqlDataAdapter(tsql, conn); DataSet ds = new DataSet(); da.SelectCommand.CommandText = (tsql); da.Fill(ds); this.dataGridView1.DataSource = ds.Tables[0];