200分求完整的C#对ACCESS数据库的增删改查操作代码！不够的另贴帖加分，在线等，急急急！！！！

Question

using System;
using System.Data;
using System.Data.OleDb;
string ConnectionString = @"Provider=Microsoft.Jet.OleDB.4.0;Data Source=C:\Program Files\Microsoft Office\Office\1033\FPNWIND.mdb";
OleDbConnection conn = new OleDbConnection(ConnectionString);
conn.Open();

   DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
                                                   new object[] {null, null, null, "TABLE"});
   foreach (DataRow dr in  schemaTable.Rows)
   {
//表名
Console.WriteLine(dr["TABLE_NAME"]);                  //字段名
DataTable columnTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns,
                new object[] {null, null, dr["TABLE_NAME"].ToString(),null});
             foreach(DataRow dr2 in columnTable.Rows)
             {
                Console.WriteLine("    {0}", dr2["COLUMN_NAME"]);
             } }
conn.Close();

Answer 1

this.oleDbConnection1.Open();
            DataTable schemaTable = oleDbConnection1.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Columns,
                new object[] {null, null, null,null});
            //this.DataGrid1.DataSource=schemaTable;
            //this.DataGrid1.DataBind();
            System.Data.DataRowCollection drc=schemaTable.Rows;
            foreach(DataRow dr in drc)
            {
               this.TextBox1.Text+=dr["COLUMN_NAME"]+":"+dr["DESCRIPTION"].ToString()+"\r\n";
            }

Answer 2

http://www.techng.com/content.aspx?titleid=3409
http://www.techng.com/content.aspx?titleid=2212虽然是BETA2的,但是原理差不多!

Answer 3

update数据库:
private void button3_Click(object sender, System.EventArgs e)
{
           mycnn=new OleDbConnection(@"Provider=MicroSoft.Jet.OLEDB.4.0;Data Source=f:\dazhu.mdb");
mycnn.Open();
if(mycnn.State==System.Data.ConnectionState.Open)
{
MessageBox.Show("  数据库连接成功！！！","ts");
myada=new OleDbDataAdapter("select * from 个人信息",mycnn);

            
ds=new DataSet();
                 myada.Fill(ds,"one");
                

}
} private void button1_Click(object sender, System.EventArgs e)
{
DataTable mytable=new DataTable();
mytable=ds.Tables["one"];

//更新行
int i=8;
foreach(DataRow row1 in mytable.Rows)
{
if(row1[1].ToString().Substring(0,1)=="朱")
{
row1[2]="东方"+i.ToString();
      row1[3]="江苏ok"+i.ToString();
i++;


}

}
            
this.dataGrid1.DataSource=mytable.DefaultView;


try
{  mycmd=new OleDbCommand("update 个人信息 set tel=@a,jg=@b where name=@c",mycnn);
mycmd.Parameters.Add("@te1l",System.Data.OleDb.OleDbType.VarChar,20,"tel");
mycmd.Parameters.Add("@j1p",System.Data.OleDb.OleDbType.VarChar,50,"jg");
mycmd.Parameters.Add("@na1me",System.Data.OleDb.OleDbType.VarChar,20,"name"); 
myada.UpdateCommand=mycmd;
myada.Update(ds,"one");
        
MessageBox.Show("  数据更新成功！！！");

}
catch(Exception my)
{
                   MessageBox.Show(my.Message.ToString());
}

}

Answer 4

DELETE数据库：
private void button1_Click(object sender, System.EventArgs e)
{
DataTable mytable=new DataTable();
mytable=ds.Tables["one"];
MessageBox.Show("现有" +mytable.Rows.Count.ToString() +"个记录！");

//删除行
foreach( DataRow row1 in mytable.Rows)
{
if(row1[3].ToString()=="江苏")
{
row1.Delete();

}

}

//更新  用参数的形式来实现
this.dataGrid1.DataSource=mytable;


try
{ 
mycmd=new OleDbCommand("delete * from 个人信息 where ID=@b and jg=@a",mycnn);
mycmd.Parameters.Add("@b11",System.Data.OleDb.OleDbType.Integer,0,"ID");
mycmd.Parameters.Add("@jg1",System.Data.OleDb.OleDbType.VarChar,50,"jg");
myada.DeleteCommand=mycmd;
myada.Update(ds,"one");
MessageBox.Show("  数据删除成功！！！");

}
catch(Exception my)
{
                   MessageBox.Show(my.Message.ToString());
}

}

