对Access数据库表字段的增删改查

我先查询数据库中表的字段：

代码如下：

view plaincopy to clipboardprint? /// <summary> /// 获得字段名称 /// </summary> /// <param name="view">要显示的DataGridView</param> /// <param name="tablename">获得字段的表名</param> public static void GetField;(DataGridView view,string tablename) { view.Rows.Clear(); DataTable table = null; table = DBHelper.GetSchemaUsingOleDbDataReader(tablename); for (int i = 0; i < table.Rows.Count; i++) { view.Rows.Add(1); view.Rows[view.Rows.Count - 1].Cells[0].Value = (object)table.Rows[i].ItemArray[0].ToString(); if (table.Rows[i].ItemArray[0].ToString() != "序号"） view.Rows[view.Rows.Count - 1].Cells[1].Value = (object)"允许修改"; else view.Rows[view.Rows.Count - 1].Cells[1].Value = (object)"禁止修改"; if (table.Rows[i].ItemArray[5].ToString() == "System.Int32") view.Rows[view.Rows.Count - 1].Cells[2].Value = (object)"整数型(系统保留)"; else if (table.Rows[i].ItemArray[5].ToString() == "System.DateTime") view.Rows[view.Rows.Count - 1].Cells[2].Value = (object)"日期型(系统保留)"; else if (table.Rows[i].ItemArray[5].ToString() == "System.Double") view.Rows[view.Rows.Count - 1].Cells[2].Value = (object)"小数型(系统保留)"; else { view.Rows[view.Rows.Count - 1].Cells[2].Value = (object)"字符型(长度):" + table.Rows[i].ItemArray[2].ToString(); } } } /// <summary> /// 获得字段名称 /// </summary> /// <param name="view">要显示的DataGridView</param> /// <param name="tablename">获得字段的表名</param> public static void GetField;(DataGridView view,string tablename) { view.Rows.Clear(); DataTable table = null; table = DBHelper.GetSchemaUsingOleDbDataReader(tablename); for (int i = 0; i < table.Rows.Count; i++) { view.Rows.Add(1); view.Rows[view.Rows.Count - 1].Cells[0].Value = (object)table.Rows[i].ItemArray[0].ToString(); if (table.Rows[i].ItemArray[0].ToString() != "序号"） view.Rows[view.Rows.Count - 1].Cells[1].Value = (object)"允许修改"; else view.Rows[view.Rows.Count - 1].Cells[1].Value = (object)"禁止修改"; if (table.Rows[i].ItemArray[5].ToString() == "System.Int32") view.Rows[view.Rows.Count - 1].Cells[2].Value = (object)"整数型(系统保留)"; else if (table.Rows[i].ItemArray[5].ToString() == "System.DateTime") view.Rows[view.Rows.Count - 1].Cells[2].Value = (object)"日期型(系统保留)"; else if (table.Rows[i].ItemArray[5].ToString() == "System.Double") view.Rows[view.Rows.Count - 1].Cells[2].Value = (object)"小数型(系统保留)"; else { view.Rows[view.Rows.Count - 1].Cells[2].Value = (object)"字符型(长度):" + table.Rows[i].ItemArray[2].ToString(); } } }   

这里面有一个查询数据表的字段的方法：

如下：

 view plaincopy to clipboardprint? /// <summary> /// 获得表的字段信息 /// </summary> /// <param name="connString"></param> /// <returns></returns> public static DataTable GetSchemaUsingOleDbDataReader(string tablename) { OleDbConnection myConn = new OleDbConnection(connectionString); DataTable table= null; try { OleDbCommand cmd = new OleDbCommand("Select * from "+tablename, myConn); myConn.Open(); OleDbDataReader dataReader = cmd.ExecuteReader(CommandBehavior.SchemaOnly); table= dataReader.GetSchemaTable(); dataReader.Close(); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } finally { if (myConn.State != ConnectionState.Closed) myConn.Close(); myConn.Dispose(); } return table; } /// <summary> /// 获得表的字段信息 /// </summary> /// <param name="connString"></param> /// <returns></returns> public static DataTable GetSchemaUsingOleDbDataReader(string tablename) { OleDbConnection myConn = new OleDbConnection(connectionString); DataTable table= null; try { OleDbCommand cmd = new OleDbCommand("Select * from "+tablename, myConn); myConn.Open(); OleDbDataReader dataReader = cmd.ExecuteReader(CommandBehavior.SchemaOnly); table= dataReader.GetSchemaTable(); dataReader.Close(); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } finally { if (myConn.State != ConnectionState.Closed) myConn.Close(); myConn.Dispose(); } return table; }   

 获得数据表中的字段我们可以对数据库表的字段进行增、删、改。

 删除数据库表字段

 如下：

