通过OleDB连接方式,访问Access,Excel数据库

1、通过OleDB连接方式,访问Access,Excel数据库. OleDbConnectionStringBuilderoleConStr=newOleDbConnectionStringBuilder();Access连接信息oleConStr.ConnectionString=Provider=Microsoft.Jet.OLEDB.4.0;DataSource=+myDBFileName+;UserId=admin;Password=;Excel连接信息stringmyExcelConStr=Provider=Microsoft.Jet.OLEDB.4.0;DataSource=+myDB

2、FileName+;ExtendedProperties=Excel8.0;myExcelConStr=Provider=Microsoft.Jet.OLEDB.4.0;+DataSource=+myDBFileName+;+ExtendedProperties=+Convert.ToChar(34).ToString()+Excel8.0;+ExcelConnectionOptions()+Convert.ToChar(34).ToString();publicboolHeadersgetreturnHasHeaders;setHasHeaders=value;publicboolMixed

3、DatagetreturnIsMixedData;setIsMixedData=value;privatestringExcelConnectionOptions()stringstrOpts=;if(this.MixedData=true)strOpts+=Imex=2;if(this.Headers=true)strOpts+=HDR=Yes;elsestrOpts+=HDR=No;returnstrOpts;=查询数据=OleDbConnectionOleConn=newOleDbConnection(oleConStr.ConnectionString);OleDbCommandcmd

4、;stringSqlStr=Selecttop1*from+newMaTolName+;cmd=newOleDbCommand(SqlStr,OleConn);OleDbDataAdapterOleDapt=newOleDbDataAdapter();OleDapt.SelectCommand=cmd;OleConn.Open();DataSetmyDS=newDataSet();OleDapt.Fill(myDS);注意:如果是Excel数据库需在表名后加$;=增加字段=SqlStr=ALTERTABLE+newMaTolName.Replace($,)+ADDIsReaddecimal,R

5、owNolongIDENTITY(1,1);cmd=newOleDbCommand(SqlStr,OleConn);intaffectRows=cmd.ExecuteNonQuery();=在DataSet表中增加一列=myDS是已保存有数据的DataSetif(myDS.Tables0.Rows.Count1)returnIsSuccess;DataTabledt=myDS.Tables0;DataColumndc=newDataColumn();dc.DataType=Type.GetType(System.String);dc.DefaultValue=1;dc.ColumnName=M

6、aTolName;dt.Columns.Add(dc);/给该列赋值for(inti=0;idt.Rows.Count;i+)dt.RowsiMaTolName=myMaTolName;dt.DataSet.AcceptChanges();=获取数据源的框架信息,如其中的表名等.=System.Data.DataTabledt=OleConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,null);=获取与更新DataGrid中的数据改变,并更新数据源.=DataTabledtChanges=dt.GetChanges();OleDbCommandol

7、eCmd;OleDbDataAdapteroleda=newOleDbDataAdapter(oleCmd);oleda.InsertCommand=newOleDbCommand(strInsert,oleConn);oleda.UpdateCommand=newOleDbCommand(strUpdate,oleConn);oleAdapter.Update(dtChanges);privatevoidCreateNewTable()System.Data.DataTabletable=newDataTable(Student);DataColumncolumn;DataRowrow;co

8、lumn=newDataColumn();column.DataType=System.Type.GetType(System.Int32);column.ColumnName=SID;column.AutoIncrement=true;column.ReadOnly=false;column.Unique=true;table.Columns.Add(column);column=newDataColumn();column.DataType=System.Type.GetType(System.String);column.ColumnName=Name;column.AutoIncrem

9、ent=false;column.Caption=Name;column.ReadOnly=false;column.DefaultValue=myName;column.Unique=false;table.Columns.Add(column);/设置表的主键DataColumnPrimaryKeyColumns=newDataColumn1;PrimaryKeyColumns0=table.ColumnsSID;table.PrimaryKey=PrimaryKeyColumns;table.AcceptChanges();DataSetdataSet=newDataSet();data

10、Set.Tables.Add(table);for(inti=0;i=2;i+)row=table.NewRow();/rowSID=i;/rowName=name+i;table.Rows.Add(row);dataSet.AcceptChanges();this.dataGrid1.DataSource=dataSet.Tables0;=表的复制=/创建新表dataTableDestDataTabledataTableDest=newDataTable();/将表dataTableSource的结构复制到新表dataTableDest中dataTableDest=dataTableSour

