关于在asp.net中使用数据库的临时表.

最新推荐文章于 2021-11-29 15:54:35 发布

stlwj

最新推荐文章于 2021-11-29 15:54:35 发布

阅读量2.7k

点赞数

分类专栏： asp.net 文章标签： asp.net 数据库 object button exception table

本文链接：https://blog.csdn.net/stlwj/article/details/24178

版权

asp.net 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

因为C#的连接是断开式连接,所以在使用数据库临时表是读不出数据的,经过一段时间的探索,终于找出办法:就是先将数据填充到DATATABLE中,然后再使用DataAdapter将DATATABLE的数据上传到临时表,同时在不断开数据连接的前提下,执行其他的SQL语句.

简单例子如下:

private void Page_Load(object sender, System.EventArgs e)
  {
      if(!this.IsPostBack)
      {
          DataTable myda=new DataTable();
          DataColumn dcName=myda.Columns.Add("ID");
          dcName.DataType=Type.GetType("System.String");
          dcName=myda.Columns.Add("CLASSID");
          dcName.DataType=Type.GetType("System.String");
          dcName=myda.Columns.Add("MCLASSID");
          dcName.DataType=Type.GetType("System.String");
          dcName=myda.Columns.Add("MCLASSNAME");
          dcName.DataType=Type.GetType("System.String");
           this.ViewState["table"]=myda;
         }
  }
  private void Button2_Click(object sender, System.EventArgs e)
  {
   DataTable myda=(DataTable)this.ViewState["table"];
   DataRow dr= myda.NewRow();
   dr[0]=this.id.Text;
   dr[1]=this.TextBox3.Text;
   dr[2]=this.mclassid.Text;
   dr[3]=this.mclassname.Text;
   myda.Rows.Add(dr);
   this.DataGrid1.DataSource=myda;
   this.DataGrid1.DataBind();
  }

  private void Button1_Click(object sender, System.EventArgs e)
  {
   DataTable ntable=(DataTable)this.ViewState["table"];

myCn = new OracleConnection(strConn);
myCn.Open();

   myCm=myCn.CreateCommand();
   OracleTransaction trans = myCn.BeginTransaction();
   myCm.Connection=myCn;
   myCm.Transaction=trans;

   try{
    OracleDataAdapter MyAdapter = new OracleDataAdapter();
    OracleTransaction trans = myCn.BeginTransaction();

    MyAdapter.InsertCommand =myCm;
    MyAdapter.InsertCommand.CommandText = "C_NAME_INN";
    MyAdapter.InsertCommand.CommandType=CommandType.StoredProcedure;

    MyAdapter.InsertCommand.Connection = myCn;

    OracleParameter p2 = new OracleParameter("V_ID", OracleType.VarChar,40);
    p2.SourceVersion = DataRowVersion.Current;
    p2.SourceColumn = ntable.Columns[0].ColumnName;
    MyAdapter.InsertCommand.Parameters.Add(p2);

    OracleParameter p1 = new OracleParameter("V_CLASSID", OracleType.VarChar, 40);
    p1.SourceVersion = DataRowVersion.Current;
    p1.SourceColumn = ntable.Columns[1].ColumnName;
    MyAdapter.InsertCommand.Parameters.Add(p1);

    OracleParameter p3 = new OracleParameter("V_MCLASSID", OracleType.VarChar, 40);
    p3.SourceVersion = DataRowVersion.Current;
    p3.SourceColumn = ntable.Columns[2].ColumnName;
    MyAdapter.InsertCommand.Parameters.Add(p3);

    OracleParameter p4 = new OracleParameter("V_MCLASSNAME", OracleType.VarChar, 40);
    p4.SourceVersion = DataRowVersion.Current;
    p4.SourceColumn = ntable.Columns[3].ColumnName;
    MyAdapter.InsertCommand.Parameters.Add(p4);

MyAdapter.Update(ntable);

    myCm.Parameters.Clear();
    myCm.CommandText="ADD_CNAME";
    myCm.CommandType = CommandType.StoredProcedure;
    myCm.ExecuteNonQuery();
    myCm.CommandText="C_NAME_DISPLAY";
    myCm.CommandType = CommandType.StoredProcedure;
    OracleParameter []pa ={ new OracleParameter("returnInfo",OracleType.Cursor)};
    pa[0].Direction = ParameterDirection.Output;
    myCm.Parameters.Add(pa[0]);
    OracleDataReader ds=myCm.ExecuteReader(CommandBehavior.Default);
    this.DataGrid1.DataSource=ds;
    this.DataGrid1.DataBind();
    trans.Commit();
   }
   catch (OracleException er)
   {
    throw new Exception(er.Message);
   }
   finally
   {
    trans.Dispose();
   }
  }