C# System.Data.SqlClient.SqlConnection.Dispose方法代码示例

        //Metodo de Iserción de Datos
        public bool Insertar(Comun.Vars p)
        {
            bool Resultado = false;
               //Realizar metodo de conexión
            System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection();
            //Activa o Desactivar String de Conexion
            cn.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=C:\\Wayvas\\Task\\Presentacion\\App_Data\\Task.mdf;Integrated Security=True;User Instance=True";
            //cn.ConnectionString = "Data Source=.\\SQLEXPRESS;Initial Catalog=Task;Integrated Security=True";
            cn.Open();//Iniciamos la Conexion

            System.Data.SqlClient.SqlCommand cm =new System.Data.SqlClient.SqlCommand();
            cm.Connection=cn;
            cm.CommandType=System.Data.CommandType.Text;
            cm.CommandText = "Insert into Task(TaskName,StartDate,DueDate,CompDate,TaskComs) Values(@P_TaskName,@P_StartDate,@P_DueDate,@P_CompDate,@P_TaskComs)";

            cm.Parameters.AddWithValue("@P_TaskName", p.TaskName);
            cm.Parameters.AddWithValue("@P_StartDate", p.StartDate);
            cm.Parameters.AddWithValue("@P_DueDate", p.DueDate);
            cm.Parameters.AddWithValue("@P_CompDate", p.CompDate);
            cm.Parameters.AddWithValue("@P_TaskComs", p.TaskComs);

            int ra = cm.ExecuteNonQuery();
                   if (ra==1)
                   { Resultado=true;}
            cn.Close();
            cn.Dispose();
            return Resultado;
        }

        private void btnSave_Click(object sender, EventArgs e)
        {
            string connectionstring = "Data Source=devrystudentsp10.db.6077598.hostedresource.com;Persist Security Info=True;User ID=DeVryStudentSP10;Password=OidLZqBv4";
            System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
            conn.ConnectionString = connectionstring;
            conn.Open();
            //MessageBox.Show(conn.State.ToString());
            System.Data.SqlClient.SqlCommand comm = new System.Data.SqlClient.SqlCommand();
            comm.Connection = conn;
            comm.CommandText = "insert into Huber_Logon (email, lastname, firstname, password, userid)";
            comm.CommandText += " values (@email, @lastname, @firstname, @password, @userid)";

            comm.Parameters.AddWithValue("@email", txtemail.Text);
            comm.Parameters.AddWithValue("@lastname", txtLname.Text);
            comm.Parameters.AddWithValue("@firstname", txtfname.Text);
            comm.Parameters.AddWithValue("@password", txtpassword.Text);
            comm.Parameters.AddWithValue("@userid", txtuserid.Text);
            if (!dbLayer.isuseralreadyinthedbornot(txtuserid.Text, connectionstring))
            {
                comm.ExecuteNonQuery();
            }
            else
            {
                MessageBox.Show("You Must enter another userid, that one is in use!");
            }

            conn.Close();
            conn.Dispose();
        }

        public BulkLoad(string datasheetPath, string worksheetName, string tempTableName, string connectionString, string excelQuery)
        {
            ExcelQuery = excelQuery;
            string excelConnectionString = ExcelConnectionString(datasheetPath);
            _dbConn = new System.Data.SqlClient.SqlConnection(connectionString);

            //VerifyWorksheetNames(excelConnection,
            OleCopyToTempTable(tempTableName, excelConnectionString, worksheetName);
            if (_dbConn.State != System.Data.ConnectionState.Closed)
            {
                _dbConn.Close();
                _dbConn.Dispose();
            }
        }

		void ExecuteSQL(string SQL, string connectionString)
		{
			System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connectionString);
			System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(SQL, conn);
			try
			{
				conn.Open();
				cmd.ExecuteNonQuery();
			}
			finally
			{
				conn.Close();
				conn.Dispose();
			}
		}

        public DataTable SQLReaderDataTable(string SQLQuery, string ConnectionStringName)
        {
            // SQL Connection String
                System.Configuration.ConnectionStringSettings SQLConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings[ConnectionStringName];

                // SQL Connection via Connection String
                var SQLConnection = new System.Data.SqlClient.SqlConnection();
                SQLConnection.ConnectionString = SQLConnectionString.ToString();
                try
                {
                    // Open SQL Connection
                    SQLConnection.Open();
                }
                catch (Exception e)
                {
                    if (SQLConnection.State == ConnectionState.Open){
                        SQLConnection.Close();
                    }
                    return null;
                }
                // SQL Command
                var SQLCommand = new System.Data.SqlClient.SqlCommand();
                SQLCommand.Connection = SQLConnection;

                // SQL Command Text
                string SQLCommandText = SQLQuery;
                SQLCommand.CommandText = SQLCommandText;

                // SQL Reader
                var SQLReader = SQLCommand.ExecuteReader();

                // Data Table
                var SQLDataTable = new System.Data.DataTable();

                // Load SQL Data into Data Table
                SQLDataTable.Load(SQLReader);

                // Close SQL Connection
                SQLConnection.Dispose();

                // Return Data
                return SQLDataTable;
        }

        public override void CreateSyncRepository()
        {
            if (_ConnectionString == null) throw new InvalidOperationException();

            IDbConnection conn = new System.Data.SqlClient.SqlConnection(_ConnectionString);
            conn.Open();

            IDbTransaction t = conn.BeginTransaction();

            IDbCommand com = conn.CreateCommand();
            com.Transaction = t;

            StringBuilder createLogin = new StringBuilder();
            createLogin.Append("CREATE LOGIN ").Append(_AccountUsername).Append(" WITH PASSWORD='").Append(_AccountPassword).Append("'");
            com.CommandText = createLogin.ToString();
            com.ExecuteNonQuery();

            StringBuilder createUser = new StringBuilder();
            createUser.Append("CREATE USER ").Append(_AccountUsername).Append(" WITH DEFAULT_SCHEMA=").Append(_AccountUsername);
            com.CommandText = createUser.ToString();
            com.ExecuteNonQuery();

            StringBuilder createSchema = new StringBuilder();
            createSchema.Append("CREATE SCHEMA ").Append(_AccountUsername).Append(" AUTHORIZATION ").Append(_AccountUsername);
            com.CommandText = createSchema.ToString();
            com.ExecuteNonQuery();

            StringBuilder createSyncItem = new StringBuilder();
            createSyncItem.Append("CREATE TABLE ").Append(_AccountUsername).Append(".SyncItem").
                Append("(SyncID INT PRIMARY KEY IDENTITY, SyncFK INT, ClassID nvarchar(255), HashCode nvarchar(32), ").
                Append("SyncGuid UNIQUEIDENTIFIER, RowGuid UNIQUEIDENTIFIER)");
            com.CommandText = createSyncItem.ToString();
            com.ExecuteNonQuery();

            StringBuilder createFieldState = new StringBuilder();
            createFieldState.Append("CREATE TABLE ").Append(_AccountUsername).Append(".FieldState").
                Append("(SyncFK INT, FieldName nvarchar(255), HashCode nvarchar(32), ").
                Append("RowGuid UNIQUEIDENTIFIER, PRIMARY KEY (SyncFK, FieldName))");
            com.CommandText = createFieldState.ToString();
            com.ExecuteNonQuery();

            t.Commit();

            conn.Close();
            conn.Dispose();
        }

 //TODO not sure where you are trying to connect on this one. :)
 /* neither am I comes out of the example in 'Professional Android Programming with Mono for Android and .NET#3aC#'
  * I don't really understand this bit.
  */
 void DataBaseButton_Click(object sender, EventArgs e)
 {
     System.Data.SqlClient.SqlConnection sqlCn = new System.Data.SqlClient.SqlConnection();
     System.Data.SqlClient.SqlCommand sqlCm = new System.Data.SqlClient.SqlCommand();
     System.Data.SqlClient.SqlDataAdapter sqlDa = new System.Data.SqlClient.SqlDataAdapter();
     DataTable dt = new DataTable();
     string strSql = "select * from Session";
     string strCn = "Server=mobiledev.scalabledevelopment.com;Database=AnDevConTest;User ID=AnDevCon;Password=AnDevConPWD;Network Library=DBMSSOCN";
     sqlCn.ConnectionString = strCn;
     sqlCm.CommandText = strSql;
     sqlCm.CommandType = CommandType.Text;
     sqlCm.Connection = sqlCn;
     sqlDa.SelectCommand = sqlCm;
     try
     {
         sqlDa.Fill(dt);
         tv.Text = "Records returned: " + dt.Rows.Count.ToString();
     }
     catch (System.Exception sysExc)
     {
         Console.WriteLine("Exc: " + sysExc.Message);
         tv.Text = "Exc: " + sysExc.Message;
     }
     finally
     {
         if (sqlCn.State != ConnectionState.Closed)
         {
             sqlCn.Close();
         }
         sqlCn.Dispose();
         sqlCm.Dispose();
         sqlDa.Dispose();
         sqlCn = null;
         sqlCm = null;
         sqlDa = null;
     }
 }

 /*
  * En el procedimiento Comando, se buscará primero si ya existe el comando en dicha Hashtable para retornarla
  * (convertida en el tipo correcto). Caso contrario, se procederá a la creación del mismo,
  * y su agregado en el repositorio. Dado que cabe la posibilidad de que ya estemos dentro de una transacción,
  * es necesario abrir una segunda conexión a la base de datos, para obtener la definición de los parámetros
  * del procedimiento Almacenado (caso contrario da error, por intentar leer sin tener asignado el
  * objeto Transaction correspondiente). Además, el comando, obtenido por cualquiera de los mecanismos
  * debe recibir la conexión y la transacción correspondientes (si no hay Transacción, la variable es null,
  * y ese es el valor que se le pasa al objeto Command)
  */
 protected override System.Data.IDbCommand Comando(string procedimientoAlmacenado)
 {
     System.Data.SqlClient.SqlCommand com;
     if (ColComandos.Contains(procedimientoAlmacenado))
         com = (System.Data.SqlClient.SqlCommand)ColComandos[procedimientoAlmacenado];
     else
     {
         var con2 = new System.Data.SqlClient.SqlConnection(CadenaConexion);
         con2.Open();
         com = new System.Data.SqlClient.SqlCommand(procedimientoAlmacenado, con2) { CommandType = System.Data.CommandType.StoredProcedure };
         System.Data.SqlClient.SqlCommandBuilder.DeriveParameters(com);
         con2.Close();
         con2.Dispose();
         ColComandos.Add(procedimientoAlmacenado, com);
     }//end else
     com.Connection = (System.Data.SqlClient.SqlConnection)Conexion;
     com.Transaction = (System.Data.SqlClient.SqlTransaction)MTransaccion;
     return com;
 }

 protected override System.Data.IDbCommand getCommand(string storedProcedure)
 {
     System.Data.SqlClient.SqlCommand mCommand;
     if(CommandsCollection.Contains(storedProcedure))
     {
         mCommand = (System.Data.SqlClient.SqlCommand) CommandsCollection[storedProcedure];
     }
     else
     {
         System.Data.SqlClient.SqlConnection Conn = new System.Data.SqlClient.SqlConnection(this.ConnectionString);
         Conn.Open();
         mCommand = new System.Data.SqlClient.SqlCommand(storedProcedure,Conn);
         mCommand.CommandType = System.Data.CommandType.StoredProcedure;
         System.Data.SqlClient.SqlCommandBuilder.DeriveParameters(mCommand);
         Conn.Close();
         Conn.Dispose();
         CommandsCollection.Add(storedProcedure, mCommand);
     }
     mCommand.Connection = (System.Data.SqlClient.SqlConnection) this.Connection;
     return (System.Data.IDbCommand) mCommand;
 }

        private static void LogToDB(string message, bool messageE, bool warning, bool error)
        {
            using (System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.AppSettings["ConnectionString"].ToString()))
            {
                try
                {
                    if (connection.State != System.Data.ConnectionState.Open)
                    {
                        connection.Close();
                        connection.Open();
                    }

                    int t = -1;
                    if (messageE && logMessage)
                    {
                        t = 1;
                    }
                    if (error && logError)
                    {
                        t = 2;
                    }
                    if (warning && logWarning)
                    {
                        t = 3;
                    }
                    string sql = String.Format("Insert into Log Values('{0}', {1})", message, t.ToString());
                    System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand(sql, connection);
                    command.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Log to database failed. An error occurs: " + ex.Message);
                }
                finally
                {
                    if (connection.State != System.Data.ConnectionState.Closed)
                    {
                        connection.Close();
                        connection.Dispose();
                    }
                }
            }
        }