11、ce.Clone();/然后再复制数据到新表中foreach(DataRowdrindataTableSource.Rows)/使用ImportRow()方法复制数据。若用dataTableDest.Rows.Add(dr)将会出错：System.ArgumentException:该行已经属于另一个表。dataTableDest.ImportRow(dr);直接用下面的方法就行了dataTableDest=dataTableSource.Copy();=创建表=DataTabledt=newDataTable(TableName);/增加列dt.Columns.Add(column0,Sys

12、tem.Type.GetType(System.String);dt.Columns.Add(newDataColumn(IsChild,typeof(bool);DataColumndc=newDataColumn(column1,System.Type.GetType(System.Boolean);dt.Columns.Add(dc);/增加行DataRowdr=dt.NewRow();drcolumn0=Good;drcolumn1=true;dt.Rows.Add(dr);/DoesntinitializetherowDataRowdr1=dt.NewRow();dt.Rows.Ad

13、d(dr1);/选择行/Searchthesecondrow如果没有赋值,则用isnull来selectDataRowdrs=dt.Select(column1isnull);DataRowdrss=dt.Select(column0=Good);/复制表包括数据DataTabledtNew=dt.Copy();/只复制表的架构DataTabledtOnlyScheme=dt.Clone();/增加行并赋值/Method1DataRowdroperate=dt.Rows0;droperatecolumn0=AXzhz;droperatecolumn1=false;/Method2dropera

14、te0=AXzhz;droperate1=false;/Method3dt.Rows0column0=AXzhz;dt.Rows0column1=false;/Method4dt.Rows00=AXzhz;dt.Rows01=false;dtOnlyScheme.Rows.Add(dt.Rows0.ItemArray);/获取行的状态if(dt.Rows0.RowState=DataRowState.Unchanged)/将表转换成xml数据流形式System.IO.TextWritertw=newSystem.IO.StringWriter();/ifTableNameisempty,Wri

15、teXml()willthrowException.dtNeedCoveret.TableName=dtNeedCoveret.TableName.Length=0?Table_AX:dtNeedCoveret.TableName;dtNeedCoveret.WriteXml(tw);dtNeedCoveret.WriteXmlSchema(tw);System.IO.TextReadertrDataTable=newSystem.IO.StringReader(xml.Substring(0,xml.IndexOf(?xml);System.IO.TextReadertrSchema=new

16、System.IO.StringReader(xml.Substring(xml.IndexOf(?xml);DataTabledtReturn=newDataTable();dtReturn.ReadXmlSchema(trSchema);dtReturn.ReadXml(trDataTable);/对表中的数据进行筛选/Itssostrangethatthesecondrowhasbeenfiltered/thesecondrowshowinGridViewnever/Itmeansnullfieldwillbefilteralways./Filtertheallconditionsdt.

17、DefaultView.RowFilter=column1true;/dt.DefaultView.RowFilter=column1=true;dt.DefaultView.RowStateFilter=DataViewRowState.Added;/对表的数据排序/StupidmethodDataRowdrsss=dt.Select(String.Empty,column0DESC,column1ASC);/Clevermethoddt.DefaultView.Sort=column0,column1ASC;dt.DefaultView.Sort=ID,NameASC;dt=dt.Defa

18、ultView.ToTable();/表的合并/两个结构相同的DT合并/*/将两个列不同的DataTable合并成一个新的DataTable/表1/表2/合并过的新表privateDataTableUnionSameDataTable(DataTabledt1,DataTabledt2)DataTabledt3=dt1.Clone();objectobj=newobjectdt3.Columns.Count;for(inti=0;idt1.Rows.Count;i+)dt1.Rowsi.ItemArray.CopyTo(obj,0);dt3.Rows.Add(obj);for(inti=0;i

19、dt2.Rows.Count;i+)dt2.Rowsi.ItemArray.CopyTo(obj,0);dt3.Rows.Add(obj);returndt3;/两个结构不同的DT合并/*/将两个列不同的DataTable合并成一个新的DataTable/表1/表2/合并过的新表privateDataTableUniteDataTable(DataTabledt1,DataTabledt2)DataTabledt3=dt1.Clone();for(inti=0;idt2.Columns.Count;i+)dt3.Columns.Add(dt2.Columnsi.ColumnName);obje

20、ctobj=newobjectdt3.Columns.Count;for(inti=0;i=dt2.Rows.Count)for(inti=0;idt2.Rows.Count;i+)for(intj=0;jdt2.Columns.Count;j+)dt3.Rowsij+dt1.Columns.Count=dt2.Rowsij.ToString();elseDataRowdr3;for(inti=0;idt2.Rows.Count-dt1.Rows.Count;i+)dr3=dt3.NewRow();dt3.Rows.Add(dr3);for(inti=0;idt2.Rows.Count;i+)

21、for(intj=0;jdt2.Columns.Count;j+)dt3.Rowsij+dt1.Columns.Count=dt2.Rowsij.ToString();returndt3;/多个结构相同的DataTable合并publicDataTableGetAllEntrysDataTable()DataTablenewDataTable=GetEntrysDataTable(0).Clone();objectobj=newobjectnewDataTable.Columns.Count;for(inti=0;ientryGroups.GetEntryGroupCount();i+)for