2024年3月8日发(作者：)
通过OleDB连接方式,访问Access,Excel数据库.
OleDbConnectionStringBuilder oleConStr = new OleDbConnectionStringBuilder();
Access 连接信息
tionString = "Provider=.4.0;Data Source=" + myDBFileName + ";User Id=admin;Password=;";
Excel 连接信息
string myExcelConStr = "Provider=.4.0;Data Source=" + myDBFileName + ";Extended Properties=Excel 8.0;";
myExcelConStr =
@"Provider=.4.0;" +
@"Data Source=" + myDBFileName + ";" +
@"Extended Properties=" + (34).ToString() +
@"Excel 8.0;" + ExcelConnectionOptions() + (34).ToString();
public bool Headers
{
get { return HasHeaders; }
set { HasHeaders = value; }
}
public bool MixedData
{
get { return IsMixedData; }
set { IsMixedData = value; }
}
private string ExcelConnectionOptions()
{
string strOpts = "";
if (ata == true)
strOpts += "Imex=2;";
if (s == true)
strOpts += "HDR=Yes;";
else
strOpts += "HDR=No;";
return strOpts;
}
=======查询数据===========
OleDbConnection OleConn = new OleDbConnection(tionStrin
g);
OleDbCommand cmd;
string SqlStr = " Select top 1 * from [" + newMaTolName + "] ";
cmd = new OleDbCommand(SqlStr, OleConn);
OleDbDataAdapter OleDapt = new OleDbDataAdapter();
Command = cmd;
();
DataSet myDS = new DataSet();
(myDS);
注意:如果是Excel数据库需在表名后加"$";
=======增加字段===========
SqlStr = " ALTER TABLE [" + e("$","") + "] ADD IsRead decimal,RowNo long IDENTITY(1,1) ";
cmd = new OleDbCommand(SqlStr, OleConn);
int affectRows = eNonQuery();
=======在DataSet表中增加一列=======
myDS 是已保存有数据的DataSet
if ([0]. < 1)
{
return IsSuccess;
}
DataTable dt = [0];
DataColumn dc = new DataColumn();
pe = e("");
tValue = 1;
Name = "MaTolName";
(dc);
//给该列赋值
for (int i = 0; i < ; i++)
{
[i]["MaTolName"] = myMaTolName;
}
Changes();
=======获取数据源的框架信息,如其中的表名等.==============
ble dt= DbSchemaTable(, null);
=======获取与更新 DataGrid中的数据改变,并更新数据源.===============
===========
DataTable dtChanges = nges();
OleDbCommand oleCmd;
OleDbDataAdapter oleda = new OleDbDataAdapter(oleCmd);
Command = new OleDbCommand(strInsert,oleConn);
Command = new OleDbCommand(strUpdate,oleConn);
(dtChanges);
private void CreateNewTable()
{
ble table = new DataTable("Student");
DataColumn column;
DataRow row;
column = new DataColumn();
pe = e("32");
Name = "SID";
crement = true;
ly = false;
= true;
(column);
column = new DataColumn();
pe = e("");
Name = "Name";
crement = false;
n = "Name";
ly = false;
tValue = "myName";
= false;
(column);
//设置表的主键
DataColumn[] PrimaryKeyColumns = new DataColumn[1];
PrimaryKeyColumns[0] = s["SID"];
yKey = PrimaryKeyColumns;
Changes();
DataSet dataSet = new DataSet();
(table);
for (int i = 0; i <= 2; i++)
{
row = ();
//row["SID"] = i;
//row["Name"] = "name " + i;
(row);
}
Changes();
urce = [0];
}
=======表的复制=========
//创建新表dataTableDest
DataTable dataTableDest = new DataTable();
//将表dataTableSource的结构复制到新表dataTableDest中
dataTableDest = ();
//然后再复制数据到新表中
foreach(DataRow dr in )
{
//使用ImportRow()方法复制数据。若用(dr)将会出错：ntException: 该行已经属于另一个表。
Row(dr);
}
直接用下面的方法就行了
dataTableDest = ();
========创建表=================
DataTable dt = new DataTable("TableName");
//增加列
("column0", e(""));
(new DataColumn("IsChild", typeof(bool)));
DataColumn dc = new DataColumn("column1", e("n"));
(dc);
//增加行
DataRow dr = ();
dr["column0"] = "Good";
dr["column1"] = true;
(dr);
//Doesn't initialize the row
DataRow dr1 = ();
(dr1);
//选择行
//Search the second row 如果没有赋值,则用is null来select
DataRow[] drs = ("column1 is null");
DataRow[] drss = ("column0 = 'Good'");
//复制表包括数据
DataTable dtNew = ();
//只复制表的架构
DataTable dtOnlyScheme = ();
//增加行并赋值
//Method 1
DataRow droperate = [0];
droperate["column0"] = "AXzhz";
droperate["column1"] = false;
//Method 2
droperate[0] = "AXzhz";
droperate[1] = false;
//Method 3
[0]["column0"] = "AXzhz";
[0]["column1"] = false;
//Method 4
[0][0] = "AXzhz";
[0][1] = false;
([0].ItemArray);
//获取行的状态
if ([0].RowState == ged)
{
}
//将表转换成xml数据流形式
iter tw = new Writer();
//if TableName is empty, WriteXml() will throw Exception.
ame===0?"Table_AX":ame;
ml(tw);
mlSchema(tw);
ader trDataTable = new Reader(
bstring(0, f("
ader trSchema = new Reader(ing(f("
DataTable dtReturn = new DataTable();
lSchema(trSchema);
l(trDataTable);
//对表中的数据进行筛选
//It's so strange that the second row has been filtered
//the second row show in GridView never
//It means null field will be filter always.
//Filter the all conditions
ter = "column1 <> true";
//ter = "column1 = true";
teFilter = ;
//对表的数据排序
//Stupid method
DataRow[] drsss = (, "column0 DESC , column1 ASC");
//Clever method
= "column0 , column1 ASC";
= "ID ,Name ASC";
dt=e();
//表的合并
//两个结构相同的DT合并
///
/// 将两个列不同的DataTable合并成一个新的DataTable
///
/// 表1
/// 表2
///
private DataTable UnionSameDataTable(DataTable dt1, DataTable dt2)
{
DataTable dt3 = ();
object[] obj = new object[];
for (int i = 0; i < ; i++)
{
[i].(obj, 0);
(obj);
}
for (int i = 0; i < ; i++)
{
[i].(obj, 0);
(obj);
}
return dt3;
}
//两个结构不同的DT合并
///
/// 将两个列不同的DataTable合并成一个新的DataTable
///
/// 表1
/// 表2
///
private DataTable UniteDataTable(DataTable dt1, DataTable dt2)
{
DataTable dt3 = ();
for (int i = 0; i < ; i++)
{
(s[i].ColumnName);
}
object[] obj = new object[];
for (int i = 0; i < ; i++)
{
[i].(obj, 0);
(obj);
}
if ( >= )
{
for (int i = 0; i < ; i++)
{
for (int j = 0; j < ; j++)
{
[i][j + ] = [i][j].ToString();
}
}
}
else
{
DataRow dr3;
for (int i = 0; i < - ; i++)
{
dr3 = ();
(dr3);
}
for (int i = 0; i < ; i++)
{
for (int j = 0; j < ; j++)
{
[i][j + ] = [i][j].ToString();
}
}
}
return dt3;
}
//多个 结构相同的DataTable合并
public DataTable GetAllEntrysDataTable()
{
DataTable newDataTable = GetEntrysDataTable(0).Clone();
object[] obj = new object[];
for (int i = 0; i < ryGroupCount(); i++)
{
for (int j = 0; j < GetEntrysDataTable(i).; j++)
{
GetEntrysDataTable(i).Rows[j].(obj, 0);
(obj);
}
}
return newDataTable;
}
//执行DataTable中的查询返回新的DataTable
//方法一
///
/// 执行DataTable中的查询返回新的DataTable
///
/// 源数据DataTable
/// 查询条件
///
private DataTable GetNewDataTable(DataTable dt, string condition)
{
DataTable newdt = new DataTable();
newdt = ();
DataRow[] dr = (condition);
for (int i = 0; i < ; i++)
{
Row((DataRow)dr[i]);
}
return newdt;//返回的查询结果
}
//方法二
///
/// 执行DataTable中的查询返回新的DataTable
///
/// 源数据DataTable
/// 查询条件
///
private DataTable GetNewDataTable(DataTable dt, string condition)
{
DataTable newdt = new DataTable();
newdt = ();
DataRow[] rows = (condition);
foreach (DataRow row in rows)
{
(ray);
}
return newdt;
}
发布评论