Answer 5

连接：
----------
using System.Data;
using System.Data.OleDb;OleDbConnection thisConnection=new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\D\DevCs\Works2\Person.mdb;");
thisConnection.Open();
OleDbCommand thisCommand=thisConnection.CreateCommand(); //
thisCommand.CommandText="SELECT * FROM Intro";
OleDbDataReader thisReader=thisCommand.ExecuteReader();
while(thisReader.Read())
{
Console.WriteLine("\t{0}\t{1}",thisReader["ID"],thisReader["TheName"]);
}
thisReader.Close();
thisConnection.Close();//带密码连接：
OleDbConnection cnn=new OleDbConnection(@"Data Source='d:\db1.mdb';Provider='Microsoft.Jet.OLEDB.4.0';Jet OLEDB:Database Password=123");
===========================
加载记录从数据库.txtOleDbConnection myConn=new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\CSharp3\QXKMeTone\bin\Debug\First.mdb;");
myConn.Open();
OleDbDataAdapter thisAdapter= new OleDbDataAdapter("SELECT * FROM Student",myConn);
OleDbCommandBuilder thisBuilder=new OleDbCommandBuilder(thisAdapter);
DataSet thisDataSet=new DataSet();
thisAdapter.Fill(thisDataSet,tablename);
int nTotals=thisDataSet.Tables[tablename].Rows.Count;
Student student=new Student();
for(int i=0;i<nTotals;i++)
{
student.Name=(string)thisDataSet.Tables[tablename].Rows[i]["name"];
student.Sex=(bool)thisDataSet.Tables[tablename].Rows[i]["sex"];
student.Folk=(string)thisDataSet.Tables[tablename].Rows[i]["folk"];
student.Address=(string)thisDataSet.Tables[tablename].Rows[i]["address"];
student.Telephone=(string)thisDataSet.Tables[tablename].Rows[i]["telephone"];
student.Nativeplace=(string)thisDataSet.Tables[tablename].Rows[i]["nativeplace"];
student.Intogroup=(DateTime)thisDataSet.Tables[tablename].Rows[i]["intogroup"];
student.Intoband=(DateTime)thisDataSet.Tables[tablename].Rows[i]["intoband"];
student.Fromschool=(string)thisDataSet.Tables[tablename].Rows[i]["fromschool"];
student.Suspend=(DateTime)thisDataSet.Tables[tablename].Rows[i]["suspend"];
student.IntoDate=(DateTime)thisDataSet.Tables[tablename].Rows[i]["intoDate"];
student.ReapDate=(DateTime)thisDataSet.Tables[tablename].Rows[i]["reapDate"];
student.Intoschool=(DateTime)thisDataSet.Tables[tablename].Rows[i]["intoschool"];
student.Serial=(int)thisDataSet.Tables[tablename].Rows[i]["serial"];
student.Borndate=(DateTime)thisDataSet.Tables[tablename].Rows[i]["borndate"];
student.Polity=(string)thisDataSet.Tables[tablename].Rows[i]["polity"];
student.IDCard=(string)thisDataSet.Tables[tablename].Rows[i]["iDCard"];
student.Intogroupaddr=(string)thisDataSet.Tables[tablename].Rows[i]["intogroupaddr"];
student.Intobandaddr=(string)thisDataSet.Tables[tablename].Rows[i]["intobandaddr"];
student.Borndate=(DateTime)thisDataSet.Tables[tablename].Rows[i]["borndate"];
students.Add(student);
}
myConn.Close();

Answer 6

