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

//.........这里部分代码省略.........
              //  Use the "Sys.Objects" Table to determine the last update date of the datasets.
              sqlStatement = "SELECT Modify_Date "
                       + "FROM Sys.Objects "
                       + "WHERE [Name] = '" + FeatureClassName + "'";

              //  Set the Udpate Date Field Name Variable to "Modify_Date".
              updateDateFieldName = "Modify_Date";

            }
            else
            {
              //  Use the SysObjects Table to determine the last update date of the datasets.
              sqlStatement = "SELECT RefDate "
                       + "FROM SysObjects "
                       + "WHERE [Name] = '" + FeatureClassName + "'";

              //  Set the Udpate Date Field Name Variable to "RefDate".
              updateDateFieldName = "RefDate";

            }

            //  Build the SQL Command Object that will be used to retrieve the Last Update Date Info from the Source SQL Server Database.
            updateDateCommand = new System.Data.SqlClient.SqlCommand();
            updateDateCommand.CommandText = sqlStatement;
            updateDateCommand.Connection = updateDateConnection;
            updateDateCommand.CommandType = System.Data.CommandType.Text;
            updateDateCommand.CommandTimeout = 20;

            //  Use the Command Object to open a SQL Data Reader that will house the Last Update Date Info from the Source SQL Server Database.
            updateDateReader = updateDateCommand.ExecuteReader();

            //  If some Last Update Date Info was retrieved from the database, return it to the calling routine.  Otherwise, return
            //  January 1st, 1900 as a default date to let the calling routine know that the date could not be determined.
            if (updateDateReader.HasRows)
            {
              //  Retrieve the Last Update Date from the SQL Data Reader.
              updateDateReader.Read();
              System.DateTime updateDate = (System.DateTime)updateDateReader[updateDateFieldName];

              //  Return the Last Update Date of the Feature class to the calling routine..
              return updateDate;

            }
            else
            {
              //  Return January 1st, 1900 as the last update date to let the calling routine know that the last update date could not
              //  be determined.
              return new System.DateTime(1900, 1, 1);

            }

              }
              catch (System.Exception caught)
              {
            //  Determine the Line Number from which the exception was thrown.
            System.Diagnostics.StackTrace stackTrace = new System.Diagnostics.StackTrace(caught, true);
            System.Diagnostics.StackFrame stackFrame = stackTrace.GetFrame(stackTrace.FrameCount - 1);
            int lineNumber = stackFrame.GetFileLineNumber();

            //  Let the user know that this process failed.
            if (ErrorMessage != null)
            {
              ErrorMessage("The FeatureClassUtilities.GetEntGeoDBFeatureClassLastUpdate() Method failed with error message - " + caught.Message + " (Line:  " + lineNumber.ToString() + ")!");
            }

            //  Return a January 1st, 1900 to the calling routine to indicate that this process failed.
            return new DateTime(1900, 1, 1);

              }
              finally
              {
            //  If the Update Date SQL Data Reader Object was instantiated, close it.
            if (updateDateReader != null)
            {
              if (!updateDateReader.IsClosed)
              {
            updateDateReader.Close();
              }
              updateDateReader.Dispose();
              updateDateReader = null;
            }
            //  If the Update Date SQL Data Command Object was instantiated, close it.
            if (updateDateCommand != null)
            {
              updateDateCommand.Dispose();
              updateDateCommand = null;
            }
            //  If the SQL Client Rebuild Spatial Index Connetion Object was instantiated, close it.
            if (updateDateConnection != null)
            {
              if (updateDateConnection.State == System.Data.ConnectionState.Open)
              {
            updateDateConnection.Close();
              }
              updateDateConnection.Dispose();
              updateDateConnection = null;
            }

              }
        }

        public void EXQuery(string query)
        {
            if (connectionString != "")
            {

                if (databaseSchemaReader is SQLServerDatabaseExtractor)
                {
                    System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connectionString);
                    System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand(query);
                    command.CommandType = CommandType.Text;
                    command.Connection = conn;
                    conn.Open();
                    command.ExecuteNonQuery();
                    conn.Close();
                    command.Dispose();
                }
            }
        }

