asp.net access添加返回自递增id的实现方法
先看界面：
添加后数据库：
而所要执行的语句：
string name_ = this.tbxUseName.Text.Trim();
string webname_ = this.tbxWebName.Text.Trim();
string url_ = this.tbxUrl.Text.Trim();
AddFieldItem("news_Title", name_);
AddFieldItem("news_Source",webname_);
AddFieldItem("news_Anthor",url_);
common.salert("添加成功，添加后的ID为" + insert("db_news").ToString());
当我看完小孔子cms对插入数据的处理后，自我感觉.net水平还一直停留在asp中。下面结合代码讲讲：
需要说明的是，小孔子cms在插入时使用的是多层架构，而这篇文章主要着重讲解的是学习，所以我就没弄成多层的了。插入时采用了参数化的过程，类似sql的存储过程；在实际应用中插入数据十分简单，正如上面代码所显示的。
先讲一个类[DbKeyItem]：
/// <summary>
/// 数据表中的字段属性：字段名，字段值
/// </summary>
public class DbKeyItem
{
/// <summary>
/// 字段名称
/// </summary>
public string fieldName;
/// <summary>
/// 字段值
/// </summary>
public string fieldValue;
public DbKeyItem(string _fieldName, object _fieldValue)
{
this.fieldName = _fieldName;
this.fieldValue = _fieldValue.ToString();
}
}
这个类包含两个属性：
1、fieldName：字段名
2、fieldValue：字段值
这个类主要用于：
protected ArrayList alFieldItems = new ArrayList(10);
/// <summary>
/// 添加一个字段/值对到数组中
/// </summary>
public void AddFieldItem(string _fieldName, object _fieldValue)
{
_fieldName = "[" + _fieldName + "]";
//遍历看是否已经存在字段名
for (int i = 0; i < this.alFieldItems.Count; i++)
{
if (((DbKeyItem)this.alFieldItems[i]).fieldName == _fieldName)
{
throw new ArgumentException("字段已经存在");
}
}
this.alFieldItems.Add(new DbKeyItem(_fieldName, _fieldValue));
}
声明一个ArrayList类，并通过AddFieldItem方法可以将字段名，字段值添加进ArrayList。
/// <summary>
/// 产生OleDbCommand对象所需的参数
/// </summary>
protected void GenParameters()
{
OleDbCommand oleCmd = (OleDbCommand)cmd;
if (this.alFieldItems.Count > 0)
{
for (int i = 0; i < alFieldItems.Count; i++)
{
oleCmd.Parameters.AddWithValue("@para" + i.ToString(),((DbKeyItem)alFieldItems[i]).fieldValue.ToString());
}
}
}
这个函数其实就是为了产生：
this.cmd.Parameters.AddWithValue("@para1", "阿会楠");
this.cmd.Parameters.AddWithValue("@para2","搜索吧");
this.cmd.Parameters.AddWithValue("@para3","http://www.glzy8.com");
但用它方便多了，不用一个个去手写。而关键的函数：
折叠展开
/// <summary>
/// 根据当前alFieldItem数组添加一条记录，并返回添加后的ID
/// </summary>
/// <param name="_tableName">要插入数据的表名</param>
/// <returns>返回添加后的ID</returns>
public int insert(string _tableName)
{
this.tableName = _tableName;
this.fieldName = string.Empty;
this.sqlText = "insert into " + this.tableName + "(";
string temValue = " values(";
for (int i = 0; i < this.alFieldItems.Count; i++)
{
this.sqlText += ((DbKeyItem)alFieldItems[i]).fieldName + ",";
temValue += "@para" + i.ToString() + ",";
}
//分别去掉,
this.sqlText = Input.CutComma(sqlText) + ")" + Input.CutComma(temValue) + ")" ;
//定义连接字符串
string myString = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=" + Server.MapPath("App_Data/mycms.mdb");
OleDbConnection conn = new OleDbConnection(myString);
conn.Open();
this.cmd.Connection = conn;
this.cmd.CommandText = this.sqlText;
this.GenParameters();
try
{
this.cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
//弹出错误信息，仅仅是为了帮助调试，可以throw new Exception(ex.Message)
common.salert(ex.Message);
}
int id = 0;
try
{
cmd.CommandText = "select @@identity as id";
id = Convert.ToInt32(cmd.ExecuteScalar());
}
catch (Exception ex)
{
common.salert(ex.Message);
}
conn.Close();
return id;
}
其实这个主要是等价于执行：
SQL复制代码
insert into db_news([news_Title],[news_Source],[news_Anthor]) values(@para0,@para1,@para2)
//产生所要的参数
this.GenParameters();
select @@identity as id
而CutComma函数的作用是为了除去最后的一个逗号。代码如下：
/// <summary>
/// 去除字符串最后一个','号
/// </summary>
/// <param name="chr">:要做处理的字符串</param>
/// <returns>返回已处理的字符串</returns>
public static string CutComma(string Input)
{
return CutComma(Input, ",");
}
public static string CutComma(string Input, string indexStr)
{
if (Input.IndexOf(indexStr) >= 0)
return Input.Remove(Input.LastIndexOf(indexStr));
else
return Input;
}
全部代码如下，自己看看吧。
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OleDb;
public partial class test : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
/// <summary>
/// 申明
/// </summary>
protected ArrayList alFieldItems = new ArrayList(10);
//不用new初始化该对象，将产生未处理的“System.NullReferenceException”类型的异常
protected OleDbCommand cmd = new OleDbCommand();
protected string tableName = string.Empty;
protected string fieldName = string.Empty;
protected string sqlText = string.Empty;
/// <summary>
/// 产生OleDbCommand对象所需的参数
/// </summary>
protected void GenParameters()
{
OleDbCommand oleCmd = (OleDbCommand)cmd;
if (this.alFieldItems.Count > 0)
{
for (int i = 0; i < alFieldItems.Count; i++)
{
oleCmd.Parameters.AddWithValue("@para" + i.ToString(),((DbKeyItem)alFieldItems[i]).fieldValue.ToString());
}
}
}
/// <summary>
/// 数据表中的字段属性：字段名，字段值
/// </summary>
public class DbKeyItem
{
/// <summary>
/// 字段名称
/// </summary>
public string fieldName;
/// <summary>
/// 字段值
/// </summary>
public string fieldValue;
public DbKeyItem(string _fieldName, object _fieldValue)
{
this.fieldName = _fieldName;
this.fieldValue = _fieldValue.ToString();
}
}
/// <summary>
/// 添加一个字段/值对到数组中
/// </summary>
public void AddFieldItem(string _fieldName, object _fieldValue)
{
_fieldName = "[" + _fieldName + "]";
//遍历看是否已经存在字段名
for (int i = 0; i < this.alFieldItems.Count; i++)
{
if (((DbKeyItem)this.alFieldItems[i]).fieldName == _fieldName)
{
throw new ArgumentException("字段已经存在");
}
}
this.alFieldItems.Add(new DbKeyItem(_fieldName, _fieldValue));
}
/// <summary>
/// 根据当前alFieldItem数组添加一条记录，并返回添加后的ID
/// </summary>
/// <param name="_tableName">要插入数据的表名</param>
/// <returns>返回添加后的ID</returns>
public int insert(string _tableName)
{
this.tableName = _tableName;
this.fieldName = string.Empty;
this.sqlText = "insert into " + this.tableName + "(";
string temValue = " values(";
for (int i = 0; i < this.alFieldItems.Count; i++)
{
this.sqlText += ((DbKeyItem)alFieldItems[i]).fieldName + ",";
temValue += "@para" + i.ToString() + ",";
}
//分别去掉,
this.sqlText = Input.CutComma(sqlText) + ")" + Input.CutComma(temValue) + ")" ;
//定义连接字符串
string myString = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=" + Server.MapPath("App_Data/mycms.mdb");
OleDbConnection conn = new OleDbConnection(myString);
conn.Open();
this.cmd.Connection = conn;
this.cmd.CommandText = this.sqlText;
this.GenParameters();
try
{
this.cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
//弹出错误信息，仅仅是为了帮助调试，可以throw new Exception(ex.Message)
common.salert(ex.Message);
}
int id = 0;
try
{
cmd.CommandText = "select @@identity as id";
id = Convert.ToInt32(cmd.ExecuteScalar());
}
catch (Exception ex)
{
common.salert(ex.Message);
}
conn.Close();
return id;
}
protected void btnOk_Click(object sender, EventArgs e)
{
string name_ = this.tbxUseName.Text.Trim();
string webname_ = this.tbxWebName.Text.Trim();
string url_ = this.tbxUrl.Text.Trim();
AddFieldItem("news_Title", name_);
AddFieldItem("news_Source",webname_);
AddFieldItem("news_Anthor",url_);
common.salert("添加成功，添加后的ID为" + insert("db_news").ToString());
}
}