insert数据库：
private void button3_Click(object sender, System.EventArgs e)
{
 mycnn=new OleDbConnection(@"Provider=MicroSoft.Jet.OLEDB.4.0;Data Source=f:\dazhu.mdb");
mycnn.Open();
if(mycnn.State==System.Data.ConnectionState.Open)
{
MessageBox.Show("  数据库连接成功！！！","ts");
myada=new OleDbDataAdapter("select * from 个人信息",mycnn);

            
ds=new DataSet();
                 myada.Fill(ds,"one");
                
DataTable mytable=new DataTable();
mytable=ds.Tables["one"];
MessageBox.Show("现有" +mytable.Rows.Count.ToString() +"个记录！");
DataRow newrow;
for(int i=1;i<=5;i++)
{  newrow=mytable.NewRow(); newrow[1]="dazhuk" +i.ToString();
               newrow[3]="中国" +i.ToString();

mytable.Rows.Add(newrow);
}
           
//更新  用参数的形式来实现
 OleDbCommand mycmd=new OleDbCommand("insert into 个人信息(name,jg) values(@name1,@jg1)",mycnn);
mycmd.Parameters.Add("@name",System.Data.OleDb.OleDbType.VarChar,20,"name");
mycmd.Parameters.Add("@jg",System.Data.OleDb.OleDbType.VarChar,20,"jg");
myada.InsertCommand=mycmd;
myada.Update(ds,"one");

          MessageBox.Show("  数据添加成功！！！");

mycnn.Close();
}
}

Answer 7

=====================
直接执行命令：string myConnectionString=@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\CSharp3\QXKMeTone\bin\Debug\First.mdb;";
string myExecuteQuery="DELETE * FROM "+tablename;
OleDbConnection myConnection = new OleDbConnection(myConnectionString);
OleDbCommand myCommand = new OleDbCommand(myExecuteQuery, myConnection);
myCommand.Connection.Open();
myCommand.ExecuteNonQuery();
myConnection.Close();
===============================
查找；
DataColumn[]keys=new DataColumn[1];
keys[0]=thisDataSet.Tables["Intro"].Columns["ID"];
thisDataSet.Tables["Intro"].PrimaryKey=keys;
DataRow findRow=thisDataSet.Tables["Intro"].Rows.Find("2");
if(findRow==null)
{
Console.WriteLine("Not found");
}
else
{
Console.WriteLine("Aleady!");
}
================================
增加：Fill---
DataRow thisRow=thisDataSet.Tables["Intro"].NewRow();
thisRow["TheName"]="WangGJ";
thisRow["Age"]="15";
thisDataSet.Tables["Intro"].Rows.Add(thisRow);
Update--
==================================
删除：--基于查找
Console.WriteLine("Deleting...");
findRow.Delete();
Console.WriteLine("OK!");
=======================================
更新：OleDbConnection myConn=new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\D\DevCs\Works2\Person.mdb;");
myConn.Open();
OleDbDataAdapter thisAdapter= new OleDbDataAdapter("SELECT * FROM Intro",myConn);
OleDbCommandBuilder thisBuilder=new OleDbCommandBuilder(thisAdapter);
DataSet thisDataSet=new DataSet();
thisAdapter.Fill(thisDataSet,"Intro");
Console.WriteLine("Name before change:{0}",thisDataSet.Tables["Intro"].Rows[1]["TheName"]);
thisDataSet.Tables["Intro"].Rows[1]["TheName"]="Acme";//更新
thisAdapter.Update(thisDataSet,"Intro");
Console.WriteLine("Name after change:{0}",thisDataSet.Tables["Intro"].Rows[1]["TheName"]);
myConn.Close();

Answer 8

TO  Tomgus(小桥流水) 
未处理的“System.Data.OleDb.OleDbException”类型的异常出现在 system.data.dll 中。

Answer 9

