ORACLEADAPTER

youzi

[C#] 纯文本查看 复制代码

using System;
using System.Data;
using Oracle.DataAccess.Client;
using System.Web.Configuration;

namespace oraModify
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            try
            {
                String connectionString = WebConfigurationManager.ConnectionStrings["ConnectionString1"].ConnectionString;
                OracleConnection con = new OracleConnection(connectionString);
                con.Open();

                //// Create the command
                OracleCommand cmdSelect = new OracleCommand("", con);

                // sql文
                OracleCommand cmdUpdate = new OracleCommand("", con);

                // sql文
                OracleCommand cmdDelete = new OracleCommand("", con);

                // sql文
                OracleCommand cmdInsert = new OracleCommand("", con);

                //select statement
                string strSelectSql = "SELECT * FROM TBUSER WHERE  LOGINID = :I_LOGINID ";
                string strUpdateSql = "UPDATE TBUSER SET EFFECTSTRT = :I_EFFECTSTRT  WHERE  LOGINID = :I_LOGINID ";
                //string strDeleteSql = "DELETE FROM TBUSER  WHERE  LOGINID = :I_LOGINID";
                //string strInsertSql = "INSERT TB_USER(LOGINID,EFFECTSTRT,EFFECTEND) VALUES (:I_LOGINID, :I_EFFECTSTRT,:I_EFFECTEND )";

                //command和检索sql文 关联
                cmdSelect.CommandText = strSelectSql;
                cmdUpdate.CommandText = strUpdateSql;
                //cmdDelete.CommandText = strDeleteSql;
                //cmdInsert.CommandText = strInsertSql;

                //sql文中变量通过oracle参数传递
                OracleParameter oraParameter;

                //当设置参数的时候
                //oracle参数值可通过SourceColumn属性与更新数据行的字段绑定
                //取得数据值的版本可通过SourceVersion来设定。
                //DataRowVersion.Current 取得数据行中修改后的数据值
                //DataRowVersion.Original 取得数据行中原始的数据值
                //Where条件里面的参数值往往设置为取DataRowVersion.Original版本
                //更新或者插入的参数值往往设置为取DataRowVersion.Current版本

                //--------------------------------------------------
                //取得用的oracle command
                oraParameter = new OracleParameter("I_LOGINID", OracleDbType.Varchar2);
                oraParameter.SourceColumn = "LOGINID";//检索字段值
                oraParameter.SourceVersion = DataRowVersion.Current;
                oraParameter.Value = "60";
                cmdSelect.Parameters.Add(oraParameter);//字符型
                //--------------------------------------------------
                //更新用的oracle command
                oraParameter = new OracleParameter("I_EFFECTSTRT", OracleDbType.Date);
                oraParameter.SourceColumn = "EFFECTSTRT";//更新字段值
                oraParameter.SourceVersion = DataRowVersion.Current;
                oraParameter.Value = Convert.ToDateTime("2010/02/14").Date;
                cmdUpdate.Parameters.Add(oraParameter);

                oraParameter = new OracleParameter("I_LOGINID", OracleDbType.Varchar2);
                oraParameter.SourceColumn = "LOGINID";//检索字段值
                oraParameter.SourceVersion = DataRowVersion.Original;
                oraParameter.Value = "60";
                cmdUpdate.Parameters.Add(oraParameter);
                ////--------------------------------------------------
                ////删除用的oracle command
                //oraParameter = new OracleParameter("I_LOGINID", OracleDbType.Varchar2);
                //oraParameter.SourceColumn = "LOGINID";//检索字段值
                //oraParameter.SourceVersion = DataRowVersion.Original;
                //oraParameter.Value = "50";
                //cmdDelete.Parameters.Add(oraParameter);

                ////--------------------------------------------------
                ////追加用的oracle command
                //oraParameter = new OracleParameter("I_LOGINID", OracleDbType.Varchar2);
                //oraParameter.SourceColumn = "LOGINID";//追加字段值
                //oraParameter.SourceVersion = DataRowVersion.Current;
                //oraParameter.Value = "70";
                //cmdInsert.Parameters.Add(oraParameter);

                //oraParameter = new OracleParameter("I_EFFECTSTRT", OracleDbType.Varchar2);
                //oraParameter.SourceColumn = "EFFECTSTRT";//追加字段值
                //oraParameter.Value = "2010/02/14";
                //oraParameter.SourceVersion = DataRowVersion.Current;
                //cmdInsert.Parameters.Add(oraParameter);

                //oraParameter = new OracleParameter("I_EFFECTEND", OracleDbType.Varchar2);
                //oraParameter.SourceColumn = "EFFECTEND";//追加字段值
                //oraParameter.Value = "2010/02/14";
                //oraParameter.SourceVersion = DataRowVersion.Current;
                //cmdInsert.Parameters.Add(oraParameter);

                using (OracleDataAdapter dataAdapter = new OracleDataAdapter())
                {
                    int nRecCount = 0;
                    DataSet i_Data = new DataSet();

                    dataAdapter.SelectCommand = cmdSelect;
                    dataAdapter.Fill(i_Data, "TB_USER");
                    //dataAdapter.InsertCommand = cmdInsert;//追加command设置
                    dataAdapter.UpdateCommand = cmdUpdate;//更新command设置
                    //dataAdapter.DeleteCommand = cmdDelete;//删除command设置
                    nRecCount = dataAdapter.Update(i_Data, "TB_USER");//数据更新
                    //this.GridView1.DataSource = i_Data;
                    //GridView1.DataBind();//将控件及其所有子控件绑定到指定的数据源
                }
                // Close and Dispose OracleConnection object
                con.Close();
                con.Dispose();

            }
            catch (Exception ex)
            {

                throw ex;
            }


        }
    }
}