C# OdbcConnection.Close方法代码示例

    //luodaan uusi bisnes
    public BLbusiness(string name, string katuosoite, string postinro, string kaupunki, OdbcConnection conn)
    {
        //business address
        this.address = new BLaddress(conn, katuosoite, postinro, kaupunki);

        /*start copy pasta insert/select same in all BL files*/
        try
        {
            //parameterized queries
            conn.Open();
            OdbcCommand insertcmd = conn.CreateCommand();
            insertcmd.CommandText = "INSERT IGNORE INTO business (BusinessName, Address_AddressId) VALUES (?, ?)";
            insertcmd.Parameters.Add("@BusinessName", OdbcType.VarChar, 100).Value = name;
            insertcmd.Parameters.Add("@Address_AddressId", OdbcType.Int).Value = this.address.id;
            insertcmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            this.id = -1;
            this.name = ex.ToString();
            conn.Close();
            return;
        }
        finally
        {
            conn.Close();
        }

        try
        {
            //parameterized queries
            conn.Open();
            OdbcCommand cmd = conn.CreateCommand();
            cmd.CommandText = "SELECT BusinessId, BusinessName FROM business WHERE BusinessName = ?";
            cmd.Parameters.Add("@BusinessName", OdbcType.VarChar, 100).Value = name;
            OdbcDataReader rows = cmd.ExecuteReader();
            rows.Read();
            int id;
            int.TryParse(rows.GetString(0), out id);
            this.id = id;
            this.name = rows.GetString(1);
        }
        catch (Exception ex)
        {
            this.id = -1;
            this.name = ex.ToString();
            conn.Close();
            return;
        }
        finally
        {
            conn.Close();
        }
        /*end copy pasta insert/select same in all BL files*/
    }

        public ContentResult Create(string json)
        {
            OdbcConnection hiveConnection = new OdbcConnection("DSN=Hadoop Server;UID=hadoop;PWD=hadoop");
            hiveConnection.Open();

            Stream req = Request.InputStream;
            req.Seek(0, SeekOrigin.Begin);
            string request = new StreamReader(req).ReadToEnd();
            ContentResult response;
            string query;

            try
            {
                query = "INSERT INTO TABLE error_log (json_error_log) VALUES('" + request + "')";
                OdbcCommand command = new OdbcCommand(query, hiveConnection);
                command.ExecuteNonQuery();
                command.CommandText = query;
                response = new ContentResult { Content = "{status: 1}", ContentType = "application/json" };
                hiveConnection.Close();
                return response;
            }
            catch(WebException error)
            {
                response = new ContentResult { Content = "{status: 0, message:" + error.Message.ToString()+ "}" };
                System.Diagnostics.Debug.WriteLine(error.ToString());
                hiveConnection.Close();
                return response;
            }
        }

    //constructor
    public BLfeedback(int customerid, int servicepackageid, OdbcConnection conn)
    {
        /*start copy pasta insert/select same in all BL files*/
        try
        {
            //parameterized queries
            conn.Open();
            OdbcCommand insertcmd = conn.CreateCommand();
            insertcmd.CommandText = "INSERT IGNORE INTO feedback (Customer_CustomerId, Timestamp, ServicePackage_ServicePackageId) VALUES (?,?,?)";
            insertcmd.Parameters.Add("@Customer_CustomerId", OdbcType.Int).Value = customerid;
            this.timestamp = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
            insertcmd.Parameters.Add("@Timestamp", OdbcType.VarChar, 20).Value = this.timestamp;
            insertcmd.Parameters.Add("@Customer_CustomerId", OdbcType.Int).Value = servicepackageid;
            insertcmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            this.id = -1;
            this.name = ex.ToString();
            conn.Close();
            return;
        }
        finally
        {
            conn.Close();
        }

        try
        {
            //parameterized queries
            conn.Open();
            OdbcCommand cmd = conn.CreateCommand();
            cmd.CommandText = "SELECT FeedbackId, Customer_CustomerId, Timestamp, ServicePackage_ServicePackageId FROM feedback WHERE Customer_CustomerId = ? AND Timestamp = ? AND ServicePackage_ServicePackageId = ?";
            cmd.Parameters.Add("@Customer_CustomerId", OdbcType.Int).Value = customerid;
            cmd.Parameters.Add("@Timestamp", OdbcType.VarChar, 20).Value = this.timestamp;
            cmd.Parameters.Add("@Customer_CustomerId", OdbcType.Int).Value = servicepackageid;
            OdbcDataReader rows = cmd.ExecuteReader();
            rows.Read();
            int id;
            int.TryParse(rows.GetString(0), out id);
            this.id = id;
            int id2;
            int.TryParse(rows.GetString(1), out id2);
            this.customerid = id2;
            this.timestamp = rows.GetString(2);
        }
        catch (Exception ex)
        {
            this.id = -1;
            this.name = ex.ToString();
            conn.Close();
            return;
        }
        finally
        {
            conn.Close();
        }
        /*end copy pasta insert/select same in all BL files*/
    }

    //constructor
    public BLcustomer(string name, string phonenumber, string email, OdbcConnection conn)
    {
        /*start copy pasta insert/select same in all BL files*/
        try
        {
            //parameterized queries
            conn.Open();
            OdbcCommand insertcmd = conn.CreateCommand();
            insertcmd.CommandText = "INSERT IGNORE INTO customer (CustomerName, PhoneNro, Email) VALUES (?, ?, ?)";
            insertcmd.Parameters.Add("@CustomerName", OdbcType.VarChar, 100).Value = name;
            insertcmd.Parameters.Add("@PhoneNro", OdbcType.VarChar, 10).Value = phonenumber;
            insertcmd.Parameters.Add("@Email", OdbcType.VarChar, 100).Value = email;
            insertcmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            this.id = -1;
            this.name = ex.ToString();
            conn.Close();
            return;
        }
        finally
        {
            conn.Close();
        }

        try
        {
            //parameterized queries
            conn.Open();
            OdbcCommand cmd = conn.CreateCommand();
            cmd.CommandText = "SELECT CustomerId, CustomerName, PhoneNro, Email FROM customer WHERE CustomerName = ? AND PhoneNro = ? AND Email = ?";
            cmd.Parameters.Add("@CustomerName", OdbcType.VarChar, 100).Value = name;
            cmd.Parameters.Add("@PhoneNro", OdbcType.VarChar, 10).Value = phonenumber;
            cmd.Parameters.Add("@Email", OdbcType.VarChar, 100).Value = email;
            OdbcDataReader rows = cmd.ExecuteReader();
            rows.Read();
            int id;
            int.TryParse(rows.GetString(0), out id);
            this.id = id;
            this.name = rows.GetString(1);
            this.phonenumber = rows.GetString(2);
            this.email = rows.GetString(3);
        }
        catch (Exception ex)
        {
            this.id = -1;
            this.name = ex.ToString();
            conn.Close();
            return;
        }
        finally
        {
            conn.Close();
        }
        /*end copy pasta insert/select same in all BL files*/
    }

    //constructor
    public BLcomment(string name, int feedbackid, OdbcConnection conn)
    {
        /*start copy pasta insert/select same in all BL files*/
        try
        {
            //parameterized queries
            conn.Open();
            OdbcCommand insertcmd = conn.CreateCommand();
            insertcmd.CommandText = "INSERT IGNORE INTO comment (Text, Feedback_FeedbackId) VALUES (?, ?)";
            insertcmd.Parameters.Add("@Text", OdbcType.VarChar, 1024).Value = name;
            insertcmd.Parameters.Add("@Feedback_FeedbackId", OdbcType.Int).Value = feedbackid;
            insertcmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            this.id = -1;
            this.name = ex.ToString();
            conn.Close();
            return;
        }
        finally
        {
            conn.Close();
        }

        try
        {
            //parameterized queries
            conn.Open();
            OdbcCommand cmd = conn.CreateCommand();
            cmd.CommandText = "SELECT CommentId, Text, Feedback_FeedbackId FROM comment WHERE Text = ? AND Feedback_FeedbackId = ?";
            cmd.Parameters.Add("@Text", OdbcType.VarChar, 1024).Value = name;
            cmd.Parameters.Add("@Feedback_FeedbackId", OdbcType.Int).Value = feedbackid;
            OdbcDataReader rows = cmd.ExecuteReader();
            rows.Read();
            int id;
            int.TryParse(rows.GetString(0), out id);
            this.id = id;
            this.name = rows.GetString(1);
            int id2;
            int.TryParse(rows.GetString(2), out id2);
            this.feedbackid = id2;
        }
        catch (Exception ex)
        {
            this.id = -1;
            this.name = ex.ToString();
            conn.Close();
            return;
        }
        finally
        {
            conn.Close();
        }
        /*end copy pasta insert/select same in all BL files*/
    }

    //constructor
    public BLaddress(OdbcConnection conn, string katuosoite, string postinro, string kaupunki)
    {
        //constructing attributes
        this.city = new BLcity(conn, kaupunki);
        this.zipcode = new BLzipcode(conn, postinro);

        /*start copy pasta insert/select same in all BL files*/
        try
        {
            //parameterized queries
            conn.Open();
            OdbcCommand insertcmd = conn.CreateCommand();
            insertcmd.CommandText = "INSERT IGNORE INTO address (City_CityId, ZIPCode_ZIPCodeId, StreetAddress) VALUES (?, ?, ?)";
            insertcmd.Parameters.Add("@City_CityId", OdbcType.Int).Value = this.city.id;
            insertcmd.Parameters.Add("@ZIPCode_ZIPCodeId", OdbcType.Int).Value = this.zipcode.id;
            insertcmd.Parameters.Add("@StreetAddress", OdbcType.VarChar, 50).Value = katuosoite;
            insertcmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            this.name = ex.ToString();
            conn.Close();
            return;
        }
        finally
        {
            conn.Close();
        }

        try
        {
            //parameterized queries
            conn.Open();
            OdbcCommand cmd = conn.CreateCommand();
            cmd.CommandText = "SELECT AddressId, StreetAddress FROM address WHERE StreetAddress = ?";
            cmd.Parameters.Add("@ZIPCodeNumber", OdbcType.VarChar, 50).Value = katuosoite;
            OdbcDataReader rows = cmd.ExecuteReader();
            rows.Read();
            int id;
            int.TryParse(rows.GetString(0), out id);
            this.id = id;
            this.name = rows.GetString(1);
        }
        catch (Exception ex)
        {
            this.name = ex.ToString();
            conn.Close();
            return;
        }
        finally
        {
            conn.Close();
        }
        /*end copy pasta insert/select same in all BL files*/
    }

        protected void btnLoginOK_Click(object sender, EventArgs e)
        {
            string connection = ConfigurationSettings.AppSettings["connectionString"];
            OdbcConnection dbCon = new OdbcConnection(connection);
            string sql = "SELECT seq FROM profile WHERE (MEMBER_NAME = @userid) AND (MEMBER_PWD = @pwd)";
            OdbcCommand dbCmd = new OdbcCommand(sql, dbCon);
            dbCmd.Parameters.Add("@userid", OdbcType.VarChar, 50, "MEMBER_NAME");
            dbCmd.Parameters.Add("@pwd", OdbcType.VarChar, 30, "MEMBER_PWD");

            dbCon.Open();
            dbCmd.Parameters["@userid"].Value = txtName.Text;
            dbCmd.Parameters["@pwd"].Value = txtPwd.Text;

            if (dbCmd.ExecuteScalar() == null)
            {

                ClientScriptManager cs = Page.ClientScript;
                cs.RegisterStartupScript(this.GetType(), "windowClose", "window.location.replace('./DefaultLogin.aspx');", true);
                cs.RegisterStartupScript(this.GetType(), "PopupScript", "alert('Incorrect ID/PW');", true);

                dbCmd.ExecuteNonQuery();
                dbCon.Close();
            }
            else
            {

                dbCmd.CommandText = "select * from profile where (MEMBER_NAME = @userid)";
                OdbcDataReader reader = dbCmd.ExecuteReader();
                reader.Read();
            //add session
                Session.Add("USER_ID", txtName.Text);
                Session.Add("USER_NAME", reader["MEMBER_NAME"].ToString());
                Session.Add("USER_PWD", reader["MEMBER_PWD"].ToString());

                reader.Close();
                dbCmd.ExecuteNonQuery();
                dbCon.Close();

                Response.Redirect("./Board_list.aspx");
                //if (Session["USER_ID"] != null)
                //{
                //    namelbl2.Text = Session["USER_ID"].ToString() + "Welcom.";

                //    //hide login
                //    LoginForm.Visible = false;

                //    //dis login
                //    loginchk.Visible = true;

                //}

            }
        }

    public BLservicepackage(string name, int service, OdbcConnection conn)
    {
        /*start copy pasta insert/select same in all BL files*/
        try
        {
            //parameterized queries
            conn.Open();
            OdbcCommand insertcmd = conn.CreateCommand();
            insertcmd.CommandText = "INSERT IGNORE INTO servicepackage (ServicePackageName, Service_ServiceId) VALUES (?, ?)";
            insertcmd.Parameters.Add("@ServicePackageName", OdbcType.VarChar, 100).Value = name;
            insertcmd.Parameters.Add("@Service_ServiceId", OdbcType.Int).Value = service;
            insertcmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            this.id = -1;
            this.name = ex.ToString();
            conn.Close();
            return;
        }
        finally
        {
            conn.Close();
        }

        try
        {
            //parameterized queries
            conn.Open();
            OdbcCommand cmd = conn.CreateCommand();
            cmd.CommandText = "SELECT ServicePackageId, ServicePackageName FROM servicepackage WHERE ServicePackageName = ?";
            cmd.Parameters.Add("@ServicePackageName", OdbcType.VarChar, 100).Value = name;
            OdbcDataReader rows = cmd.ExecuteReader();
            rows.Read();
            int id;
            int.TryParse(rows.GetString(0), out id);
            this.id = id;
            this.name = rows.GetString(1);
        }
        catch (Exception ex)
        {
            this.id = -1;
            this.name = ex.ToString();
            conn.Close();
            return;
        }
        finally
        {
            conn.Close();
        }
        /*end copy pasta insert/select same in all BL files*/
    }

    //constructor
    public BLzipcode(OdbcConnection conn, string postinro)
    {
        /*start copy pasta insert/select same in all BL files*/
        try
        {
            //parameterized queries
            conn.Open();
            OdbcCommand insertcmd = conn.CreateCommand();
            insertcmd.CommandText = "INSERT IGNORE INTO zipcode (ZIPCodeNumber) VALUES (?)";
            insertcmd.Parameters.Add("@ZIPCodeNumber", OdbcType.VarChar, 5).Value = postinro;
            insertcmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            this.id = -1;
            this.name = ex.ToString();
            conn.Close();
            return;
        }
        finally
        {
            conn.Close();
        }

        try
        {
            //parameterized queries
            conn.Open();
            OdbcCommand cmd = conn.CreateCommand();
            cmd.CommandText = "SELECT ZIPCodeId, ZIPCodeNumber FROM zipcode WHERE ZIPCodeNumber = ?";
            cmd.Parameters.Add("@ZIPCodeNumber", OdbcType.VarChar, 5).Value = postinro;
            OdbcDataReader rows = cmd.ExecuteReader();
            rows.Read();
            int id;
            int.TryParse(rows.GetString(0), out id);
            this.id = id;
            this.name = rows.GetString(1);
        }
        catch (Exception ex)
        {
            this.id = -1;
            this.name = ex.ToString();
            conn.Close();
            return;
        }
        finally
        {
            conn.Close();
        }
        /*end copy pasta insert/select kaikissa osoite BL:ssä sama*/
    }

    //constructor
    public BLcity(OdbcConnection conn, string kaupunki)
    {
        /*start copy pasta insert/select same in all BL files*/
        try
        {
            //parameterized queries
            conn.Open();
            OdbcCommand insertcmd = conn.CreateCommand();
            insertcmd.CommandText = "INSERT IGNORE INTO city (CityName) VALUES (?)";
            insertcmd.Parameters.Add("@CityName", OdbcType.VarChar, 20).Value = kaupunki;
            insertcmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            this.id = -1;
            this.name = ex.ToString();
            conn.Close();
            return;
        }
        finally
        {
            conn.Close();
        }

        try
        {
            //parameterized queries
            conn.Open();
            OdbcCommand cmd = conn.CreateCommand();
            cmd.CommandText = "SELECT CityId, CityName FROM city WHERE CityName = ?";
            cmd.Parameters.Add("@CityName", OdbcType.VarChar, 20).Value = kaupunki;
            OdbcDataReader rows = cmd.ExecuteReader();
            rows.Read();
            int id;
            int.TryParse(rows.GetString(0), out id);
            this.id = id;
            this.name = rows.GetString(1);
        }
        catch (Exception ex)
        {
            this.id = -1;
            this.name = ex.ToString();
            conn.Close();
            return;
        }
        finally
        {
            conn.Close();
        }
        /*end copy pasta insert/select same in all BL files*/
    }

        /* queries for all students (IDs, First names, last names)
         * whose ID numbers contain the values provided by the student W# field
         * in the current form. */
        private void button1_Click(object sender, System.EventArgs e)
        {
            //Refresh the listview.
            searchView.Items.Clear();
            string SearchTerm = searchField.Text;
            System.Data.Odbc.OdbcConnection connection = new System.Data.Odbc.OdbcConnection(dsnSource);

            try
            {
                connection.Open();
                System.Data.Odbc.OdbcCommand command = connection.CreateCommand();
                command.CommandText = "SELECT ID, FIRST_NAME, LAST_NAME FROM G_PERSON WHERE ID like '%" + SearchTerm + "%'";
                System.Data.Odbc.OdbcDataReader Reader = command.ExecuteReader();
                while (Reader.Read())
                {
                    String id = Reader["ID"].ToString();
                    String fname = Reader["FIRST_NAME"].ToString();
                    String lname = Reader["LAST_NAME"].ToString();
                    ListViewItem item = new ListViewItem(new[] { id, fname, lname });
                    searchView.Items.Add(item);

                }
                connection.Close();
            } catch (Exception s) {
                MessageBox.Show("Could not connect at this time. Please try again later.");
                MessageBox.Show(s.ToString());
            }
        }

 public static void Load_Sport_TXT_TO_DB_ByTableNameAndFieldName(string table_name, string field_names)
 {
     OpenFileDialog ofd = new OpenFileDialog();
     if (ofd.ShowDialog() == DialogResult.OK)
     {
         using (OdbcConnection conn = new OdbcConnection(Basic_HTB_Info.Conn_Str))
         {
             conn.Open();
             //MessageBox.Show(String.Format("TRUNCATE TABLE {0};", table_name));
             //using (OdbcCommand cmd = new OdbcCommand(String.Format("TRUNCATE TABLE {0};", table_name), conn))
             //{
              //   cmd.ExecuteNonQuery();
             //}
             StreamReader sr = new StreamReader(ofd.FileName,Encoding.Default);
             string line = null;
             while ((line = sr.ReadLine()) != null)
             {
                 string[] str_a = line.Split(';');
                 String[] fieldnames = field_names.Split(',');
                 if (str_a.Length == fieldnames.Length)
                 {
                     Lib.inc_cmd(field_names, table_name, str_a, conn);
                 }
             }
             conn.Close();
         }
     }
 }

 /// <summary>
 /// Executa algum comando de inserção, exclusão ou edição
 /// </summary>
 /// <param name="sql">o sql a ser executado</param>
 public void ExecutaComando(string sql)
 { 
     //Usa a classe de conexão apenas nesse bloco
     using(OdbcConnection conn = new OdbcConnection(this.ConnectionString))
     {
         try
         {   
             //Abre conexão com o servidor de banco
             conn.Open();
             //instancia um objeto de comando, passando como parametro o sql e a conexão para o construtor
             OdbcCommand comm = new OdbcCommand(sql, conn);
             //Executa o comando
             comm.ExecuteNonQuery();
         }
         catch (Exception ex)
         {   
             //Atira uma nova excessão, caso dê algum erro
             throw new Exception(ex.Message);
         }
         finally
         {   
             //Sempre fechará conexão, independente se der erro ou não
             conn.Close();
         }
     }
 }

        public DataSet OpenCsv(string strFilePath)
        {
            if (!File.Exists(strFilePath))
            {
                return null;
            }
            string strFolderPath = Path.GetDirectoryName(strFilePath);
            string strCSVFile = Path.GetFileName(strFilePath);

            DataSet ds = null;
            string strConnection = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + strFolderPath + ";Extensions=asc,csv,tab,txt;Persist Security Info=False";
            try
            {
                using (OdbcConnection conn = new OdbcConnection(strConnection.Trim()))
                {
                    conn.Open();
                    string strSql = "select * from [" + strCSVFile + "]";
                    OdbcDataAdapter odbcDAdapter = new OdbcDataAdapter(strSql, conn);
                    ds = new DataSet();
                    odbcDAdapter.Fill(ds, "table");
                    //ds.Tables[0].Rows[0]["zh"] = "12345678901234567890";
                    conn.Close();
                }
                return ds;
            }
            catch (Exception e)
            {
                throw e;
            }
            return ds;
        }

        public void LoadDB()
        {
	        _MCDB = new DataSet();
	        try
	        {
		        // Creates and opens an ODBC connection
                String strConnString = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + _DBPath.Trim() + ";Extensions=asc,csv,tab,txt;Persist Security Info=False;HDR=NO;";
                String sql_select;
		        OdbcConnection conn;
		        conn = new OdbcConnection(strConnString.Trim());
		        conn.Open();

		        //Creates the select command text
                sql_select = "select * from [" + this._DBFileName.Trim() + "]";

		        //Creates the data adapter
		        OdbcDataAdapter obj_oledb_da = new OdbcDataAdapter(sql_select, conn);
				
		        //Fills dataset with the records from CSV file
		        obj_oledb_da.Fill(_MCDB, "csv");

                
                //closes the connection
		        conn.Close();
	        }
	        catch (Exception e) //Error
	        {
                Console.WriteLine("Error in: " + e.ToString());
	        }
        }