直接执行命令：string myConnectionString=@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\CSharp3\QXKMeTone\bin\Debug\First.mdb;";
string myExecuteQuery="DELETE * FROM "+tablename;
OleDbConnection myConnection = new OleDbConnection(myConnectionString);
OleDbCommand myCommand = new OleDbCommand(myExecuteQuery, myConnection);
myCommand.Connection.Open();
myCommand.ExecuteNonQuery();
myConnection.Close();
===============================
查找；
DataColumn[]keys=new DataColumn[1];
keys[0]=thisDataSet.Tables["Intro"].Columns["ID"];
thisDataSet.Tables["Intro"].PrimaryKey=keys;
DataRow findRow=thisDataSet.Tables["Intro"].Rows.Find("2");
if(findRow==null)
{
Console.WriteLine("Not found");
}
else
{
Console.WriteLine("Aleady!");
}
================================
增加：Fill---
DataRow thisRow=thisDataSet.Tables["Intro"].NewRow();
thisRow["TheName"]="WangGJ";
thisRow["Age"]="15";
thisDataSet.Tables["Intro"].Rows.Add(thisRow);
Update--
==================================
删除：--基于查找
Console.WriteLine("Deleting...");
findRow.Delete();
Console.WriteLine("OK!");
=======================================
更新：OleDbConnection myConn=new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\D\DevCs\Works2\Person.mdb;");
myConn.Open();
OleDbDataAdapter thisAdapter= new OleDbDataAdapter("SELECT * FROM Intro",myConn);
OleDbCommandBuilder thisBuilder=new OleDbCommandBuilder(thisAdapter);
DataSet thisDataSet=new DataSet();
thisAdapter.Fill(thisDataSet,"Intro");
Console.WriteLine("Name before change:{0}",thisDataSet.Tables["Intro"].Rows[1]["TheName"]);
thisDataSet.Tables["Intro"].Rows[1]["TheName"]="Acme";//更新
thisAdapter.Update(thisDataSet,"Intro");
Console.WriteLine("Name after change:{0}",thisDataSet.Tables["Intro"].Rows[1]["TheName"]);
myConn.Close();

Answer 10

