C# SqlConnection.Dispose方法代码示例

 public void Method1(string connection,string command)
 {
     SqlConnection conn = null;
     try
     {
         conn = new SqlConnection(connection);
         //do something
         SqlCommand comm =null;
         try
         {
             comm = new SqlCommand(command);
             //do something
         }
         finally
         {
             if (comm != null)
                 comm.Dispose();
         }
     }
     finally
     {
         if (conn != null)
             conn.Dispose();
     }
 }

    public static MultipleCommands(string[] cmds, SqlConnection scon)
    {
        SqlCommand cmd = new SqlCommand();
        SqlCommand.Connection = scon;

        try
        {
            foreach (string c in cmds)
            {
                try
                {
                    scon.Open()
                    cmd.CommandText = c;
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "";
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    scon.Close();
                    scon.Dispose();
                }
            }
            return 1;
        }
        catch (Exception ex)
        {
            throw ex;
            return 0;
        }
        finally
        {
            cmd.Dispose()
        }
    }

 //InvalidOperationException from conenction.Dispose if that connection has prepared command cancelled during reading of data
 private static void CancelAndDisposePreparedCommand(string constr)
 {
     int expectedValue = 1;
     using (var connection = new SqlConnection(constr))
     {
         try
         {
             // Generate a query with a large number of results.
             using (var command = new SqlCommand("select @P from sysobjects a cross join sysobjects b cross join sysobjects c cross join sysobjects d cross join sysobjects e cross join sysobjects f"
                 , connection))
             {
                 command.Parameters.Add(new SqlParameter("@P", SqlDbType.Int) { Value = expectedValue });
                 connection.Open();
                 // Prepare the query.
                 // Currently this does nothing until command.ExecuteReader is called.
                 // Ideally this should call sp_prepare up-front.
                 command.Prepare();
                 using (var reader = command.ExecuteReader(CommandBehavior.SingleResult))
                 {
                     if (reader.Read())
                     {
                         int actualValue = reader.GetInt32(0);
                         Assert.True(actualValue == expectedValue, string.Format("Got incorrect value. Expected: {0}, Actual: {1}", expectedValue, actualValue));
                     }
                     // Abandon reading the results.
                     command.Cancel();
                 }
             }
         }
         finally
         {
             connection.Dispose(); // before the fix, InvalidOperationException happened here
         }
     }
 }

        private void updatedata()
        {
            //use filestream object to read the image.

            //read to the full length of image to a byte array.

            //add this byte as an oracle parameter and insert it into database.

            try
            {

                //proceed only when the image has a valid path

                if (imagename != "")
                {

                    FileStream fs;

                    fs = new FileStream(@imagename, FileMode.Open, FileAccess.Read);

                    //a byte array to read the image

                    byte[] picbyte = new byte[fs.Length];

                    fs.Read(picbyte, 0, System.Convert.ToInt32(fs.Length));

                    fs.Close();

                    //open the database using odp.net and insert the data

                    string connstr = @"Data Source=.;Initial Catalog=TestImage;Persist Security Info=True;User ID=sa";

                    SqlConnection conn = new SqlConnection(connstr);

                    conn.Open();

                    string query;

                    query = "insert into test_table(id_image,pic) values(" +
                    textBox1.Text + "," + " @pic)";

                    SqlParameter picparameter = new SqlParameter();

                    picparameter.SqlDbType = SqlDbType.Image;

                    picparameter.ParameterName = "pic";

                    picparameter.Value = picbyte;

                    SqlCommand cmd = new SqlCommand(query, conn);

                    cmd.Parameters.Add(picparameter);

                    cmd.ExecuteNonQuery();

                    MessageBox.Show("Image Added");

                    cmd.Dispose();

                    conn.Close();

                    conn.Dispose();

                    Connection();

                }

            }

            catch (Exception ex)
            {

                MessageBox.Show(ex.Message);

            }
        }