C# Odbc.OdbcCommand类代码示例

        /// <summary>
        /// ��Ʈw�s�W��k
        /// </summary>
        /// <param name="insert"></param>
        public void insert(ICommand insert)
        {
            string myCmd = insert.getCommand();
            try
            {
                cmd = new OdbcCommand(myCmd, GetConn());
                //cmd = new IBM.Data.DB2.DB2Command(myCmd, GetConn());
                cmd.ExecuteNonQuery();
            }
            catch (OdbcException dobcEx)
            {
                if (dobcEx.ErrorCode == -2146232009)
                {
                    return;
                }

                try
                {
                    lock (typeof(OdbcConnection))
                    {
                        //cmd = new IBM.Data.DB2.DB2Command(myCmd, GetConn());
                        cmd = new OdbcCommand(myCmd, GetConn());
                        cmd.ExecuteNonQuery();
                    }
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
            }
        }

    public static List<Auspiciante> getAllAuspiciantes()
    {
        OdbcConnection con = ConexionBD.ObtenerConexion();
        DataSet ds = new DataSet();
        List<Auspiciante> listaAuspiciantes = new List<Auspiciante>();
        try
        {
            OdbcCommand cmd = new OdbcCommand("SELECT a.id, a.imagen FROM auspiciante a", con);
            cmd.CommandType = CommandType.Text;
            OdbcDataReader dr = cmd.ExecuteReader();

            while (dr.Read())
            {
                Auspiciante a = new Auspiciante();
                a.IdAuspiciante = dr.GetInt32(dr.GetOrdinal("id"));
                a.ImagenAuspiciante = ImagenDAL.getImagen(con,dr.GetInt32(dr.GetOrdinal("imagen")));

                listaAuspiciantes.Add(a);
            }
        }
        catch (Exception e)
        {
            throw new SportingException("Ocurrio un problema al intentar obtener los auspiciantes. " + e.Message);
        }
        return listaAuspiciantes;
    }

 public int inserir(Voos voo)
 {
     OdbcCommand comando;
     int result = 0;
     String comand = "INSERT INTO voo(codigo, data, origem, destino) VALUES(?, ?, ?, ?)";
     OdbcParameter codigo = new OdbcParameter("?", voo.Codigo);
     OdbcParameter data = new OdbcParameter("?", voo.Data);
     OdbcParameter origem = new OdbcParameter("?", voo.Origem);
     OdbcParameter destino = new OdbcParameter("?", voo.Destino);
     try
     {
         comando = new OdbcCommand(comand, conexao);
         comando.Connection.Open();
         comando.Parameters.Add(codigo);
         comando.Parameters.Add(data);
         comando.Parameters.Add(origem);
         comando.Parameters.Add(destino);
         result = comando.ExecuteNonQuery();
     }
     catch (Exception e)
     {
         MessageBox.Show(e.Message);
     }
     finally
     {
         conexao.Close();
     }
     return result;
 }

        /// <summary>
        /// Use the Username property to fill in the remainder of the fields if the User exists in the database.
        /// </summary>
        /// <returns>True, if the record was found. Else the return value will be false.</returns>
        public bool Find()
        {
            string mySelectQuery = "select AES_DECRYPT(Password, 'key'), idNumber, UserClass from User where Username = '" + m_Username + "'";

            OdbcConnection myConnection = GetConnection();
            OdbcCommand myCommand = new OdbcCommand(mySelectQuery, myConnection);

            myConnection.Open();

            bool recordFound = false;
            OdbcDataReader myReader;
            myReader = myCommand.ExecuteReader(CommandBehavior.SingleRow);
            try
            {
                if (myReader.Read())
                {
                    m_Password = myReader.GetString(0);
                    m_idNumber = myReader.GetInt32(1);
                    m_UserClass = myReader.GetString(2);
                    recordFound = true;
                }
            }
            finally
            {
                myReader.Close();
                myConnection.Close();
            }

            return recordFound;
        }

		public OdbcDataAdapter (OdbcCommand selectCommand) 
		{
			DeleteCommand = null;
			InsertCommand = null;
			SelectCommand = selectCommand;
			UpdateCommand = null;
		}

    /// <summary>
    /// Setea la lista de imagenes de una noticia
    /// </summary>
    /// <returns></returns>
    public static List<Imagen> getImagenes(Noticia noticia, OdbcConnection con)
    {
        List<Imagen> listaImagenes = new List<Imagen>();
        OdbcDataReader dr = null;

        String query = "SELECT i.id, i.pathBig, i.pathSmall, i.portada, i.pathMedium FROM imagen i, imagen_x_noticia n WHERE i.id=n.idImagen AND n.idNoticia=" + noticia.IdNoticia;

        try
        {
            OdbcCommand cmd = new OdbcCommand(query, con);
            cmd.CommandType = CommandType.Text;
            dr = cmd.ExecuteReader();

            while (dr.Read())
            {
                Imagen imagen = new Imagen();
                imagen.IdImagen = dr.GetInt32(0);
                imagen.PathBig = dr.GetString(1);
                imagen.PathSmall = dr.GetString(2);
                imagen.Portada = dr.GetBoolean(3);
                imagen.PathMedium = dr.GetString(4);
                listaImagenes.Add(imagen);
            }
        }
        catch (Exception e)
        {
            throw new SportingException("Ocurrio un problema al intentar obtener las imagenes de las noticias. " + e.Message);
        }

        return listaImagenes;
    }

    public static DataTable getDataTableImagenes(int id)
    {
        OdbcConnection con = ConexionBD.ObtenerConexion();
        DataSet ds = new DataSet();
        List<Noticia> listaNoticias = new List<Noticia>();
        DataTable dataTable = null;
        try
        {
            OdbcCommand cmd = new OdbcCommand("SELECT i.pathBig, i.pathSmall, i.pathMedium FROM imagen_x_noticia ixn, imagen i" +
            " WHERE ixn.idNoticia = " + id + " AND i.id = ixn.idImagen", con);
            cmd.CommandType = CommandType.Text;

            dataTable = new DataTable();
            OdbcDataAdapter adapter = new OdbcDataAdapter();
            adapter.SelectCommand = cmd;
            adapter.Fill(dataTable);
        }
        catch (Exception e)
        {
            throw new SportingException("Ocurrio un problema al intentar obtener las imagenes de las noticias. " + e.Message);
        }
        finally
        {
            con.Close();
        }
        return dataTable;
    }

 public Database(string connectionString)
 {
     OC = new OdbcConnection(connectionString);
     dbCommand = new OdbcCommand();
     dbCommand.Connection = OC;
     OC.Open();
 }

    //delete the record
    public bool delete_record(string id, string date, string warn_code, string level)
    {
        bool stat = false;
        try
        {
            //set up new connection
            OdbcConnection con = getconnection();
            //open the setup coneection
            con.Open();
            //set command
            OdbcCommand cmd = new OdbcCommand("delete  from student_warning  where ADMISSION_NO='" + id + "' and WARN_DATE='" + date + "' and WARNING_CODE='" + warn_code + "' and LEVEL_CODE='" + level + "'", con);
            //execute the command
            cmd.ExecuteNonQuery();
            //set status as true
            stat = true;
            //close the connection
            con.Close();
        }
        catch (Exception ss)
        {
            string s = ss.Message;
            stat = false;

        }
        return stat;
    }

    protected void Button1_Click(object sender, EventArgs e)
    {
        string[] strArray = new string[5] { "Delhi", "Mumbai", "Kolkata", "Chennai", "Chandigarh" };
        foreach (string str in strArray)
        {
           OdbcConnection oledbConn = new OdbcConnection("DSN=exceldb");
        try
        {
            // Open connection
            oledbConn.Open();

            // Create OleDbCommand object and select data from worksheet Sheet1
            OdbcCommand cmd = new OdbcCommand("SELECT * FROM [Details$]", oledbConn);
            //cmd.Parameters.AddWithValue("@city",str);where [email protected]
            // Create new OleDbDataAdapter
            OdbcDataAdapter oleda = new OdbcDataAdapter();

            oleda.SelectCommand = cmd;

            // Create a DataSet which will hold the data extracted from the worksheet.
            // DataSet ds = new DataSet();
            DataTable dt = new DataTable();

            // Fill the DataSet from the data extracted from the worksheet.
            oleda.Fill(dt);
            /*      if(dt.Rows.Count>0)
                  {
                       ID = dt.Rows[0]["ID"].ToString(); //Where ColumnName is the Field from the DB that you want to display
                       name= dt.Rows[0]["Name"].ToString();
                       Address = dt.Rows[0]["Address"].ToString();
                       EmailAddress = dt.Rows[0]["emailaddress"].ToString();
                       if(EmailAddress!=null)
                       {
                        if(EmailAddress==)
                       }
                       EmailContent = dt.Rows[0]["emailcontent"].ToString();
                   }
              */
            DataView dv = new DataView(dt);
            // dv.Sort = "emailcontents";
            // dv.Sort = "Name";

            // Bind the data to the GridView
            Grdexcel.DataSource = dv;
            Grdexcel.DataBind();
            // cmd = new OdbcCommand("delete FROM [ter$] where Address like 'M%'", oledbConn);
            // cmd.ExecuteNonQuery();
            //cmd.CommandType
        }
        catch (Exception ex)
        {
            Alert.Show("Sorry");
        }
        finally
        {
            // Close connection
            oledbConn.Close();
        }
        }
    }

        private String addUser()
        {
            try
            {
                Contract contract = new Contract();
                String cxnString = "Driver={SQL Server};Server=HC-sql7;Database=REVINT;Trusted_Connection=yes;";
                using (OdbcConnection dbConnection = new OdbcConnection(cxnString))
                {
                    //open OdbcConnection object
                    dbConnection.Open();

                    OdbcCommand cmd = new OdbcCommand();

                    cmd.CommandText = "{CALL [REVINT]." + contract.getSchema() + ".[OCP_addUser]( ?, ?, ?, ? )}";
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.Connection = dbConnection;

                    cmd.Parameters.Add("@hawkId", OdbcType.NVarChar, 400).Value = txtHealthcareID.Text;
                    cmd.Parameters.Add("@administrator", OdbcType.Bit).Value = chkAdmin.IsChecked;
                    cmd.Parameters.Add("@name", OdbcType.NVarChar, 400).Value = txtName.Text;
                    cmd.Parameters.Add("@numRecords", OdbcType.Int);
                    cmd.Parameters["@numRecords"].Direction = System.Data.ParameterDirection.ReturnValue;

                    cmd.ExecuteNonQuery();

                    dbConnection.Close();

                    return cmd.Parameters["@numRecords"].Value.ToString();
                }
            }
            catch (Exception)
            {
                return null;
            }
        }

    public bool AddTermCode(TermCode termCode)
    {
        bool result = false;

        using (OdbcConnection connection = new OdbcConnection(connectionString))
        {
            using (OdbcCommand command = new OdbcCommand())
            {
                command.Connection = connection;
                command.CommandText = "{CALL TermCode_insert(?,?)}";
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.AddWithValue("@AssessmentOID", termCode.AssessmentOID);
                command.Parameters.AddWithValue("@TermCodeName", termCode.TermCodeName);
                connection.Open();
                int n = command.ExecuteNonQuery();
                if (n > 0)
                    result = true;
                else
                    result = false;
            }
        }

        return result;
    }

    protected void Button1_Click1(object sender, EventArgs e)
    {
        int i;
        string htmlstr = "<table style='width:100%;'>";
        string data;
        string sql = TextBox1.Text;
        string constr = Session["connection"].ToString();
        OdbcConnection cn = new OdbcConnection(constr);
        cn.Open();
        OdbcCommand cmd = new OdbcCommand(sql, cn);
        OdbcDataReader reader;
        reader = cmd.ExecuteReader();
        int col_count = reader.FieldCount;
        int count1;

        while (reader.Read())
        {
            htmlstr += "<tr>";
            count1 = col_count;
            i = 0;
            while (count1 != 0)
            {
                data = reader[i].ToString();
                htmlstr += "<td class='style2'>" + data + "</td>";
                count1--;
                i++;
            }
            htmlstr += "</tr>";
        }
        htmlstr += "</table>";
        table_data.InnerHtml = htmlstr;
    }

        public static void Load_Sport_GR_TB_TXT_TO_DB(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);
                    string line = null;
                    while ((line = sr.ReadLine()) != null)
                    {

                        string[] str_a = line.Split(',');
                        if (str_a.Length > 5)
                        {
                            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();
         }
     }
 }