namespace DataAccess
{
public class ClassDbBinding
{
static OleDbConnection cnn;
static OleDbDataAdapter da;
static DataTable tbl;
static string ConString;  //连接字符串
public ClassDbBinding()
{
cnn=new OleDbConnection();
cnn.ConnectionString=oleDbConnectionString;
} public static string getConString()
{
return ConString;
}
public static void setConString(string strCon)
{
ConString=strCon;
} //绑定到浏览需要的查询结果集
public DataTable BindingTable(string sqlStatement)
{
cnn=new OleDbConnection();
cnn.ConnectionString=ConString;
cnn.Open(); tbl=new DataTable(); da=new OleDbDataAdapter(sqlStatement,cnn);
da.Fill(tbl);
return tbl; 

} //以OleDbParameter[]为参数的查询
public  OleDbCommand selectcmd(OleDbParameter[] cmdpArray)
{
cnn=new OleDbConnection();
cnn.ConnectionString=ConString;
cnn.Open(); OleDbCommand cmd= new OleDbCommand("SELECT * FROM 辅料表 WHERE 辅料ID Like ? and 辅料名 Like ? ", cnn);

for (int j=0; j<cmdpArray.Length; j++)
{
cmd.Parameters.Add(cmdpArray[j]) ;
}

return cmd;

}

//以string为参数的查询
public OleDbCommand pcmd(string strP)
{
cnn=new OleDbConnection();
cnn.ConnectionString=ConString;
cnn.Open(); OleDbCommand cmd= new OleDbCommand("SELECT * FROM 辅料表 WHERE 辅料ID ='"+strP+"'", cnn); return cmd;
}

//构建DataSet
public DataSet dbFL(string cmdTxt)
{
cnn=new OleDbConnection();
cnn.ConnectionString=ConString;
cnn.Open(); DataSet dsFL=new DataSet(); da=new OleDbDataAdapter(cmdTxt,cnn);
da.Fill(dsFL,"辅料表");
return dsFL;
}
//插入数据到辅料表中
public  OleDbCommand insertFL(string strIn)
{
cnn=new OleDbConnection();
cnn.ConnectionString=ConString;

try
{

cnn.Open();

}
catch(Exception e)
{
Console.WriteLine("产生错误:\n{0}",e.Message);
}

OleDbCommand cmdIn=new OleDbCommand("INSERT INTO 辅料表(辅料ID,辅料名,辅料规格, 辅料数量,单重,产地,批次) VALUES (?, ?, ?, ?, ?, ?, ?)",cnn);

OleDbParameterCollection pc=cmdIn.Parameters;
pc.Add(new System.Data.OleDb.OleDbParameter("辅料ID", System.Data.OleDb.OleDbType.VarWChar, 14, "辅料ID"));
pc.Add(new System.Data.OleDb.OleDbParameter("辅料名", System.Data.OleDb.OleDbType.VarWChar, 50, "辅料名"));
pc.Add(new System.Data.OleDb.OleDbParameter("辅料规格", System.Data.OleDb.OleDbType.VarWChar, 50, "辅料规格"));
pc.Add(new System.Data.OleDb.OleDbParameter("辅料数量", System.Data.OleDb.OleDbType.VarWChar,2, "辅料数量"));
pc.Add(new System.Data.OleDb.OleDbParameter("单重", System.Data.OleDb.OleDbType.VarWChar, 2, "单重"));
pc.Add(new System.Data.OleDb.OleDbParameter("产地", System.Data.OleDb.OleDbType.VarWChar, 50, "产地"));
pc.Add(new System.Data.OleDb.OleDbParameter("批次", System.Data.OleDb.OleDbType.VarWChar, 50, "批次"));


cmdIn.CommandText="INSERT INTO 辅料表(辅料ID,辅料名,辅料规格, 辅料数量,单重,产地,批次) VALUES ("+strIn+")";
try
{ cmdIn.ExecuteNonQuery();
}
catch(OleDbException e)
{
Console.WriteLine("连接出现错误："+e.Message);
}
return cmdIn;


} //插入数据到出入库表中
public  OleDbCommand insertCHR(string strIn)
{
cnn=new OleDbConnection();
cnn.ConnectionString=ConString;

try
{

cnn.Open();

}
catch(Exception e)
{
Console.WriteLine("产生错误:\n{0}",e.Message);
}

OleDbCommand cmdIn=new OleDbCommand("INSERT INTO 出入库表(操作ID,辅料ID,日期, 操作人,操作类型，提取数量) VALUES (?, ?, ?, ?, ?,?)",cnn);

OleDbParameterCollection pc=cmdIn.Parameters;
pc.Add(new System.Data.OleDb.OleDbParameter("操作ID", System.Data.OleDb.OleDbType.VarWChar, 14, "操作ID"));
pc.Add(new System.Data.OleDb.OleDbParameter("辅料ID", System.Data.OleDb.OleDbType.VarWChar, 50, "辅料ID"));
pc.Add(new System.Data.OleDb.OleDbParameter("日期", System.Data.OleDb.OleDbType.Date, 10, "日期"));
pc.Add(new System.Data.OleDb.OleDbParameter("操作人", System.Data.OleDb.OleDbType.VarWChar,2, "操作人,"));
pc.Add(new System.Data.OleDb.OleDbParameter("操作类型", System.Data.OleDb.OleDbType.VarWChar, 2, "操作类型"));
pc.Add(new System.Data.OleDb.OleDbParameter("提取数量", System.Data.OleDb.OleDbType.VarWChar, 2, "提取数量"));


cmdIn.CommandText="INSERT INTO 出入库表(操作ID,辅料ID,日期, 操作人,操作类型,提取数量) VALUES ("+strIn+")";
try
{ cmdIn.ExecuteNonQuery();
}
catch(OleDbException e)
{
Console.WriteLine("连接出现错误："+e.Message);
}
return cmdIn;


}

Answer 11

删除整个表: string strConn = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = db1.mdb " ; 
OleDbConnection myConn = new OleDbConnection ( strConn ) ; 
myConn.Open ( ) ; 
string strInsert = "DELETE  FROM table1" ; 
OleDbCommand inst = new OleDbCommand ( strInsert , myConn ) ; 
inst.ExecuteNonQuery ( ) ; 
myConn.Close ( ) ;