通过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="+myDBFileName+";ExtendedProperties=Excel8.0;";
myExcelConStr=
@"Provider=Microsoft.Jet.OLEDB.4.0;"+
@"DataSource="+myDBFileName+";"+
@"ExtendedProperties="+Convert.ToChar(34).ToString()+
@"Excel8.0;"+ExcelConnectionOptions()+Convert.ToChar(34).ToString();
publicboolHeaders
{
get{returnHasHeaders;}
set{HasHeaders=value;}
}
publicboolMixedData
{
get{returnIsMixedData;}
set{IsMixedData=value;}
}
privatestringExcelConnectionOptions()
{
stringstrOpts="";
if(this.MixedData==true)
strOpts+="Imex=2;";
if(this.Headers==true)
strOpts+="HDR=Yes;";
else
strOpts+="HDR=No;";
returnstrOpts;
}
=======查询数据===========
OleDbConnectionOleConn=newOleDbConnection(oleConStr.ConnectionString);
OleDbCommandcmd;
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,RowNolongIDENTITY(1,1)";
cmd=newOleDbCommand(SqlStr,OleConn);
intaffectRows=cmd.ExecuteNonQuery();
=======在DataSet表中增加一列=======
myDS是已保存有数据的DataSet
if(myDS.Tables[0].Rows.Count<1)
{
returnIsSuccess;
}
DataTabledt=myDS.Tables[0];
DataColumndc=newDataColumn();
dc.DataType=Type.GetType("System.String");
dc.DefaultValue=1;
dc.ColumnName="MaTolName";
dt.Columns.Add(dc);
//给该列赋值
for(inti=0;i<dt.Rows.Count;i++)
{
dt.Rows[i]["MaTolName"]=myMaTolName;
}
dt.DataSet.AcceptChanges();
=======获取数据源的框架信息,如其中的表名等.==============
System.Data.DataTabledt=OleConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,null);
=======获取与更新DataGrid中的数据改变,并更新数据源.==========================
DataTabledtChanges=dt.GetChanges();
OleDbCommandoleCmd;
OleDbDataAdapteroleda=newOleDbDataAdapter(oleCmd);
oleda.InsertCommand=newOleDbCommand(strInsert,oleConn);
oleda.UpdateCommand=newOleDbCommand(strUpdate,oleConn);
oleAdapter.Update(dtChanges);
privatevoidCreateNewTable()
{
System.Data.DataTabletable=newDataTable("Student");
DataColumncolumn;
DataRowrow;
column=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.AutoIncrement=false;
column.Caption="Name";
column.ReadOnly=false;
column.DefaultValue="myName";
column.Unique=false;
table.Columns.Add(column);
//设置表的主键
DataColumn[]PrimaryKeyColumns=newDataColumn[1];
PrimaryKeyColumns[0]=table.Columns["SID"];
table.PrimaryKey=PrimaryKeyColumns;
table.AcceptChanges();
DataSetdataSet=newDataSet();
dataSet.Tables.Add(table);
for(inti=0;i<=2;i++)
{
row=table.NewRow();
//row["SID"]=i;
//row["Name"]="name"+i;
table.Rows.Add(row);
}
dataSet.AcceptChanges();
this.dataGrid1.DataSource=dataSet.Tables[0];
}
=======表的复制=========
//创建新表dataTableDest
DataTabledataTableDest=newDataTable();
//将表dataTableSource的结构复制到新表dataTableDest中
dataTableDest=dataTableSource.Clone();
//然后再复制数据到新表中
foreach(DataRowdrindataTableSource.Rows)
{
//使用ImportRow()方法复制数据。若用dataTableDest.Rows.Add(dr)将会出错：System.ArgumentException:该行已经属于另一个表。
dataTableDest.ImportRow(dr);
}
直接用下面的方法就行了
dataTableDest=dataTableSource.Copy();
========创建表=================
DataTabledt=newDataTable("TableName");
//增加列
dt.Columns.Add("column0",System.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();
dr["column0"]="Good";
dr["column1"]=true;
dt.Rows.Add(dr);
//Doesn'tinitializetherow
DataRowdr1=dt.NewRow();
dt.Rows.Add(dr1);
//选择行
//Searchthesecondrow如果没有赋值,则用isnull来select
DataRow[]drs=dt.Select("column1isnull");
DataRow[]drss=dt.Select("column0='Good'");
//复制表包括数据
DataTabledtNew=dt.Copy();
//只复制表的架构
DataTabledtOnlyScheme=dt.Clone();
//增加行并赋值
//Method1
DataRowdroperate=dt.Rows[0];
droperate["column0"]="AXzhz";
droperate["column1"]=false;
//Method2
droperate[0]="AXzhz";
droperate[1]=false;
//Method3
dt.Rows[0]["column0"]="AXzhz";
dt.Rows[0]["column1"]=false;
//Method4
dt.Rows[0][0]="AXzhz";
dt.Rows[0][1]=false;
dtOnlyScheme.Rows.Add(dt.Rows[0].ItemArray);
//获取行的状态
if(dt.Rows[0].RowState==DataRowState.Unchanged)
{
}
//将表转换成xml数据流形式
System.IO.TextWritertw=newSystem.IO.StringWriter();
//ifTableNameisempty,WriteXml()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=newSystem.IO.StringReader(xml.Substring(xml.IndexOf("<?xml")));
DataTabledtReturn=newDataTable();
dtReturn.ReadXmlSchema(trSchema);
dtReturn.ReadXml(trDataTable);
//对表中的数据进行筛选
//It'ssostrangethatthesecondrowhasbeenfiltered
//thesecondrowshowinGridViewnever
//Itmeansnullfieldwillbefilteralways.
//Filtertheallconditions
dt.DefaultView.RowFilter="column1<>true";
//dt.DefaultView.RowFilter="column1=true";
dt.DefaultView.RowStateFilter=DataViewRowState.Added;
//对表的数据排序
//Stupidmethod
DataRow[]drsss=dt.Select(String.Empty,"column0DESC,column1ASC");
//Clevermethod
dt.DefaultView.Sort="column0,column1ASC";
dt.DefaultView.Sort="ID,NameASC";
dt=dt.DefaultView.ToTable();
//表的合并
//两个结构相同的DT合并
/**////<summary>
///将两个列不同的DataTable合并成一个新的DataTable
///</summary>
///<paramname="dt1">表1</param>
///<paramname="dt2">表2</param>
///<returns>合并过的新表</returns>
privateDataTableUnionSameDataTable(DataTabledt1,DataTabledt2)
{
DataTabledt3=dt1.Clone();
object[]obj=newobject[dt3.Columns.Count];
for(inti=0;i<dt1.Rows.Count;i++)
{
dt1.Rows[i].ItemArray.CopyTo(obj,0);
dt3.Rows.Add(obj);
}
for(inti=0;i<dt2.Rows.Count;i++)
{
dt2.Rows[i].ItemArray.CopyTo(obj,0);
dt3.Rows.Add(obj);
}
returndt3;
}
//两个结构不同的DT合并
/**////<summary>
///将两个列不同的DataTable合并成一个新的DataTable
///</summary>
///<paramname="dt1">表1</param>
///<paramname="dt2">表2</param>
///<returns>合并过的新表</returns>
privateDataTableUniteDataTable(DataTabledt1,DataTabledt2)
{
DataTabledt3=dt1.Clone();
for(inti=0;i<dt2.Columns.Count;i++)
{
dt3.Columns.Add(dt2.Columns[i].ColumnName);
}
object[]obj=newobject[dt3.Columns.Count];
for(inti=0;i<dt1.Rows.Count;i++)
{
dt1.Rows[i].ItemArray.CopyTo(obj,0);
dt3.Rows.Add(obj);
}
if(dt1.Rows.Count>=dt2.Rows.Count)
{
for(inti=0;i<dt2.Rows.Count;i++)
{
for(intj=0;j<dt2.Columns.Count;j++)
{
dt3.Rows[i][j+dt1.Columns.Count]=dt2.Rows[i][j].ToString();
}
}
}
else
{
DataRowdr3;
for(inti=0;i<dt2.Rows.Count-dt1.Rows.Count;i++)
{
dr3=dt3.NewRow();
dt3.Rows.Add(dr3);
}
for(inti=0;i<dt2.Rows.Count;i++)
{
for(intj=0;j<dt2.Columns.Count;j++)
{
dt3.Rows[i][j+dt1.Columns.Count]=dt2.Rows[i][j].ToString();
}
}
}
returndt3;
}
//多个结构相同的DataTable合并
publicDataTableGetAllEntrysDataTable()
{
DataTablenewDataTable=GetEntrysDataTable(0).Clone();
object[]obj=newobject[newDataTable.Columns.Count];
for(inti=0;i<entryGroups.GetEntryGroupCount();i++)
{
for(intj=0;j<GetEntrysDataTable(i).Rows.Count;j++)
{
GetEntrysDataTable(i).Rows[j].ItemArray.CopyTo(obj,0);
newDataTable.Rows.Add(obj);
}
}
returnnewDataTable;
}
//执行DataTable中的查询返回新的DataTable
//方法一
/**////<summary>
///执行DataTable中的查询返回新的DataTable
///</summary>
///<paramname="dt">源数据DataTable</param>
///<paramname="condition">查询条件</param>
///<returns></returns>
privateDataTableGetNewDataTable(DataTabledt,stringcondition)
{
DataTablenewdt=newDataTable();
newdt=dt.Clone();
DataRow[]dr=dt.Select(condition);
for(inti=0;i<dr.Length;i++)
{
newdt.ImportRow((DataRow)dr[i]);
}
returnnewdt;//返回的查询结果
}
//方法二
/**////<summary>
///执行DataTable中的查询返回新的DataTable
///</summary>
///<paramname="dt">源数据DataTable</param>
///<paramname="condition">查询条件</param>
///<returns></returns>
privateDataTableGetNewDataTable(DataTabledt,stringcondition)
{
DataTablenewdt=newDataTable();
newdt=dt.Clone();
DataRow[]rows=dt.Select(condition);
foreach(DataRowrowinrows)
{
newdt.Rows.Add(row.ItemArray);
}
returnnewdt;
}
posted on -02-15 18:13 DotNet编程 阅读(...) 评论(...) 编辑 收藏
如果觉得《通过OleDB连接方式 访问Access Excel数据库.》对你有帮助，请点赞、收藏，并留下你的观点哦！