view plaincopy to clipboardprint? /// <summary> /// 删除列 /// </summary> /// <param name="view"></param> public static void DeleteField;(DataGridView viewc, string tablename) { try { string columnsname = viewc.Rows[viewc.CurrentCell.RowIndex].Cells[0].Value.ToString(); if (columnsname.Trim() == "序号"） { MessageBox.Show("此列不能删除!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning); return; } else { DialogResult relsut = MessageBox.Show("您确定要删除 " + columnsname + " 这列吗？", "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning); if (relsut == DialogResult.OK) { string sql = "alter table " + tablename + " drop column " + columnsname; int count= 0; count = DBHelper.ExecuteNonQuery(CommandType.Text, sql, null); MessageBox.Show("已经删除", "提示"); } } } catch (Exception ie) { DevExpress.XtraEditors.XtraMessageBox.Show("错误！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error); } } /// <summary> /// 删除列 /// </summary> /// <param name="view"></param> public static void DeleteField;(DataGridView viewc, string tablename) { try { string columnsname = viewc.Rows[viewc.CurrentCell.RowIndex].Cells[0].Value.ToString(); if (columnsname.Trim() == "序号"） { MessageBox.Show("此列不能删除!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning); return; } else { DialogResult relsut = MessageBox.Show("您确定要删除 " + columnsname + " 这列吗？", "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning); if (relsut == DialogResult.OK) { string sql = "alter table " + tablename + " drop column " + columnsname; int count= 0; count = DBHelper.ExecuteNonQuery(CommandType.Text, sql, null); MessageBox.Show("已经删除", "提示"); } } } catch (Exception ie) { DevExpress.XtraEditors.XtraMessageBox.Show("错误！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error); } }   

修改数据表字段名称：

如下：

 view plaincopy to clipboardprint? /// <summary> /// 修改字段名称 /// </summary> /// <param name="tablename">表名</param> /// <param name="txtinput">字段名</param> /// <param name="viewm">DataGridView显示控件/param> /// <param name="rowindex">（在DataGridView）选择的当前行号</param> public static bool ModyField;(string tablename, string columName, DataGridView viewm, int rowindex) { if (columName.Trim() == "") { MessageBox.Show("请输入内容！", "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning); return false; } else { string sql= "alter table " + tablename + " add column " +columName.Trim(); string type = viewm.Rows[rowindex].Cells[2].Value.ToString(); switch (type.Substring(0, type.IndexOf("("))) { case "整数型": sql2 += " int "; break; case "小数型": sql2 += " double"; break; case "日期型": sql2 += " datetime"; break; case "字符型": sql2 += " text(" + type.Substring(type.IndexOf(":") + 1, type.Length - type.IndexOf(":") - 1) + ") WITH COMPRESSION"; break; } string sql2 = "update " + tablename + " set " + columName.Trim() + "=" + viewm.Rows[rowindex].Cells[0].Value.ToString(); string sql3= "alter table " + tablename + " drop column " + viewm.Rows[rowindex].Cells[0].Value.ToString(); int Count= 0,Count2=0,Count3=0; Count = DBHelper.ExecuteNonQuery(CommandType.Text, sql, null); Count2 = DBHelper.ExecuteNonQuery(CommandType.Text, sql2, null); Count3= DBHelper.ExecuteNonQuery(CommandType.Text, sql3, null); viewm.Rows[rowindex].Cells[0].Value = txtinput.Text.Trim(); return true; } } /// <summary> /// 修改字段名称 /// </summary> /// <param name="tablename">表名</param> /// <param name="txtinput">字段名</param> /// <param name="viewm">DataGridView显示控件/param> /// <param name="rowindex">（在DataGridView）选择的当前行号</param> public static bool ModyField;(string tablename, string columName, DataGridView viewm, int rowindex) { if (columName.Trim() == "") { MessageBox.Show("请输入内容！", "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning); return false; } else { string sql= "alter table " + tablename + " add column " +columName.Trim(); string type = viewm.Rows[rowindex].Cells[2].Value.ToString(); switch (type.Substring(0, type.IndexOf("("))) { case "整数型": sql2 += " int "; break; case "小数型": sql2 += " double"; break; case "日期型": sql2 += " datetime"; break; case "字符型": sql2 += " text(" + type.Substring(type.IndexOf(":") + 1, type.Length - type.IndexOf(":") - 1) + ") WITH COMPRESSION"; break; } string sql2 = "update " + tablename + " set " + columName.Trim() + "=" + viewm.Rows[rowindex].Cells[0].Value.ToString(); string sql3= "alter table " + tablename + " drop column " + viewm.Rows[rowindex].Cells[0].Value.ToString(); int Count= 0,Count2=0,Count3=0; Count = DBHelper.ExecuteNonQuery(CommandType.Text, sql, null); Count2 = DBHelper.ExecuteNonQuery(CommandType.Text, sql2, null); Count3= DBHelper.ExecuteNonQuery(CommandType.Text, sql3, null); viewm.Rows[rowindex].Cells[0].Value = txtinput.Text.Trim(); return true; } }

