数据库域的动态插入

来源：互联网发布：建立网络舆情引导机制编辑：程序博客网时间：2024/06/05 18:19

稍后解释

protected void ButtonXinZen_Click(object sender, EventArgs e)

{

// 设置传递参数

String fieldParam = "";

for (int fieldCount = 0; fieldCount < ReportsFieldCount ; fieldCount++)

{

fieldParam = fieldParam + ":field" + fieldCount.ToString() + ",";

}

String sqlInsert = "INSERT INTO " + biaoMing + " VALUES(" + fieldParam + ")";

OracleParameter[] parms = new OracleParameter[ReportsFieldCount];

for (int count = 0; count < ReportsFieldCount ; count++)

{

parms[count] = new OracleParameter();

}

// 特殊列的处理

parms[0].ParameterName = ":id";

parms[0].OracleType = OracleType.Char;

parms[0].Size = 32;

parms[0].Value = LabelZhuJian.Text.Trim();

// 编号设置

parms[1].ParameterName = ":bh";

parms[1].OracleType = OracleType.Number;

parms[1].Size = 8;

parms[1].Value = Math.Ceiling(getMaxSeq() + 1.0);

// 从2开始

int i = 2;

GetTableInformation getTableInformation = new GetTableInformation();

// 获取表结构信息

Hashtable tableInfor = getTableInformation.getTableInfor("baobiaoxinxi");

foreach (DictionaryEntry de in tableInfor)

{

String value = de.Value.ToString();

// 移调SN

tableInfor.Remove("SN");

switch (value)

{

case "CHAR":

parms[i].ParameterName = ":field" + i.ToString();

parms[i].OracleType = OracleType.Char;

parms[i].Size = 32;

parms[i].Value = "";

break;

case "VARCHAR2":

parms[i].ParameterName = ":field" + i.ToString();

parms[i].OracleType = OracleType.VarChar;

parms[i].Size = 512;

parms[i].Value = "";

break;

case "NUMBER":

parms[i].ParameterName = ":field" + i.ToString();

parms[i].OracleType = OracleType.Number;

parms[i].Size = 8;

parms[i].Value = 0;

break;

}

parms[ReportsFieldCount - 1].ParameterName = ":sn";

parms[ReportsFieldCount - 1].OracleType = OracleType.Char;

parms[ReportsFieldCount - 1].Size = 32;

parms[ReportsFieldCount - 1].Value = System.Guid.NewGuid().ToString().Replace("-", "");

//// 设置传递参数

//String fieldParam = "";

//for (int fieldCount = 2; fieldCount < ReportsFieldCount - 1; fieldCount++)

//{

// fieldParam = fieldParam + ":field" + fieldCount.ToString() + ",";

//}

//// 插入数据库语句

//String sqlInsert = "INSERT INTO "+ biaoMing + " VALUES(:id,:bh," + fieldParam + ":sn)";

//// 设置参数

//OracleParameter[] parms = new OracleParameter[ReportsFieldCount];

//for (int count = 0; count <= ReportsFieldCount-1 ; count++)

//{

// parms[count] = new OracleParameter();

//}

//// ID设置

//parms[0].ParameterName = ":id";

//parms[0].OracleType = OracleType.Char;

//parms[0].Size = 32;

//parms[0].Value = LabelZhuJian.Text.Trim();

//// 编号设置

//parms[1].ParameterName = ":bh";

//parms[1].OracleType = OracleType.Number;

//parms[1].Size = 8;

//parms[1].Value = Math.Ceiling(getMaxSeq() + 1.0);

//// 其他域设置从第3列开始前面有编辑和删除列

//for (int fieldCount = 2; fieldCount < ReportsFieldCount - 1; fieldCount++)

//{

// parms[fieldCount].ParameterName = ":field" + fieldCount.ToString();

// parms[fieldCount].OracleType = OracleType.Char;

// parms[fieldCount].Size = 32;

// parms[fieldCount].Value = "";

//}

//// 末尾的SN列

//parms[ReportsFieldCount - 1].ParameterName = ":sn";

//parms[ReportsFieldCount - 1].OracleType = OracleType.Char;

//parms[ReportsFieldCount - 1].Size = 32;

//parms[ReportsFieldCount - 1].Value = System.Guid.NewGuid().ToString().Replace("-", "");

OracleConnection conn = new OracleConnection(OracleHelper.DatabaseConnStr);

conn.Open();

OracleTransaction trans = conn.BeginTransaction(IsolationLevel.ReadCommitted);

try

{

OracleHelper.ExecuteNonQuery(trans, CommandType.Text, sqlInsert, parms);

trans.Commit();

}

catch (Exception ex)

{

trans.Rollback();

ex.ToString();

}

finally

{

conn.Close();

}

GridViewTianJia.EditIndex = -1;

RecoveryGridView();

}

动态更新

/// <summary>

/// 行更新

/// </summary>

/// <param name="sender"></param>

/// <param name="e"></param>

protected void GridViewTianJia_RowUpdating(object sender, GridViewUpdateEventArgs e)

{

// 表头按顺序排放

ArrayList tableHead = new ArrayList();

for (int m = 0; m < ReportsFieldCount-1 ; m++)

{ // 3表示去除前面2列 + ID隐藏列

tableHead.Add(GridViewTianJia.HeaderRow.Cells[m + 3].Text);

}

// 设置参数

OracleParameter[] parms = new OracleParameter[ReportsFieldCount - 1];

for (int count = 0; count < ReportsFieldCount - 1; count++)

{

parms[count] = new OracleParameter();

}

GetTableInformation getTableInformation = new GetTableInformation();

// 获取表结构信息

Hashtable tableInfor = getTableInformation.getTableInfor(biaoMing);

// 移除SN

tableInfor.Remove("ID");

tableInfor.Remove("SN");

String updateSqlParam = "";

int i = 0;

foreach (DictionaryEntry de in tableInfor)

{

String key = de.Key.ToString();

String value = de.Value.ToString();

switch (value)

{

case "CHAR":

parms[i].ParameterName = ":field" + tableHead.IndexOf(key).ToString();

parms[i].OracleType = OracleType.Char;

parms[i].Size = 32;

break;

case "VARCHAR2":

parms[i].ParameterName = ":field" + tableHead.IndexOf(key).ToString();

parms[i].OracleType = OracleType.VarChar;

parms[i].Size = 512;

break;

case "NUMBER":

parms[i].ParameterName = ":field" + tableHead.IndexOf(key).ToString();

parms[i].OracleType = OracleType.Number;

parms[i].Size = 8;

break;

}

updateSqlParam = updateSqlParam + de.Key.ToString() + "=" + parms[i].ParameterName.ToString() + ",";

i++;

}

// 插入数据库语句

String sqlUpdate = "UPDATE " + biaoMing + " SET " + updateSqlParam;

sqlUpdate = sqlUpdate.Remove(sqlUpdate.Length - 1) + " WHERE SN=:sn";

// 重新设置Value值

for (int count = 0; count < ReportsFieldCount - 2; count++)

{

// 3表示前面的2列 + 隐藏的ID列

parms[count].Value = (((TextBox)(GridViewTianJia.Rows[e.RowIndex].Cells[count + 3].Controls[0])).Text.ToString().Trim());

// 对Number 进行转换

if (parms[count].OracleType == OracleType.Number)

{

parms[0].Value = Decimal.Parse(parms[0].Value.ToString());

}

// SN设置

parms[ReportsFieldCount - 2].ParameterName = ":sn";

parms[ReportsFieldCount - 2].OracleType = OracleType.Char;

parms[ReportsFieldCount - 2].Size = 32;

parms[ReportsFieldCount - 2].Value = GridViewTianJia.DataKeys[e.RowIndex].Values[0].ToString().Trim();

// SN设置

////String sqlUpdate = "UPDATE cxcs SET 编号=:bh,生产企业=:scqy,产品名称=:cpmc,计量单位=:jldw,生产量本年实际=:sclbnsj,生产量去年实际=:sclqnsj,"

//// + "销售量本年实际=:xslbnsj,销售量去年实际=:xslqnsj,库存量年初库存=:kclnckc,库存量期末库存=:kclqmkc,平均售价本年实际=:pjsjbnsj, "

//// + "平均售价去年实际=:pjsjqnsj,产销率本年实际=:cxlbnsj,产销率去年实际=:cxlqnsj,市场占有率本年实际=:sczylbnsj,"

//// + "市场占有率去年实际=:sczylqnsj "

//// + " WHERE SN=:sn";

////OracleParameter[] parms = {

//// new OracleParameter(":bh", OracleType.Number, 8),

//// new OracleParameter(":scqy", OracleType.VarChar, 512),

//// new OracleParameter(":cpmc", OracleType.VarChar, 512),

//// new OracleParameter(":jldw", OracleType.Char, 32),

//// new OracleParameter(":sclbnsj", OracleType.Char, 32),

//// new OracleParameter(":sclqnsj", OracleType.Char, 32),

//// new OracleParameter(":xslbnsj", OracleType.Char, 32),

//// new OracleParameter(":xslqnsj", OracleType.Char, 32),

//// new OracleParameter(":kclnckc", OracleType.Char, 32),

//// new OracleParameter(":kclqmkc", OracleType.Char, 32),

//// new OracleParameter(":pjsjbnsj", OracleType.Char, 32),

//// new OracleParameter(":pjsjqnsj", OracleType.Char, 32),

//// new OracleParameter(":cxlbnsj", OracleType.Char, 32),

//// new OracleParameter(":cxlqnsj", OracleType.Char, 32),

//// new OracleParameter(":sczylbnsj", OracleType.Char, 32),

//// new OracleParameter(":sczylqnsj", OracleType.Char, 32),

//// new OracleParameter(":sn", OracleType.Char, 32)

//// };

////// 重新设置Value值

////for (int count = 0; count < ReportsFieldCount - 2; count++)

////{

//// // 3表示前面的2列 + 隐藏的ID列

//// parms[count].Value = (((TextBox)(GridViewTianJia.Rows[e.RowIndex].Cells[count + 3].Controls[0])).Text.ToString().Trim());

////}

////parms[ReportsFieldCount - 2].Value = GridViewTianJia.DataKeys[e.RowIndex].Values[0].ToString().Trim();

//parms[0].Value = ((TextBox)GridViewTianJia.Rows[e.RowIndex].FindControl("TextBoxBianHao")).Text.Trim();

//parms[1].Value = ((TextBox)GridViewTianJia.Rows[e.RowIndex].FindControl("TextBoxShenChanQiYe")).Text.Trim();

//parms[2].Value = ((TextBox)GridViewTianJia.Rows[e.RowIndex].FindControl("TextBoxChanPinMingChen")).Text.Trim();

//parms[3].Value = ((TextBox)GridViewTianJia.Rows[e.RowIndex].FindControl("TextBoxJiLiangDanWei")).Text.Trim();

//parms[4].Value = ((TextBox)GridViewTianJia.Rows[e.RowIndex].FindControl("TextBoxShenChanLiangBenNian")).Text.Trim();

//parms[5].Value = ((TextBox)GridViewTianJia.Rows[e.RowIndex].FindControl("TextBoxShenChanLiangQuNian")).Text.Trim();

//parms[6].Value = ((TextBox)GridViewTianJia.Rows[e.RowIndex].FindControl("TextBoxXiaoShouLiangBenNian")).Text.Trim();

//parms[7].Value = ((TextBox)GridViewTianJia.Rows[e.RowIndex].FindControl("TextBoxXiaoShouLiangQuNian")).Text.Trim();

//parms[8].Value = ((TextBox)GridViewTianJia.Rows[e.RowIndex].FindControl("TextBoxKuCunLiangNianChu")).Text.Trim();

//parms[9].Value = ((TextBox)GridViewTianJia.Rows[e.RowIndex].FindControl("TextBoxKuCunLiangQiMo")).Text.Trim();

//parms[10].Value = ((TextBox)GridViewTianJia.Rows[e.RowIndex].FindControl("TextBoxPingJunShouJiaBenNian")).Text.Trim();

//parms[11].Value = ((TextBox)GridViewTianJia.Rows[e.RowIndex].FindControl("TextBoxPingJunShouJiaQuNian")).Text.Trim();

//parms[12].Value = ((TextBox)GridViewTianJia.Rows[e.RowIndex].FindControl("TextBoxChanXiaoLvBenNian")).Text.Trim();

//parms[13].Value = ((TextBox)GridViewTianJia.Rows[e.RowIndex].FindControl("TextBoxChanXiaoLvQuNian")).Text.Trim();

//parms[14].Value = ((TextBox)GridViewTianJia.Rows[e.RowIndex].FindControl("TextBoxShiChangBenNian")).Text.Trim();

//parms[15].Value = ((TextBox)GridViewTianJia.Rows[e.RowIndex].FindControl("TextBoxShiChangQuNian")).Text.Trim();

//parms[16].Value = GridViewTianJia.DataKeys[e.RowIndex].Values[0].ToString().Trim();

OracleConnection conn = new OracleConnection(OracleHelper.DatabaseConnStr);

conn.Open();

OracleTransaction trans = conn.BeginTransaction(IsolationLevel.ReadCommitted);

try

{

OracleHelper.ExecuteNonQuery(trans, CommandType.Text, sqlUpdate, parms);

trans.Commit();

}

catch (Exception ex)

{

trans.Rollback();

ex.ToString();

}

finally

{

conn.Close();

}

GridViewTianJia.EditIndex = -1;

RecoveryGridView();

}