C# SqlDataAdapter.Dispose方法代码示例

        public DataSet FilterData(ArsonFilterModel filterData)
        {
            var connectionString = ConfigurationManager.ConnectionStrings["UCR_DataEntities"];
            DataSet dsResult = new DataSet();
            using (SqlConnection conn = new SqlConnection(connectionString.ConnectionString))
            {
                try
                {
                    SqlCommand command = new SqlCommand();
                    command.Connection = conn;
                    command.CommandText = GenerateArsonORQuery(filterData);
                    command.CommandType = System.Data.CommandType.Text;
                    SqlDataAdapter adapter = new SqlDataAdapter();
                    adapter.SelectCommand = command;

                    conn.Open();
                    adapter.Fill(dsResult);
                    conn.Close();
                    adapter.Dispose();
                    command.Dispose();
                }
                catch (Exception ex)
                {
                    StorageClient.LogError(ex);
                }
            }
            return dsResult;
        }

        public static string SP_Tender_FindRecord(string ProcessId, ref DataSet ReturnDs)
        {
            SqlConnection sqlConn = new SqlConnection();  //defines database connection
            SqlCommand sqlCmd = new SqlCommand();  //defines what to do
            SqlDataAdapter sqlAdap = new SqlDataAdapter();

            try
            {
                sqlConn.ConnectionString = ConfigurationManager.ConnectionStrings["AMS_MasterConnectionString"].ToString();
                sqlConn.Open();

                sqlCmd.CommandText = "SP_Tender_FindRecord";
                sqlCmd.CommandType = CommandType.StoredProcedure;
                sqlCmd.Connection = sqlConn;

                SqlParameter parm1 = new SqlParameter("@ProcessId", SqlDbType.VarChar);
                parm1.Value = ProcessId;
                parm1.Direction = ParameterDirection.Input;
                sqlCmd.Parameters.Add(parm1);

                sqlAdap.SelectCommand = sqlCmd;
                sqlAdap.Fill(ReturnDs);

                return string.Empty;

            }
            catch (Exception err)
            {
                return err.Message;
            }
            finally
            { sqlConn.Close(); sqlConn.Dispose(); sqlAdap.Dispose(); }
        }

 /// <summary>
 /// Вывести в ds результат запроса select из command
 /// В случае ошибки вывести код ошибки.
 /// </summary>
 /// <param name="command"></param>
 /// <param name="ds"></param>
 /// <param name="ErrMsg"></param>
 /// <returns></returns>
 public static SqlState SqlViewTable(string Command, ref DataSet ResultDS, ref string ErrMsg)
 {
     SqlDataAdapter sql = new SqlDataAdapter(Command, ConnectString);
     ResultDS = new DataSet();
     try
     {
         int count = sql.Fill(ResultDS);
         sql.Dispose();
         if (count > 0)
             return SqlState.Success;
         else
         {
             ErrMsg = "Запрос не вернул ни одну запись.";
             return SqlState.EmptyResult;
         }
     }
     catch (Exception exp)
     {
         sql.Dispose();
         ErrMsg = "Method: " + exp.TargetSite.Name.ToString() + "\r\nMessage: " + exp.Message + "\r\nSource: " + exp.Source + "\r\nCommand: " + Command;
         MessageBox.Show(ErrMsg, "SqlViewTable() Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
         return SqlState.Error;
     }
     finally
     {
         sql.Dispose();
     }
 }

        public static void NewBlogSubscribers()
        {
            SqlTriggerContext triggContext = SqlContext.TriggerContext;
            SqlCommand command = null;
            DataSet insertedDS = null;
            SqlDataAdapter dataAdapter = null;

            try
            {
                // Retrieve the connection that the trigger is using
                using (SqlConnection connection
                   = new SqlConnection(@"context connection=true"))
                {
                    connection.Open();

                    command = new SqlCommand(@"SELECT * FROM INSERTED;",
                       connection);

                    dataAdapter = new SqlDataAdapter(command);
                    insertedDS = new DataSet();
                    dataAdapter.Fill(insertedDS);

                    TriggerHandler(connection, ContentType.Blog, insertedDS);
                }
            }
            catch { }
            finally
            {
                try
                {

                    if (command != null)
                    {
                        command.Dispose();
                        command = null;
                    }

                    if (dataAdapter != null)
                    {
                        dataAdapter.Dispose();
                        dataAdapter = null;
                    }

                    if (dataAdapter != null)
                    {
                        dataAdapter.Dispose();
                        dataAdapter = null;
                    }

                    if (insertedDS != null)
                    {
                        insertedDS.Dispose();
                        insertedDS = null;
                    }
                }
                catch { }
            }
        }

        /// <summary>
        /// ִ�д洢���̻�SQL��䣬������һ��DataTable
        /// </summary>
        /// <param name="strProcedureName">Ҫ�󷵻�DataTable�Ĵ洢���̻�SQL���</param>
        /// <returns></returns>
        public DataTable ExcuteProcedure(string strProcedureName)
        {
            DataTable dt = new DataTable();
            SqlConnection conn=null;

            try
            {
                conn = new SqlConnection(ConfigurationSettings.AppSettings.GetValues("connection")[0]);

                SqlDataAdapter sda = new SqlDataAdapter(strProcedureName, conn);

                DataSet ds = new DataSet();
                conn.Open();

                sda.Fill(ds);

                dt = ds.Tables[0];

                sda.Dispose();

                conn.Close();
                conn.Dispose();
            }
            catch (Exception ex)
            {
                Err MyErr = new Err(ex.Message.ToString());
            }

            return dt;
        }

    public DataTable getData(partyBO PartyBO)
    {
        try
        {

            query = "fetchParty";
            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }
            dap = new SqlDataAdapter(query, con);
            dap.SelectCommand.CommandType = CommandType.StoredProcedure;
            dap.SelectCommand.Parameters.AddWithValue("@countryId", PartyBO.countryId);
            DataSet ds = new DataSet();
            dap.Fill(ds, "temp");
            dap.Dispose();
            return ds.Tables["temp"];
        }
        catch
        {
            throw;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
                con.Close();
        }
    }

 /// <summary>
 /// 下SQL Command取回資料
 /// </summary>
 /// <param name="sSqlCmd">SQL Commnad String</param>
 /// <param name="sConnStr1">SQL Connection String</param>
 /// <returns>回傳經由SQL Command Select後的結果，傳回DataTable</returns>
 public DataTable execDataTable(string sSqlCmd, string sConnStr1)
 {
     SqlConnection conDB = new SqlConnection();
     SqlDataAdapter sqlDataAdap = new SqlDataAdapter();
     DataTable dttReturnData = new DataTable();
     try
     {
         conDB = new SqlConnection(sConnStr1);
         sqlDataAdap = new SqlDataAdapter(sSqlCmd, conDB);
         sqlDataAdap.Fill(dttReturnData);
     }
     catch (Exception ex)
     {
         gLogger.ErrorException("DBConnection.execDataTable", ex);
         throw ex;
     }
     finally
     {
         if (sqlDataAdap != null)
         {
             sqlDataAdap.Dispose();
         }
         if (conDB != null)
         {
             if (conDB.State == ConnectionState.Open)
             {
                 conDB.Close();
             }
             conDB.Dispose();
         }
     }
     return dttReturnData;
 }

        public static string GetProcessId(ref string ProcessId, string TenderNumber)
        {
            SqlConnection sqlConn = new SqlConnection();  //defines database connection
            SqlCommand sqlCmd = new SqlCommand();  //defines what to do
            SqlDataAdapter sqlAdap = new SqlDataAdapter();

            try
            {
                sqlConn.ConnectionString = ConfigurationManager.ConnectionStrings["AMS_MasterConnectionString"].ToString();
                sqlConn.Open();

                sqlCmd.CommandText = "SELECT  [TH_ProcessID] FROM [AMS_Master].[dbo].[Tender_Header] H WHERE [TH_NoTender] = '" + TenderNumber + "'";
                sqlCmd.CommandType = CommandType.Text;
                sqlCmd.Connection = sqlConn;

                ProcessId = sqlCmd.ExecuteScalar().ToString();
                return string.Empty;
            }
            catch (Exception err)
            {
                return err.Message;
            }
            finally
            { sqlConn.Close(); sqlConn.Dispose(); sqlAdap.Dispose(); }
        }

    protected void LinkButtonOffers_Click(object sender, EventArgs e)
    {
        PanelUsers.Visible = false;
        PanelOffers.Visible = true;
        PanelCredit.Visible = false;
        PanelCoupons.Visible = false;
        LinkButtonUsers.Enabled = true;
        LinkButtonOffers.Enabled = false;
        LinkButtonCredit.Enabled = true;
        LinkButtonCoupons.Enabled = true;

        DataTable dt = new DataTable();
        DataSet ds = new DataSet();
        SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["ShopConnectionString"].ConnectionString);

        SqlDataAdapter sda = new SqlDataAdapter("sp_statsOffers", sqlConn);
        sda.SelectCommand.CommandType = CommandType.StoredProcedure;
        sda.Fill(ds);
        dt = ds.Tables[0];

        TimeClass tc = new TimeClass();
        LabelOffersDate.Text = tc.ConvertToIranTimeString(Convert.ToDateTime(dt.Rows[0]["OffersDate"].ToString()));

        LabelOffersOffers.Text = dt.Rows[0]["OffersCount"].ToString();
        LabelOffersActive.Text = dt.Rows[0]["OffersActive"].ToString();
        LabelOffersPast.Text = dt.Rows[0]["OffersPast"].ToString();
        LabelOffersSold.Text = dt.Rows[0]["OffersSoldCount"].ToString();
        LabelOffersAverage.Text = (Convert.ToInt32(dt.Rows[0]["OffersSoldCount"].ToString()) / Convert.ToInt32(dt.Rows[0]["OffersPast"].ToString())).ToString();

        sda.Dispose();
        sqlConn.Close();
    }

    protected void Page_Load(object sender, EventArgs e)
    {
        if (Session["kullanici"] != null || Session["yonetici"] != null)
        {

        }
        else
        {
            Response.Redirect("KullaniciGiris.aspx");
        }
        try
        {
            Yardimci.baglanti.Open();
                SqlCommand ogretmen_cek = new SqlCommand();
                ogretmen_cek.CommandText = "SELECT * FROM OGRETMENLER_VIEW2";
                ogretmen_cek.Connection = Yardimci.baglanti;
                DataSet ds = new DataSet();
                SqlDataAdapter adp = new SqlDataAdapter(ogretmen_cek);
                adp.Fill(ds, "OGRETMENLER_VIEW2");
                dwOgretmenler.DataSource = ds.Tables["OGRETMENLER_VIEW2"];
                dwOgretmenler.DataBind();
                ds.Dispose();
                adp.Dispose();
                Yardimci.baglanti.Close();
        }
        catch (Exception hata)
        {
            Yardimci.baglanti.Close();
            lbl_sonuc.Text = hata.Message;
        }
    }

    public DataTable getIssues(Int64 NUMBER,Int16 TYPE)
    {
        try
        {

            query = "ISSUES_FETCHING";
            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }
          dap = new SqlDataAdapter(query,con);
          dap.SelectCommand.CommandType = CommandType.StoredProcedure;
          dap.SelectCommand.Parameters.AddWithValue("@number",NUMBER) ;
          dap.SelectCommand.Parameters.AddWithValue("@type", TYPE);

          DataSet ds = new DataSet();
           dap.Fill(ds,"temp");
           dap.Dispose();
            return ds.Tables["temp"];
        }
        catch
        {
            throw;
        }
        finally
        {  if(con.State == ConnectionState.Open)
            con.Close();
        }
    }

    protected void Page_Load(object sender, EventArgs e)
    {
        if (Session["kullanici"] != null || Session["yonetici"] != null)
        {

        }
        else
        {
            Response.Redirect("KullaniciGiris.aspx");
        }
        try
        {
            Yardimci.baglanti.Open();
            SqlCommand sinif_cek = new SqlCommand();
            sinif_cek.CommandText = "SELECT SINIF_ID, SINIF_AD FROM SINIFLAR";
            sinif_cek.Connection = Yardimci.baglanti;
            DataSet ds = new DataSet();
            SqlDataAdapter adp = new SqlDataAdapter(sinif_cek);
            adp.Fill(ds, "SINIFLAR");
            dwSiniflar.DataSource = ds.Tables["SINIFLAR"];
            dwSiniflar.DataBind();
            ds.Dispose();
            adp.Dispose();
            Yardimci.baglanti.Close();
        }
        catch (Exception hata)
        {
            Yardimci.baglanti.Close();
            lbl_sonuc.Text = hata.Message;
        }
    }

    //district
    public DataTable load_districts(Int16 stateId)
    {
        try
        {
            if (care.State == ConnectionState.Closed)
            {
                care.Open();
            }

            adapt = new SqlDataAdapter("fetchDist", care);
            adapt.SelectCommand.CommandType = CommandType.StoredProcedure;
            adapt.SelectCommand.Parameters.AddWithValue("@stateId", stateId);
            DataSet ds = new DataSet();
            adapt.Fill(ds, "tblDist");
            adapt.Dispose();
            return ds.Tables["tblDist"];
        }
        catch
        {
            throw;
        }

        finally
        {
            care.Close();
        }
    }

 public async Task<List<WindowsDtls>> GetWindows()
 {
     var constring = @"Data Source='c:\users\u3696174\documents\visual studio 2012\Projects\CLSBforNode\CLSBforNode\App_Data\Database1.sdf'";
     List<WindowsDtls> lst_result = new List<WindowsDtls>();
     using (SqlConnection con=new SqlConnection(constring))
     {
         await con.OpenAsync();
         using (SqlCommand cmd=new SqlCommand())
         {
             cmd.Connection = con;
             cmd.CommandText = @"select * from Windows";
             cmd.CommandType = CommandType.Text;
             SqlDataAdapter adapter = new SqlDataAdapter(cmd);
             DataTable dt = new DataTable();
             adapter.Fill(dt);
             adapter.Dispose();
             con.Close();
            
             WindowsDtls obj_single = null;
             foreach (DataRow dr in dt.Rows)
             {
                 obj_single = new WindowsDtls();
                 obj_single.ID = Convert.ToInt32( dr[0].ToString());
                 obj_single.Name = dr[1].ToString();
                 obj_single.Quantity = dr[2].ToString();
                 obj_single.Price = dr[3].ToString();
                 obj_single.Image = dr[4].ToString();
                 lst_result.Add(obj_single);
                 obj_single = null;
             }
         }
         
     }
     return lst_result;
 }

        public Int32 getUserId(string VerificationCode)
        {
            DataTable dt = new DataTable();
            DataSet ds = new DataSet();
            SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["AppConnectionString"].ConnectionString);
            SqlDataAdapter sda = new SqlDataAdapter("sp_loginSessionRead", sqlConn);

            //try
            //{
                sda.SelectCommand.CommandType = CommandType.StoredProcedure;
                sda.SelectCommand.Parameters.Add("@VerificationCode", SqlDbType.NVarChar).Value = VerificationCode;
                sda.Fill(ds);
                dt = ds.Tables[0];
            //}
            //catch (Exception ex)
            //{

            //}
            //finally
            //{
                sqlConn.Close();
                sda.Dispose();
                sqlConn.Dispose();
            //}

            if (dt.Rows.Count == 0) //no user found
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(dt.Rows[0]["UserId"].ToString());
            }
        }