将SqlConnection赋给SqlCommand.Connection后,引用关系丢失了?

Question

using (SqlConnection conn = new SqlConnection())
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    PrepareCommand(cmd, new SqlConnection(Utils.Context.ConnectionString), null,CommandType.Text, sql, null);
                    //过了上面一句,conn就和cmd.Connection就不相等了,变成两个了
                    //而在上面那个函数内部,两者是一个东西
                    using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        da.Fill(ds, "ds");
                        cmd.Parameters.Clear();
                        conn.Close();
                        cmd.Connection.Close();//没有这一句就不能关闭连接
                        return ds.Tables[0];
                    }
                }
            }private static void PrepareCommand(DbCommand cmd, DbConnection conn, DbTransaction trans, CommandType cmdType,
            string cmdText, DbParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = cmdType;            if (cmdParms != null)
            {
                foreach (SqlParameter parm in cmdParms)
                {
                    parm.ParameterName = parm.ParameterName.Replace("?", "@").Replace(":", "@");
                    cmd.Parameters.Add(parm);
                }
            }
        }

Answer 1

给你一个数据库访问类
using System;
using System.Collections.Generic;
using System.Text;using System.Configuration;
using System.Data;
using System.Data.SqlClient;namespace Wicresoft.Common.Utility
{
    public static class SqlHelper
    {
        public static readonly string connectionString = ConfigurationManager.ConnectionStrings["connectionString"].ToString();        public static DataSet GetDataFormDataSet(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(cmdText, conn);
                command.CommandType = cmdType;
                if (commandParameters != null)
                {
                    foreach (SqlParameter parm in commandParameters)
                        command.Parameters.Add(parm);
                }
                SqlDataAdapter adapter = new SqlDataAdapter(command);
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                return ds;
            }
        }        public static int ExecuteNonQuery(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;
            }
        }        public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();
            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }        public static SqlDataReader ExecuteReader(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();
            SqlConnection conn = new SqlConnection(connectionString);            try
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);                cmd.Parameters.Clear();
                return rdr;
            }
            catch
            {
                conn.Close();
                throw;
            }
        }        public static SqlDataReader ExecuteReader(SqlConnection conn, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();            try
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                SqlDataReader rdr = cmd.ExecuteReader();                cmd.Parameters.Clear();
                return rdr;
            }
            catch
            {
                conn.Close();
                throw;
            }
        }        public static object ExecuteScalar(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
                object val = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return val;
            }
        }        public static object ExecuteScalar(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
            object val = cmd.ExecuteScalar();
            cmd.Parameters.Clear();
            return val;
        }        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
        {            if (conn.State != ConnectionState.Open)
                conn.Open();            cmd.Connection = conn;
            cmd.CommandText = cmdText;            if (trans != null)
                cmd.Transaction = trans;            cmd.CommandType = cmdType;            if (cmdParms != null)
            {
                foreach (SqlParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }
    }
}

Answer 2

随便下个sqlhelper 这些都不用自己写的

Answer 3

using (SqlConnection conn = new SqlConnection())
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    PrepareCommand(cmd, new SqlConnection(Utils.Context.ConnectionString), null,CommandType.Text, sql, null);
                    //过了上面一句,conn就和cmd.Connection就不相等了,变成两个了
                    //而在上面那个函数内部,两者是一个东西
                    using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        da.Fill(ds, "ds");
                        cmd.Parameters.Clear();
                        conn.Close();
                        cmd.Connection.Close();//没有这一句就不能关闭连接
                        return ds.Tables[0];
                    }
                }
            }
你实例化了两个,当然会用两个了,改为以下
    using (SqlConnection conn = new SqlConnection(Utils.Context.ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    PrepareCommand(cmd, conn, null,CommandType.Text, sql, null);
                    //过了上面一句,conn就和cmd.Connection就不相等了,变成两个了
                    //而在上面那个函数内部,两者是一个东西
                    using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        da.Fill(ds, "ds");
                        cmd.Parameters.Clear();
                        conn.Close();
                        return ds.Tables[0];
                    }
                }
            }

Answer 4

我的C#数据操作类
#region 数据库初始化
public string GetDefaultConnStr()
{
string ConnStr = ConfigurationSettings.AppSettings["ConnStr"];
return ConnStr;
}
public  OleDbConnection GetDefaultConnection()
{
Conn=new OleDbConnection(GetDefaultConnStr());
Conn.Open();
return Conn;
}
public OleDbConnection GetDefaultConnection(string Connstr)
{
Conn=new OleDbConnection(Connstr);
Conn.Open();
return Conn;
}
public OleDbCommand GetCmd(string SQL, OleDbConnection Conn) 
{
Cmd = new OleDbCommand(SQL, Conn); 
return Cmd; 
} 
public OleDbDataAdapter GetDataAdapter(string SQL,OleDbConnection Conn)
{
Da = new OleDbDataAdapter(SQL, Conn); 
return Da; 
}
public void GetDataSet(string SQL ,DataSet Ds,string TableName) 
{ 
Conn=this.GetDefaultConnection();
Da=GetDataAdapter(SQL,Conn);
try 
{
Da.Fill(Ds,TableName);
}
catch(Exception Err) 
{ 
throw Err; 
} 
Dispose(Conn);
} 
public void GetDataSet(string SQL ,DataSet Ds,string TableName,string Connstr) 
{ 
Conn=GetDefaultConnection(Connstr);
Da=GetDataAdapter(SQL,Conn);
try 
{
Da.Fill(Ds,TableName);
}
catch(Exception Err) 
{ 
throw Err; 
} 
Dispose(Conn);

} 
public void Dispose(OleDbConnection Conn) 
{ 
if(Conn!=null) 
{ 
Conn.Close(); 
Conn.Dispose(); 
} 
GC.Collect(); 
}
public void RunProc(string SQL) 
{ 
Conn=this.GetDefaultConnection();
Cmd=GetCmd(SQL,Conn);
try 
{ 
Cmd.ExecuteNonQuery(); 
} 
catch 
{ 
throw new Exception(SQL); 
} 
Dispose(Conn); 
return; 
} // public OleDbDataReader RunProcGetReader(string SQL) 
// { 
// Conn=this.GetDefaultConnection();
// Cmd=GetCmd(SQL,Conn);
// OleDbDataReader Dr; 
// try 
// { 
// Dr = Cmd.ExecuteReader(CommandBehavior.Default); 
// } 
// catch 
// { 
// throw new Exception(SQL); 
// } 
// return Dr; 
// } 
#region 更新数据
public void updataToDataSet(string SQL,DataSet Ds,string table)
{
Conn=this.GetDefaultConnection();
Da=this.GetDataAdapter(SQL,Conn);
OleDbCommandBuilder bldr = new OleDbCommandBuilder(Da);
Da.UpdateCommand = bldr.GetUpdateCommand();
Da.DeleteCommand = bldr.GetDeleteCommand();
Da.InsertCommand = bldr.GetInsertCommand();
Da.Update(Ds.GetChanges(),table);
Da.Dispose();
Dispose(Conn);
}
#endregion 更新数据