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

        static RoomClient()
        {
            
            //string ConnectString = "metadata=res://*/Model2.csdl|res://*/Model2.ssdl|res://*/Model2.msl;provider=System.Data.SqlClient;provider connection string="Server=192.168.2.24,8605;Database=dbroom;User ID=sa;Password=654321;MultipleActiveResultSets=True"";
            //System.Data.SqlClient.SqlConnectionStringBuilder sqlConnection = new System.Data.SqlClient.SqlConnectionStringBuilder();
            //sqlConnection.DataSource = @"192.168.2.24,8605";
            //sqlConnection.ApplicationName = "";
            //sqlConnection.InitialCatalog = "dbroom";
            //sqlConnection.IntegratedSecurity = true;
            //sqlConnection.PersistSecurityInfo = true;
            //sqlConnection.UserID = "sa";
            //sqlConnection.Password ="654321";

            //string connectString = "Server=10.21.99.82;Database=SecureDB;User ID=secure;Password=secure;";
            string connectString = "data source=10.21.99.80;initial catalog=SecureDB;persist security info=True;user id=secure;password=secure;MultipleActiveResultSets=True;App=EntityFramework;";
            string dir = System.Diagnostics.Process.GetCurrentProcess().MainModule.FileName;
            dir = dir.Remove(dir.LastIndexOf('\\'));
            if (System.IO.File.Exists(dir [email protected]"\Server.txt"))
            {
                string serverSet = System.IO.File.ReadLines(dir + @".\Server.txt").First();
                if (serverSet.Length < 300)
                {
                    connectString = serverSet;
                }
            }

            //System.Data.SqlClient.SqlConnection s = new System.Data.SqlClient.SqlConnection(sqlConnection.ConnectionString);

            //System.Data.EntityClient.EntityConnectionStringBuilder ecsb = new System.Data.EntityClient.EntityConnectionStringBuilder();
            //ecsb.Provider = "System.Data.SqlClient";              
            //ecsb.ProviderConnectionString = sqlConnection.ConnectionString;
            //ecsb.Metadata = @"res://*/Model2.csdl|res://*/Model2.ssdl|res://*/Model2.msl";
            //System.Data.EntityClient.EntityConnection ec = new System.Data.EntityClient.EntityConnection(ecsb.ConnectionString);

            //dbroomClientEntities dbroom = new dbroomClientEntities(ec);
            try
            {
                System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter("Select * from tblHostConfig;", connectString);
                System.Data.DataTable DT = new System.Data.DataTable();
                adapter.Fill(DT);
                adapter.Dispose();
                objUrl = "tcp://" + DT.Rows[0]["IP"] + ":" + DT.Rows[0]["Port"] + "/RoomObj";
                
                System.Runtime.Remoting.Channels.Tcp.TcpChannel tcp = new System.Runtime.Remoting.Channels.Tcp.TcpChannel(0);
                System.Runtime.Remoting.Channels.ChannelServices.RegisterChannel(tcp, false);
                //var HostData = (from o in dbroom.tblHostConfigs select o).First();
                //objUrl = "tcp://" + HostData.IP + ":" + HostData.Port + "/RoomObj";
            }
            catch (Exception )
            {
                throw new Exception("資料庫讀取失敗");
            }
            roomEvent.RoomEvent += new RoomEventHandler(RoomClient_RoomEvent);
            timer.Elapsed += new System.Timers.ElapsedEventHandler(timer_Elapsed);
            timer.Interval = 10000;
            timer.Start();           
        }

        /// <summary>
        /// Run a transact SQL query against the database.
        /// </summary>
        /// <param name="sqlQuery">String with the SQL query (Select, etc).</param>
        /// <returns>The DataTable with the result set. Returns a New, empty DataTable if there are any exceptions.</returns>
        public System.Data.DataTable GetDataTable(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 new System.Data.DataTable();
            }

            // Clear past exceptions
            this.hasException = false;

            /*
             * Switch to the appropriate database client and execute the query
             *
             * Create an adapter to run the SQL query
             * It was found some WFS Queries exceeded the default time out, so we bump up the command timeout
             * Fill the DataTable with the results of the SQL query
             * Dispose of the Adapter
             * Send the Adapter to Nothing
             * Return the DataTable
             */

            System.Data.DataTable output = new System.Data.DataTable();

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

                    try
                    {
                        System.Data.OleDb.OleDbDataAdapter oleAdapter = new System.Data.OleDb.OleDbDataAdapter(sqlQuery, this.oleConn);
                        oleAdapter.SelectCommand.CommandTimeout = this.CommandTimeout;
                        oleAdapter.Fill(output);
                        oleAdapter.Dispose();
                        return output;
                    }
                    catch (Exception exception)
                    {
                        this.hasException = true;
                        this.lastException = exception;
                        if (this.ThrowExceptions == true) throw this.lastException;
                    }

                    break;

                case DatabaseClient.SqlClient:

                    try
                    {
                        System.Data.SqlClient.SqlDataAdapter sqlAdapter = new System.Data.SqlClient.SqlDataAdapter(sqlQuery, this.sqlConn);
                        sqlAdapter.SelectCommand.CommandTimeout = this.CommandTimeout;
                        sqlAdapter.Fill(output);
                        sqlAdapter.Dispose();
                        return output;
                    }
                    catch (Exception exception)
                    {
                        this.hasException = true;
                        this.lastException = exception;
                        if (this.ThrowExceptions == true) throw this.lastException;
                    }

                    break;

                default:
                    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 new System.Data.DataTable();
        }

 //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 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>
 /// ִ��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();
     }
 }

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

        private void cmbSqlTables_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                string dataBase =  this.cmbSqlDataBases.SelectedItem.ToString();
                System.Data.SqlClient.SqlConnection cnn = new System.Data.SqlClient.SqlConnection(Program._cnnStringNoDataBase+ dataBase) ;
                System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
                System.Data.SqlClient.SqlDataAdapter ad = new System.Data.SqlClient.SqlDataAdapter();
                System.Data.DataSet dataset = new DataSet();
                cnn.Open();
                cmd.Connection = cnn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = '" + cmbSqlTables.Text + "';";
                ad.SelectCommand = cmd;
                ad.Fill(dataset);
                lsbSqlFild.Items.Clear();
                foreach (DataRow R in dataset.Tables[0].Rows)
                {
                    lsbSqlFild.Items.Add(R[0].ToString());
                }

                dataset.Dispose();
                ad.Dispose();
                cnn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void btnGetInfoSql_Click(object sender, EventArgs e)
        {
            try
            {
                /*si quieres obtener todas las tablas
                 * SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
                    si quieres obtener todas las columnas
                    SELECT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
                 */
                String dataBase = this.cmbSqlDataBases.SelectedItem.ToString();

                System.Data.SqlClient.SqlConnection cnn = new System.Data.SqlClient.SqlConnection(Program._cnnStringNoDataBase + dataBase);
                cnn.Open();
                System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES", cnn);
                System.Data.SqlClient.SqlDataAdapter ad = new System.Data.SqlClient.SqlDataAdapter(cmd);
                System.Data.DataSet dtset = new DataSet();
                ad.Fill(dtset);
                cmbSqlTables.Items.Clear();
                foreach (DataRow R in dtset.Tables[0].Rows)
                {
                    cmbSqlTables.Items.Add(R[0].ToString());
                }
                ad.Dispose();
                dtset.Dispose();
                cnn.Close();
                MessageBox.Show("Done...");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

 public DataRowCollection GetSP_Rows(string ProcedureName)
 {
     this.cmd.CommandText = ProcedureName;
     this.cmd.CommandType = CommandType.StoredProcedure;//指定是存储过程
     this.GenParameters();
     System.Data.SqlClient.SqlDataAdapter ada = new System.Data.SqlClient.SqlDataAdapter();
     ada.SelectCommand = (System.Data.SqlClient.SqlCommand)cmd;
     DataTable dt = new DataTable();
     ada.Fill(dt);
     ada.Dispose();
     return dt.Rows;
 }