//.........这里部分代码省略.........
            rebuildIndexGridLevel1Parameter.ParameterName = "@gridLevel1";
            rebuildIndexGridLevel1Parameter.Direction = System.Data.ParameterDirection.Input;
            rebuildIndexGridLevel1Parameter.SqlDbType = System.Data.SqlDbType.VarChar;
            rebuildIndexGridLevel1Parameter.Value = gridLevel1Value;
            rebuildIndexSQLCommandObject.Parameters.Add(rebuildIndexGridLevel1Parameter);

            //  Grid Level 2 Parameter.
            rebuildIndexGridLevel2Parameter = new System.Data.SqlClient.SqlParameter();
            rebuildIndexGridLevel2Parameter.ParameterName = "@gridLevel2";
            rebuildIndexGridLevel2Parameter.Direction = System.Data.ParameterDirection.Input;
            rebuildIndexGridLevel2Parameter.SqlDbType = System.Data.SqlDbType.VarChar;
            rebuildIndexGridLevel2Parameter.Value = gridLevel2Value;
            rebuildIndexSQLCommandObject.Parameters.Add(rebuildIndexGridLevel2Parameter);

            //  Grid Level 3 Parameter.
            rebuildIndexGridLevel3Parameter = new System.Data.SqlClient.SqlParameter();
            rebuildIndexGridLevel3Parameter.ParameterName = "@gridLevel3";
            rebuildIndexGridLevel3Parameter.Direction = System.Data.ParameterDirection.Input;
            rebuildIndexGridLevel3Parameter.SqlDbType = System.Data.SqlDbType.VarChar;
            rebuildIndexGridLevel3Parameter.Value = gridLevel3Value;
            rebuildIndexSQLCommandObject.Parameters.Add(rebuildIndexGridLevel3Parameter);

            //  Grid Level 4 Parameter.
            rebuildIndexGridLevel4Parameter = new System.Data.SqlClient.SqlParameter();
            rebuildIndexGridLevel4Parameter.ParameterName = "@gridLevel4";
            rebuildIndexGridLevel4Parameter.Direction = System.Data.ParameterDirection.Input;
            rebuildIndexGridLevel4Parameter.SqlDbType = System.Data.SqlDbType.VarChar;
            rebuildIndexGridLevel4Parameter.Value = gridLevel4Value;
            rebuildIndexSQLCommandObject.Parameters.Add(rebuildIndexGridLevel4Parameter);

            //  Attempt to Rebuild the Spatial Index on the specified Feature Class.
            rebuildIndexSQLCommandObject.ExecuteNonQuery();

            //  If the process made it to here, it was successful so return TRUE to the calling method.
            return true;

              }
              catch (System.Exception caught)
              {
            //  Determine the Line Number from which the exception was thrown.
            System.Diagnostics.StackTrace stackTrace = new System.Diagnostics.StackTrace(caught, true);
            System.Diagnostics.StackFrame stackFrame = stackTrace.GetFrame(stackTrace.FrameCount - 1);
            int lineNumber = stackFrame.GetFileLineNumber();

            //  Let the User know that this process failed.
            if (ErrorMessage != null)
            {
              ErrorMessage("The MaintTools.FeatureClassUtilities.CreateSQLSpatialIndexOnEntGeoDBFeatureClass() Method failed with error message:  " + caught.Message + " (Line:  " + lineNumber.ToString() + ")!");
            }

            //  Return FALSE to the calling routine to indicate that this process failed.
            return false;

              }
              finally
              {
            //  If the SQL Client Grid Level 4 SQL Parameter Object was instantiated, close it.
            if (rebuildIndexGridLevel4Parameter != null)
            {
              rebuildIndexGridLevel4Parameter = null;
            }
            //  If the SQL Client Grid Level 3 SQL Parameter Object was instantiated, close it.
            if (rebuildIndexGridLevel3Parameter != null)
            {
              rebuildIndexGridLevel3Parameter = null;
            }
            //  If the SQL Client Grid Level 2 SQL Parameter Object was instantiated, close it.
            if (rebuildIndexGridLevel2Parameter != null)
            {
              rebuildIndexGridLevel2Parameter = null;
            }
            //  If the SQL Client Grid Level 1 SQL Parameter Object was instantiated, close it.
            if (rebuildIndexGridLevel1Parameter != null)
            {
              rebuildIndexGridLevel1Parameter = null;
            }
            //  If the SQL Client Feature Class Name SQL Parameter Object was instantiated, close it.
            if (rebuildIndexFClassSQLParameter != null)
            {
              rebuildIndexFClassSQLParameter = null;
            }
            //  If the SQL Client Rebuild Spatial Index Command Object was instantiated, close it.
            if (rebuildIndexSQLCommandObject != null)
            {
              rebuildIndexSQLCommandObject.Dispose();
              rebuildIndexSQLCommandObject = null;
            }
            //  If the SQL Client Rebuild Spatial Index Connetion Object was instantiated, close it.
            if (rebuildIndexSQLConnectionObject != null)
            {
              if (rebuildIndexSQLConnectionObject.State == System.Data.ConnectionState.Open)
              {
            rebuildIndexSQLConnectionObject.Close();
              }
              rebuildIndexSQLConnectionObject.Dispose();
              rebuildIndexSQLConnectionObject = null;
            }

              }
        }

        private void getMenu()
        {
            System.Data.SqlClient.SqlCommand _lcJetCmd = null;
            //System.Data.SqlClient.SqlDataReader _lcJetDReader = null;
            System.Data.DataSet _lcDataSet = null;
            System.Data.SqlClient.SqlDataAdapter _lcDataAdatapter = null;
            System.Windows.Forms.MenuItem _lcMenuItem = new MenuItem();
            byte menukey = 0;
            byte intMenuKey = 0;
            bool _lcIsPrimeraVes = true;
            try
            {
                if (Program._glbControlQuality2012Config.State == ConnectionState.Closed) { Program._glbControlQuality2012Config.Open(); }

                _lcJetCmd = new System.Data.SqlClient.SqlCommand();
                _lcDataAdatapter = new System.Data.SqlClient.SqlDataAdapter();
                _lcDataSet = new DataSet();
                _lcJetCmd.Connection = Program._glbControlQuality2012Config;
                _lcJetCmd.CommandType = CommandType.Text;
                _lcJetCmd.CommandText = "SELECT A.*,B.* FROM tblMenu A INNER JOIN tblMenuItems B ON A.menuKey = B.MenuKey order by A.menuKey;";
                _lcDataAdatapter.SelectCommand = _lcJetCmd;
                _lcDataAdatapter.Fill(_lcDataSet);

                foreach (System.Data.DataRow _lcJetDReader in _lcDataSet.Tables[0].Rows)
                {
                    intMenuKey = Convert.ToByte(_lcJetDReader["menukey"].ToString());
                    if (menukey != Convert.ToByte(intMenuKey) || _lcIsPrimeraVes)
                    {
                        writeMenu(
                            _lcJetDReader["Label"].ToString(),
                            Convert.ToByte(_lcJetDReader["menukey"].ToString()),
                            _lcJetDReader["Label1"].ToString(),
                            ref  _lcMenuItem);
                        _lcIsPrimeraVes = false;
                    }

                    writeMenuItems(
                           _lcJetDReader["Label1"].ToString(),
                           Convert.ToByte(_lcJetDReader["menukey"].ToString()),
                           ref _lcMenuItem);

                    menukey = Convert.ToByte(_lcJetDReader["menukey"].ToString());
                }//end For

                _lcDataAdatapter.Dispose();
                _lcJetCmd.Dispose();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString() + "---Hay Problema con el archivo de configuracion de la aplicacion---");
            }
            finally
            {
                if (Program._glbControlQuality2012Config.State == ConnectionState.Open)
                {
                    Program._glbControlQuality2012Config.Close();
                }
                _lcJetCmd = null;
                _lcDataAdatapter = null;
            }
        }

        /// <summary>
        /// Run a transact SQL query against the database.
        /// </summary>
        /// <param name="sqlQuery">String with the SQL query (Insert, Update, Delete, etc).</param>
        /// <returns>The number of rows affected or -1 if an exception occurs.</returns>
        public int ExecuteQuery(string sqlQuery)
        {
            // Do not proceed if the database is not connected.
            if (this.IsConnected == false)
            {
                this.hasException = true;
                this.lastException = new System.Exception("You cannot query a database until you connect to it (ExecuteQuery(string). Connect first.");
                if (this.ThrowExceptions == true) throw this.lastException;
                return -1;
            }

            // Clear past exceptions
            this.hasException = false;

            /*
             * Switch to the appropriate database client and execute the query
             *
             * Set the default output to -1 (which means that there was an error)
             * Create a Command object
             * Set the Command to use the current database's object
             * Set the Command's timeout value (if exceeded, an exception will occur)
             * Execute the SQL query, populating the output with the number of rows affected
             * Dispose of the Command object
             */

            int output = -1;

            switch (this.DataClient)
            {
                case DatabaseClient.OleClient:

                    try
                    {
                        System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand();
                        cmd.Connection = this.oleConn;
                        cmd.CommandText = sqlQuery;
                        cmd.CommandTimeout = this.CommandTimeout;
                        output = cmd.ExecuteNonQuery();
                        cmd.Dispose();
                    }
                    catch (Exception exception)
                    {
                        hasException = true;
                        lastException = exception;

                        if (ThrowExceptions == true)
                            throw lastException;
                    }

                    break;

                case DatabaseClient.SqlClient:

                    try
                    {
                        System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
                        cmd.Connection = this.sqlConn;
                        cmd.CommandText = sqlQuery;
                        cmd.CommandTimeout = this.CommandTimeout;
                        output = cmd.ExecuteNonQuery();
                        cmd.Dispose();
                    }
                    catch (Exception exception)
                    {
                        hasException = true;
                        lastException = exception;

                        if (ThrowExceptions == true) throw lastException;
                    }

                    break;

                default:
                    this.isConnected = false;
                    this.hasException = true;
                    this.lastException = new SystemException("The database client type entered is invalid (DataClient=" + this.DataClient.ToString() + ").");

                    if (this.ThrowExceptions == true)
                        throw this.lastException;

                    break;
            }

            return output;
        }

 //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;
     }
 }

        private void CreateCommand(string sql, ArrayList parameters)
        {
            // Open Connection
            if (mSqlConnection.State == ConnectionState.Closed)
            {
                mSqlConnection.Open();
                if (OnContext is ONServiceContext) // Services
                {
                    ONSQLCommand lSqlCommand = new ONSQLCommand("SET TRANSACTION ISOLATION LEVEL READ COMMITTED", mSqlConnection);
                    lSqlCommand.ExecuteNonQuery();
                    lSqlCommand.Dispose();
                    lSqlCommand = null;
                }
                else // Queries
                {
                    ONSQLCommand lSqlCommand = new ONSQLCommand("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED", mSqlConnection);
                    lSqlCommand.ExecuteNonQuery();
                    lSqlCommand.Dispose();
                    lSqlCommand = null;
                }
            }

            // Create Command
            mSqlCommand = new ONSQLCommand(sql, mSqlConnection);

            // Parameters
            if (parameters != null)
            {
                string lName = "";
                int lIndex = 0;
                foreach(ONSimpleType lParameterItem in parameters)
                {
                    lName = "@" + lIndex;
                    if (lParameterItem.Value == null)
                    {
                        ONSQLParameter lParameter = mSqlCommand.Parameters.Add(lName, lParameterItem.SQLType);
                        lParameter.IsNullable = true;
                        lParameter.Value = DBNull.Value;
                    }
                    else
                    {
                        (mSqlCommand as ONSQLCommand).Parameters.Add(lName, lParameterItem.SQLType).Value = lParameterItem.Value;
                    }
                    lIndex++;
                }
            }
        }

 /// <summary>
 /// ִ��SQL��䲢����DateTable
 /// </summary>
 /// <param name="conn">���Ӷ���</param>
 /// <param name="sqlString">SQL���</param>
 /// <returns>System.Data.DataTable</returns>
 public System.Data.DataTable execSqlReturnDataTable(System.Data.SqlClient.SqlConnection  conn , 
     string sqlString)
 {
     try
     {
         if (conn.State == System.Data.ConnectionState.Closed )
         {
             conn.Open();
         }
         System.Data.SqlClient.SqlCommand cmd     =  new System.Data.SqlClient.SqlCommand(sqlString , conn);
         cmd.CommandTimeout = 36000 ;
         System.Data.SqlClient.SqlDataAdapter dap = new System.Data.SqlClient.SqlDataAdapter(cmd);
         System.Data.DataTable dt = new System.Data.DataTable();
         dap.Fill(dt);
         cmd.Dispose();
         conn.Close();
         dap.Dispose();
         conn.Close();
         return dt;
     }
     catch//(Exception exp)
     {
         //System.Windows.Forms.MessageBox.Show(exp.Message);
         return new System.Data.DataTable();
     }
 }

 /// <summary>
 /// ִ��SQL��䲢����DateTable
 /// </summary>
 /// <param name="conn">���Ӷ���</param>
 /// <param name="sqlString">SQL���</param>
 public Boolean execSql(System.Data.SqlClient.SqlConnection  conn , string sqlString)
 {
     try
     {
         if (conn.State == System.Data.ConnectionState.Closed )
         {
             conn.Open();
         }
         System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(sqlString , conn);
         cmd.CommandTimeout = 36000 ;
         int i = cmd.ExecuteNonQuery();
         cmd.Dispose();
         conn.Close();
         return true;
     }
     //            catch//(NullReferenceException Nexp)
     //            {
     //                //System.Windows.Forms.MessageBox.Show(Nexp.Message);
     //                return false;
     //            }
     catch//(Exception exp)
     {
         //System.Windows.Forms.MessageBox.Show(exp.Message);
         return false;
     }
 }

 protected static void SprocSetTokenExpires(int tokenId, System.DateTime expires)
 {
     // create a connection...
     System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(EnterpriseApplication.Application.ConnectionString);
     connection.Open();
     // create a command...
     System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand("SetTokenExpires", connection);
     command.CommandType = System.Data.CommandType.StoredProcedure;
     // parameters...
     System.Data.SqlClient.SqlParameter tokenIdParam = command.Parameters.Add("@tokenId", System.Data.SqlDbType.Int);
     tokenIdParam.Value = tokenId;
     System.Data.SqlClient.SqlParameter expiresParam = command.Parameters.Add("@expires", System.Data.SqlDbType.DateTime);
     expiresParam.Value = expires;
     // execute...
     command.ExecuteNonQuery();
     // cleanup...
     command.Dispose();
     connection.Close();
 }

 // Thanks to Kevin Trickey for providing the C# implementation of this method!
 protected static DataSet SprocGetToken(string token)
 {
     // create a connection...
     System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(EnterpriseApplication.Application.ConnectionString);
     connection.Open();
     // create a command...
     System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand("GetToken", connection);
     command.CommandType = System.Data.CommandType.StoredProcedure;
     // parameters...
     System.Data.SqlClient.SqlParameter tokenParam  = command.Parameters.Add("@token", System.Data.SqlDbType.VarChar, 256);
     tokenParam.Value = token;
     // extract the dataset...
     System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter(command);
     DataSet dataset = new DataSet();
     adapter.Fill(dataset);
     adapter.Dispose();
     // cleanup...
     command.Dispose();
     connection.Close();
     // return dataset...
     return dataset;
 }

 protected static int SprocCreateSecurityToken(int userId, string token, System.DateTime expires)
 {
     // create a connection...
     System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(EnterpriseApplication.Application.ConnectionString);
     connection.Open();
     // create a command...
     System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand("CreateSecurityToken", connection);
     command.CommandType = System.Data.CommandType.StoredProcedure;
     // parameters...
     System.Data.SqlClient.SqlParameter userIdParam = command.Parameters.Add("@userId", System.Data.SqlDbType.Int);
     userIdParam.Value = userId;
     System.Data.SqlClient.SqlParameter tokenParam = command.Parameters.Add("@token", System.Data.SqlDbType.VarChar, 256);
     tokenParam.Value = token;
     System.Data.SqlClient.SqlParameter expiresParam = command.Parameters.Add("@expires", System.Data.SqlDbType.DateTime);
     expiresParam.Value = expires;
     System.Data.SqlClient.SqlParameter returnValueParam = command.Parameters.Add("@returnValueParam", System.Data.SqlDbType.Int);
     returnValueParam.Direction = System.Data.ParameterDirection.ReturnValue;
     // execute...
     command.ExecuteNonQuery();
     // cleanup...
     command.Dispose();
     connection.Close();
     // return...
     return ((int)(returnValueParam.Value));
 }