C# OracleConnection.CreateCommand方法代码示例

 private void button1_Click(object sender, EventArgs e)
 {
     try
     {
         OracleConnection OrclConn = new OracleConnection(DataAccess.OIDSConnStr);
         OrclConn.Open();
         OracleDataAdapter OrclNestAdapter = new OracleDataAdapter();
         OracleCommand OrclNestCmd = OrclConn.CreateCommand();
         OracleCommand OrclCmd = OrclConn.CreateCommand();
         //OracleCommandBuilder myCommandBuilder = new OracleCommandBuilder(mOrclAdapter);
         //mOrclAdapter.Update(MyDataSet);
         //conn.Close();
         //for each row and each spool , insert into single_inventory_table
         for (int i = 0; i < InventoryTable.Rows.Count; i++)
         {
             string pipetype = InventoryTable.Rows[i]["类型"].ToString();
             Int32 pipelength = Convert.ToInt32(InventoryTable.Rows[i]["长度"].ToString());
             string erpcode = InventoryTable.Rows[i]["ERP编码"].ToString();
             double weight = (Convert.ToDouble(InventoryTable.Rows[i]["重量"].ToString())) / (Convert.ToInt32(InventoryTable.Rows[i]["数量"].ToString()));
             string batch_no = InventoryTable.Rows[i]["炉批号"].ToString();
             for (int j = 0; j < Convert.ToInt32(InventoryTable.Rows[i]["数量"].ToString()); j++)
             {
                 //insert into oracle
                 OrclCmd.CommandText = "INSERT INTO pipe_inventory_single_spool (PIPETYPE,PIPELENGTH,ERPCODE,WEIGHT,BATCH_NO,STATE) VALUES ('" + pipetype + "'," + pipelength + ",'" + erpcode + "'," + weight + ",'" + batch_no + "',1)";
                 OrclCmd.ExecuteNonQuery();
             }
         }
     }
     catch (Exception err)
     {
         MessageBox.Show(err.Message);
     }
     MessageBox.Show("更新成功!");
 }

        public void createMember(string firstname, string lastname, string address, string city, string username,
            string password, string email, string picture) //insert member
        {
            String oradb = "Data Source= cp3dbinstance.c4pxnpz4ojk8.us-east-1.rds.amazonaws.com:1521/cp3db;User Id=sw1;Password=CampBand4;";
            OracleConnection conn = new OracleConnection(oradb);
            try
            {

                String strSQL = "INSERT INTO MEMBERS VALUES(Members_seq.nextval, '" + firstname + "', '" + lastname + "', '" + address + "', '" + city + "', '" + username + "', '" + password + "', '" + picture + "', '" + email + "', '" + DateTime.Now.ToString("dd-MMM-yyyy") + "' )";
                conn.Open();

                OracleCommand cmd = conn.CreateCommand();
                cmd.CommandText = strSQL;
                cmd.ExecuteNonQuery();
                conn.Close();
                MessageBox.Show("Welcome to BandCamp!");
                registered = true;
            }
            catch (OracleException ex)
            {
                registered = false;
                MessageBox.Show("Something went wrong when accessing the database.");
                MessageBox.Show("Registration was unsuccessful");
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }

        }

        public static bool AddFeature(string usrid,string usrname,string password,int privilege)
        {
            StringBuilder sb = new StringBuilder();
            using (OracleConnection conn = new OracleConnection(OraHelper.str))
            {
                conn.Open();
                OracleCommand comm = conn.CreateCommand();
                OracleParameter[] parms = new OracleParameter[]{
                    new OracleParameter(":USRID",OracleType.VarChar),
                    new OracleParameter(":USRNAME",OracleType.VarChar),
                    new OracleParameter(":PASSWORD",OracleType.VarChar),
                    new OracleParameter(":PRIVILEGE",OracleType.UInt32)
                };
                parms[0].Value = usrid;
                parms[1].Value = usrname;
                parms[2].Value = password;
                parms[3].Value = privilege;

                comm.CommandText = SQL_INSERT_USER;
                comm.CommandType = CommandType.Text;
                foreach (OracleParameter parm in parms)
                {
                    comm.Parameters.Add(parm);

                }
                comm.ExecuteNonQuery();
                conn.Close();
                return true;
            }
        }

 /// <summary>
 /// 填充combox
 /// </summary>
 /// <param name="cb"></param>
 /// <param name="sql"></param>
 private void FillComb(ComboBox cb,string sql)
 {
     try
     {
         OracleConnection conn = new OracleConnection(DataAccess.OIDSConnStr);
         conn.Open();
         OracleCommand cmd = conn.CreateCommand();
         cmd.CommandText = sql;
         OracleDataReader dr = cmd.ExecuteReader();
         if (dr.HasRows)
         {
             cb.Items.Clear();
             cb.Items.Add("");
             while (dr.Read())
             {
                 cb.Items.Add(dr[0].ToString());
             }
             conn.Close();
             dr.Close();
         }
     }
     catch (OracleException ox)
     {
         MessageBox.Show(ox.Message.ToString());
         return;
     }
 }

        protected void Page_Load(object sender, EventArgs e)
        {
            string guid = string.Empty;
            guid = Request["q"];
            int ww = Convert.ToInt32(Request["w"]);
            int wh = Convert.ToInt32(Request["h"]);
            if (guid == null || guid == string.Empty)
            {

                this.map.InnerHtml = "<h1 style='font-size:24px;text-align:center;'>输入参数无效</h1>";
                Response.End();

            }
            else
            {
                using (OracleConnection conn = new OracleConnection(OraHelper.str))
                {
                    conn.Open();
                    OracleCommand comm = conn.CreateCommand();
                    comm.CommandText = string.Format(SQL_SELECT, guid);
                    OracleDataReader dr = comm.ExecuteReader();
                    if (dr.Read())
                    {
                        string innerHTML = dr["DATA"].ToString();
                        this.map.InnerHtml = innerHTML;
                        this.map.Style.Value = "display:block;width:" + ww + "px;height:" + wh + "px;";
                    }
                    else
                    {
                        this.map.InnerHtml = "<h1 style='font-size:24px;text-align:center;'>该分享不存在</h1>";
                    }
                }
            }
        }

        public static string GetFeature(string p_key, int p_size, int p_page, double p_left, double p_bottom, double p_right,double p_top )
        {
            StringBuilder sb = new StringBuilder();
            using (OracleConnection conn = new OracleConnection(OraHelper.str))
            {
                conn.Open();
                OracleCommand comm = conn.CreateCommand();
                comm.CommandText = string.Format(SQL_RECTANGLE_KEYWORD_TOTAL, p_size,p_left,p_right,p_bottom,p_top,p_key);
                object total = comm.ExecuteScalar();

                comm.CommandText = string.Format(SQL_RECTANGLE_KEYWORD, p_key,p_left, p_right,p_bottom,p_top,p_page * p_size,(p_page-1)*p_size);
                OracleDataReader rdr = comm.ExecuteReader();
                string str = Reader2JSON.ToJSON(rdr);

                sb.Append("{total:");
                sb.Append(total);
                sb.Append(",page:");
                sb.Append(p_page);
                sb.Append(",content:");
                sb.Append(str);
                sb.Append("}");

                conn.Close();

            }

            return sb.ToString();
        }

        public void ProcessRequest(HttpContext context)
        {
            string htmltext = string.Empty;
            string guid = string.Empty;
            int ww;
            int wh;

            htmltext = context.Request["HTML"];
            ww = Convert.ToInt32(context.Request["ww"]);
            wh = Convert.ToInt32(context.Request["wh"]);
            guid = Guid.NewGuid().ToString();

            using (OracleConnection conn = new OracleConnection(context.Cache["ORASTR"].ToString()))
            {
                conn.Open();
                OracleCommand comm = conn.CreateCommand();
                OracleParameter[] parms = new OracleParameter[]{
                    new OracleParameter(":GUID",OracleType.VarChar),
                    new OracleParameter(":DATA",OracleType.NClob),
                };
                parms[0].Value = guid;
                parms[1].Value = htmltext;

                comm.CommandText = SQL_INSERT;
                comm.CommandType = CommandType.Text;
                foreach (OracleParameter parm in parms)
                {
                    comm.Parameters.Add(parm);

                }
                comm.ExecuteNonQuery();
                string url = context.Request.Url.AbsoluteUri.Substring(0, context.Request.Url.AbsoluteUri.IndexOf("SVR/ShareMap.ashx"));
                context.Response.Write("window.open(\'"+url+"Delegate.aspx?q="+guid+"&w="+ww+"&h="+wh+"\')");
            }
        }

 public static void FillTreeView(TreeNode node, string sql)
 {
     try
     {
         OracleConnection conn = new OracleConnection(DataAccess.OIDSConnStr);
         conn.Open();
         OracleCommand cmd = conn.CreateCommand();
         cmd.CommandText = sql;
         OracleDataReader dr = cmd.ExecuteReader();
         if (dr.HasRows)
         {
             while (dr.Read())
             {
                 node.Nodes.Add(dr[0].ToString());
             }
             conn.Close();
             dr.Close();
         }
     }
     catch (OracleException ox)
     {
         MessageBox.Show(ox.Message.ToString());
         return;
     }
 }

        public static bool DeckIsRefD(int Deck_id)
        {
            try
            {
                OracleConnection OraCon = new OracleConnection("Data Source=oids;User ID=plm;Password=123!feed;Unicode=True");
                OraCon.Open();

                OracleDataAdapter OrclPrjAdapter = new OracleDataAdapter();
                OracleCommand OrclPrjCmd = OraCon.CreateCommand();
                OrclPrjAdapter.SelectCommand = OrclPrjCmd;
                OrclPrjCmd.CommandText = @"SELECT T.TABLE_NAME,T.COLUMN_NAME FROM use_deckid_tables_view T";
                DataSet Mydata = new DataSet();
                OrclPrjAdapter.Fill(Mydata);
                DataSet tmpData = new DataSet();
                string QueryPrjIdCmdStr = string.Empty;
                for (int i = 0; i < Mydata.Tables[0].Rows.Count; i++)
                {
                    QueryPrjIdCmdStr = @"SELECT T." + Mydata.Tables[0].Rows[i][1] + " FROM " + Mydata.Tables[0].Rows[i][0] + " T WHERE TO_CHAR(T." + Mydata.Tables[0].Rows[i][1] + ")=TO_CHAR(" + Deck_id + ")";
                    OrclPrjCmd.CommandText = QueryPrjIdCmdStr;
                    OrclPrjAdapter.Fill(tmpData);
                    if (tmpData.Tables[0].Rows.Count > 0)
                        return true;
                }

                return false;
            }
            catch (Exception err)
            {
                System.Console.WriteLine(err.Message);
                return true;
            }
        }

        public static string GetFeature(string p_key, int p_size, int p_page)
        {
            StringBuilder sb = new StringBuilder();
            using (OracleConnection conn = new OracleConnection(OraHelper.str))
            {
                conn.Open();
                OracleCommand comm = conn.CreateCommand();
                comm.CommandText = string.Format(SQL_SEARCH_STATION_TOTAL, p_size, p_key);
                object total = comm.ExecuteScalar();

                comm.CommandText = string.Format(SQL_SEARCH_STATION, p_key, p_page * p_size, (p_page - 1) * p_size);
                OracleDataReader rdr = comm.ExecuteReader();
                string str = Reader2JSON.ToJSON(rdr);

                sb.Append("{\"total\":");
                sb.Append(total);
                sb.Append(",\"page\":");
                sb.Append(p_page);
                sb.Append(",\"content\":");
                sb.Append(str);
                sb.Append("}");

                conn.Close();
            }
            return sb.ToString();
        }

        /// <summary>
        /// 测试 调用 Oracle 返回结果集的函数.
        /// </summary>
        private void CallFuncWithTable(OracleConnection conn)
        {
            // 创建一个 Command.
            OracleCommand testCommand = conn.CreateCommand();

            // 定义需要执行的SQL语句.
            testCommand.CommandText = "pkg_HelloWorld.getHelloWorld";

            // 定义好，本次执行的类型，是存储过程.
            testCommand.CommandType = CommandType.StoredProcedure;

            // 定义好，我这个参数，是 游标 + 返回值.
            OracleParameter para = new OracleParameter("c", OracleType.Cursor);
            para.Direction = ParameterDirection.ReturnValue;
            testCommand.Parameters.Add(para);


            // 执行SQL命令，结果存储到Reader中.
            OracleDataReader testReader = testCommand.ExecuteReader();

            // 处理检索出来的每一条数据.
            while (testReader.Read())
            {
                // 将检索出来的数据，输出到屏幕上.
                Console.WriteLine("调用函数:{0}; 返回:{1} - {2}",
                    testCommand.CommandText, testReader[0], testReader[1]
                    );
            }

            // 关闭Reader.
            testReader.Close();
        }

        public static void GetFlowStatus(ComboBox cbox, string sql)
        {
            OracleConnection conn = new OracleConnection(DataAccess.OIDSConnStr);
            try
            {
                //OracleConnection conn = new OracleConnection(DataAccess.OIDSConnStr);
                conn.Open();
                OracleCommand cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                OracleDataReader datareader = cmd.ExecuteReader();

                while (datareader.Read())
                {
                    cbox.Items.Add(datareader[0].ToString());
                }

                datareader.Close();
                //conn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message.ToString());
                //return;
            }
            finally { conn.Close(); }
        }

        public static bool EditFeature(string labelid,string title,string content)
        {
            StringBuilder sb = new StringBuilder();
            using (OracleConnection conn = new OracleConnection(OraHelper.str))
            {
                conn.Open();
                OracleCommand comm = conn.CreateCommand();
                OracleParameter[] parms = new OracleParameter[]{
                    new OracleParameter(":LABELID",OracleType.VarChar),
                    new OracleParameter(":TITLE",OracleType.VarChar),
                    new OracleParameter(":CONTENT",OracleType.VarChar)
                };
                parms[0].Value = labelid;
                parms[1].Value = title;
                parms[2].Value = content;

                comm.CommandText = SQL_UPDATE_LABEL;
                comm.CommandType = CommandType.Text;
                foreach (OracleParameter parm in parms)
                {
                    comm.Parameters.Add(parm);

                }
                comm.ExecuteNonQuery();
                conn.Close();
                return true;
            }
        }

        /// <summary>
        /// 将生成的材料信息更新到数据库中
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="excelinfo"></param>
        public static void UpdateExcelInfo(string sql,byte[] excelinfo)
        {
            try
            {
                byte[] file = excelinfo;
                using (OracleConnection conn = new OracleConnection(DataAccess.OIDSConnStr))
                {
                    conn.Open();
                    using (OracleCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = sql;
                        OracleParameter op = new OracleParameter("dfd", OracleType.Blob);
                        op.Value = file;
                        if (file.Length == 0)
                        {
                            MessageBox.Show("插入信息表不能为空！", "WARNNING", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                            return;
                        }
                        else
                        {
                            cmd.Parameters.Add(op);
                            cmd.ExecuteNonQuery();
                        }
                    }
                    conn.Close();
                }

            }
            catch (IOException ee)
            {
                MessageBox.Show(ee.Message.ToString());
                return;
            }
        }

        private void btnAlter_Click(object sender, EventArgs e)
        {
            bool check = true;
            if (this.comboxSL.Text == "")
            { check = false; MessageBox.Show("税率没有选择"); }
            else if (this.ComboxJSFS.Text == "")
            { check = false; MessageBox.Show("结算方式没有选择"); }
            else if (this.txtGHDW.Text == "")
            { check = false; MessageBox.Show("单位没有选择"); }
            else
            { check = true; }
            if (check == true)
            {
                using (OracleConnection connection = new OracleConnection(StrCon))
                {

                    try
                    {
                        connection.Open();
                        OracleCommand cmd = connection.CreateCommand();
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.CommandText = "Jt_C_XSJSD_update";
                        cmd.Parameters.Add("LS_XSJSDid", OracleType.VarChar).Value = this.txtJSDH.Tag.ToString();
                        cmd.Parameters.Add("ls_GHDWID", OracleType.VarChar).Value = this.txtGHDW.Tag.ToString();
                        cmd.Parameters.Add("ls_JSR", OracleType.VarChar).Value = this.txtJSR.Text.ToString().Trim();
                        cmd.Parameters.Add("ls_JSFSID", OracleType.VarChar).Value = GetJSFSID(this.ComboxJSFS.Text.ToString().Trim());
                        cmd.Parameters.Add("ls_SL", OracleType.VarChar).Value = this.comboxSL.Text.ToString().Trim();
                        cmd.Parameters.Add("ls_ZT", OracleType.VarChar).Value = "02";
                        cmd.Parameters.Add("ls_BZ", OracleType.VarChar).Value = this.txtBZ.Text.ToString().Trim();
                        cmd.Parameters.Add("DescErr", OracleType.VarChar, 255).Direction = ParameterDirection.Output;
                        cmd.Parameters.Add("Message", OracleType.VarChar, 255).Direction = ParameterDirection.Output;

                        cmd.ExecuteNonQuery();
                        xpServerCollectionSource1.FixedFilterString = "[XSJSDID] = \'" + this.txtJSDH.Tag.ToString() + "\'";
                        unitOfWork1.DropIdentityMap();
                        xpServerCollectionSource1.Reload();
                        gridView1.BestFitColumns();
                        MessageBox.Show(cmd.Parameters["DescErr"].Value.ToString() + cmd.Parameters["Message"].Value.ToString());

                    }
                    catch (OracleException ex)
                    {
                        MessageBox.Show(ex.Message);
                    }
                    finally
                    {
                        connection.Close();
                        this.btnDWselect.Enabled = false;
                        this.comboxSL.Enabled = false;
                        this.ComboxJSFS.Enabled = false;
                        this.txtJSR.ReadOnly = true;
                        this.txtBZ.ReadOnly = true;
                        this.txtZT.Text = "存盘";
                        this.txtZT.Tag = "02";
                    }
                }

            }
        }