添加字段名称
如下：

 view plaincopy to clipboardprint? /// <summary> /// 添加字段 /// </summary> /// <param name="name">字段名称</param> /// <param name="size">字符字段大小</param> /// <param name="tablename">表名</param> /// <param name="cbxtype">字段类型</param> public static void AddField;(string name, int size,string tablename,string cbxtype) { try { if (nameTrim() == "") { MessageBox.Show("请输入字段名", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (cbxtype.Trim() == "") { MessageBox.Show("请输入字段类型", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } else { string sql = "alter table "+tablename+" add column " + name.Trim(); switch (cbxtype.Trim()) { case "整数": sql += " int"; break; case "日期": sql += " datetime"; break; case "小数": sql += " double"; break; case "字符": try { if (size> 255) { MessageBox.Show("请输入正确的字符大小！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } sql += " text(" + size.toString() + ") WITH COMPRESSION"; } catch (Exception ee) { MessageBox.Show("请输入正确的字符大小！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } break; } int count = 0; ount = DBHelper.ExecuteNonQuery(CommandType.Text, sql, null); } } catch (Exception ie) { MessageBox.Show("已经存在此字段名！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error); } } /// <summary> /// 添加字段 /// </summary> /// <param name="name">字段名称</param> /// <param name="size">字符字段大小</param> /// <param name="tablename">表名</param> /// <param name="cbxtype">字段类型</param> public static void AddField;(string name, int size,string tablename,string cbxtype) { try { if (nameTrim() == "") { MessageBox.Show("请输入字段名", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (cbxtype.Trim() == "") { MessageBox.Show("请输入字段类型", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } else { string sql = "alter table "+tablename+" add column " + name.Trim(); switch (cbxtype.Trim()) { case "整数": sql += " int"; break; case "日期": sql += " datetime"; break; case "小数": sql += " double"; break; case "字符": try { if (size> 255) { MessageBox.Show("请输入正确的字符大小！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } sql += " text(" + size.toString() + ") WITH COMPRESSION"; } catch (Exception ee) { MessageBox.Show("请输入正确的字符大小！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } break; } int count = 0; count = DBHelper.ExecuteNonQuery(CommandType.Text, sql, null); } } catch (Exception ie) { MessageBox.Show("已经存在此字段名！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error); } }   

 这几个方法就是access数据表的字段的增删改查，具体的限制可以随实际情况添加。

 

本文来自优快云博客，转载请标明出处：http://blog.youkuaiyun.com/zyxfcb1520/archive/2011/06/28/6573836.aspx