OleDbConnectionStringBuilder oleConStr
=
new
OleDbConnectionStringBuilder();
Access 连接信息
oleConStr.ConnectionString = " Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + myDBFileName +
" ;User Id=admin;Password=; " ;
Excel 连接信息
string myExcelConStr = " Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + myDBFileName + " ;Extended Properties=Excel 8.0; " ;
myExcelConStr =
@" Provider=Microsoft.Jet.OLEDB.4.0; " +
@" Data Source= " + myDBFileName + " ; " +
@" Extended Properties= " + Convert.ToChar( 34 ).ToString() +
@" Excel 8.0; " + ExcelConnectionOptions() + Convert.ToChar( 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 (this.MixedData == true)
strOpts += "Imex=2;";
if (this.Headers == true)
strOpts += "HDR=Yes;";
else
strOpts += "HDR=No;";
return strOpts;
}
======= 查询数据 ===========
OleDbConnection OleConn = new OleDbConnection(oleConStr.ConnectionString);
OleDbCommand cmd;
string SqlStr = " Select top 1 * from [ " + newMaTolName + " ] " ;
cmd = new OleDbCommand(SqlStr, OleConn);
OleDbDataAdapter OleDapt = new OleDbDataAdapter();
OleDapt.SelectCommand = cmd;
OleConn.Open();
DataSet myDS = new DataSet();
OleDapt.Fill(myDS);
注意:如果是Excel数据库需在表名后加 " $ " ;
======= 增加字段 ===========
SqlStr = " ALTER TABLE [ " + newMaTolName.Replace( " $ " , "" ) + " ] ADD IsRead decimal,RowNo long IDENTITY(1,1) " ;
cmd = new OleDbCommand(SqlStr, OleConn);
int affectRows = cmd.ExecuteNonQuery();
======= 在DataSet表中增加一列 =======
myDS 是已保存有数据的DataSet
if (myDS.Tables[ 0 ].Rows.Count < 1 )
{
return IsSuccess;
}
DataTable dt = myDS.Tables[ 0 ];
DataColumn dc = new DataColumn();
dc.DataType = Type.GetType( " System.String " );
dc.DefaultValue = 1 ;
dc.ColumnName = " MaTolName " ;
dt.Columns.Add(dc);
// 给该列赋值
for ( int i = 0 ; i < dt.Rows.Count; i ++ )
{
dt.Rows[i]["MaTolName"] = myMaTolName;
}
dt.DataSet.AcceptChanges();
======= 获取数据源的框架信息,如其中的表名等. ==============
System.Data.DataTable dt = OleConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null );
======= 获取与更新 DataGrid中的数据改变,并更新数据源. ==========================
DataTable dtChanges = dt.GetChanges();
OleDbCommand oleCmd;
OleDbDataAdapter oleda = new OleDbDataAdapter(oleCmd);
oleda.InsertCommand = new OleDbCommand(strInsert,oleConn);
oleda.UpdateCommand = new OleDbCommand(strUpdate,oleConn);
oleAdapter.Update(dtChanges);
private void CreateNewTable()
{
System.Data.DataTable table = new DataTable("Student");
DataColumn column;
DataRow row;
column = new DataColumn();
column.DataType = System.Type.GetType("System.Int32");
column.ColumnName = "SID";
column.AutoIncrement = true;
column.ReadOnly = false;
column.Unique = true;
table.Columns.Add(column);
column = new DataColumn();
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 = new DataColumn[1];
PrimaryKeyColumns[0] = table.Columns["SID"];
table.PrimaryKey = PrimaryKeyColumns;
table.AcceptChanges();
DataSet dataSet = new DataSet();
dataSet.Tables.Add(table);
for (int i = 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
DataTable dataTableDest = new DataTable();
// 将表dataTableSource的结构复制到新表dataTableDest中
dataTableDest = dataTableSource.Clone();
// 然后再复制数据到新表中
foreach (DataRow dr in dataTableSource.Rows)
{
//使用ImportRow()方法复制数据。若用dataTableDest.Rows.Add(dr)将会出错:System.ArgumentException: 该行已经属于另一个表。
dataTableDest.ImportRow(dr);
}
直接用下面的方法就行了
dataTableDest = dataTableSource.Copy();
======== 创建表 =================
DataTable dt = new DataTable( " TableName " );
// 增加列
dt.Columns.Add( " column0 " , System.Type.GetType( " System.String " ));
dt.Columns.Add( new DataColumn( " IsChild " , typeof ( bool )));
DataColumn dc = new DataColumn( " column1 " , System.Type.GetType( " System.Boolean " ));
dt.Columns.Add(dc);
// 增加行
DataRow dr = dt.NewRow();
dr[ " column0 " ] = " Good " ;
dr[ " column1 " ] = true ;
dt.Rows.Add(dr);
// Doesn't initialize the row
DataRow dr1 = dt.NewRow();
dt.Rows.Add(dr1);
// 选择行
// Search the second row 如果没有赋值,则用is null来select
DataRow[] drs = dt.Select( " column1 is null " );
DataRow[] drss = dt.Select( " column0 = 'Good' " );
// 复制表包括数据
DataTable dtNew = dt.Copy();
// 只复制表的架构
DataTable dtOnlyScheme = dt.Clone();
// 增加行并赋值
// Method 1
DataRow droperate = dt.Rows[ 0 ];
droperate[ " column0 " ] = " AXzhz " ;
droperate[ " column1 " ] = false ;
// Method 2
droperate[ 0 ] = " AXzhz " ;
droperate[ 1 ] = false ;
// Method 3
dt.Rows[ 0 ][ " column0 " ] = " AXzhz " ;
dt.Rows[ 0 ][ " column1 " ] = false ;
// Method 4
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.TextWriter tw = new System.IO.StringWriter();
// if TableName is empty, WriteXml() will throw Exception.
dtNeedCoveret.TableName = dtNeedCoveret.TableName.Length == 0 ? " Table_AX " :dtNeedCoveret.TableName;
dtNeedCoveret.WriteXml(tw);
dtNeedCoveret.WriteXmlSchema(tw);
System.IO.TextReader trDataTable = new System.IO.StringReader(xml.Substring( 0 , xml.IndexOf( " <?xml " )));
System.IO.TextReader trSchema = new System.IO.StringReader(xml.Substring(xml.IndexOf( " <?xml " )));
DataTable dtReturn = new DataTable();
dtReturn.ReadXmlSchema(trSchema);
dtReturn.ReadXml(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
dt.DefaultView.RowFilter = " column1 <> true " ;
// dt.DefaultView.RowFilter = "column1 = true";
dt.DefaultView.RowStateFilter = DataViewRowState.Added;
// 对表的数据排序
// Stupid method
DataRow[] drsss = dt.Select(String.Empty, " column0 DESC , column1 ASC " );
// Clever method
dt.DefaultView.Sort = " column0 , column1 ASC " ;
dt.DefaultView.Sort = " ID ,Name ASC " ;
dt = dt.DefaultView.ToTable();
// 表的合并
// 两个结构相同的DT合并
/**/ /// <summary>
/// 将两个列不同的DataTable合并成一个新的DataTable
/// </summary>
/// <param name="dt1">表1</param>
/// <param name="dt2">表2</param>
/// <returns>合并过的新表</returns>
private DataTable UnionSameDataTable(DataTable dt1, DataTable dt2)
{
DataTable dt3 = dt1.Clone();
object[] obj = new object[dt3.Columns.Count];
for (int i = 0; i < dt1.Rows.Count; i++)
{
dt1.Rows[i].ItemArray.CopyTo(obj, 0);
dt3.Rows.Add(obj);
}
for (int i = 0; i < dt2.Rows.Count; i++)
{
dt2.Rows[i].ItemArray.CopyTo(obj, 0);
dt3.Rows.Add(obj);
}
return dt3;
}
// 两个结构不同的DT合并
/**/ /// <summary>
/// 将两个列不同的DataTable合并成一个新的DataTable
/// </summary>
/// <param name="dt1">表1</param>
/// <param name="dt2">表2</param>
/// <returns>合并过的新表</returns>
private DataTable UniteDataTable(DataTable dt1, DataTable dt2)
{
DataTable dt3 = dt1.Clone();
for (int i = 0; i < dt2.Columns.Count; i++)
{
dt3.Columns.Add(dt2.Columns[i].ColumnName);
}
object[] obj = new object[dt3.Columns.Count];
for (int i = 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 (int i = 0; i < dt2.Rows.Count; i++)
{
for (int j = 0; j < dt2.Columns.Count; j++)
{
dt3.Rows[i][j + dt1.Columns.Count] = dt2.Rows[i][j].ToString();
}
}
}
else
{
DataRow dr3;
for (int i = 0; i < dt2.Rows.Count - dt1.Rows.Count; i++)
{
dr3 = dt3.NewRow();
dt3.Rows.Add(dr3);
}
for (int i = 0; i < dt2.Rows.Count; i++)
{
for (int j = 0; j < dt2.Columns.Count; j++)
{
dt3.Rows[i][j + dt1.Columns.Count] = dt2.Rows[i][j].ToString();
}
}
}
return dt3;
}
// 多个 结构相同的DataTable合并
public DataTable GetAllEntrysDataTable()
{
DataTable newDataTable = GetEntrysDataTable(0).Clone();
object[] obj = new object[newDataTable.Columns.Count];
for (int i = 0; i < entryGroups.GetEntryGroupCount(); i++)
{
for (int j = 0; j < GetEntrysDataTable(i).Rows.Count; j++)
{
GetEntrysDataTable(i).Rows[j].ItemArray.CopyTo(obj, 0);
newDataTable.Rows.Add(obj);
}
}
return newDataTable;
}
// 执行DataTable中的查询返回新的DataTable
// 方法一
/**/ /// <summary>
/// 执行DataTable中的查询返回新的DataTable
/// </summary>
/// <param name="dt">源数据DataTable</param>
/// <param name="condition">查询条件</param>
/// <returns></returns>
private DataTable GetNewDataTable(DataTable dt, string condition)
{
DataTable newdt = new DataTable();
newdt = dt.Clone();
DataRow[] dr = dt.Select(condition);
for (int i = 0; i < dr.Length; i++)
{
newdt.ImportRow((DataRow)dr[i]);
}
return newdt;//返回的查询结果
}
// 方法二
/**/ /// <summary>
/// 执行DataTable中的查询返回新的DataTable
/// </summary>
/// <param name="dt">源数据DataTable</param>
/// <param name="condition">查询条件</param>
/// <returns></returns>
private DataTable GetNewDataTable(DataTable dt, string condition)
{
DataTable newdt = new DataTable();
newdt = dt.Clone();
DataRow[] rows = dt.Select(condition);
foreach (DataRow row in rows)
{
newdt.Rows.Add(row.ItemArray);
}
return newdt;
}
Access 连接信息
oleConStr.ConnectionString = " Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + myDBFileName +
" ;User Id=admin;Password=; " ;
Excel 连接信息
string myExcelConStr = " Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + myDBFileName + " ;Extended Properties=Excel 8.0; " ;
myExcelConStr =
@" Provider=Microsoft.Jet.OLEDB.4.0; " +
@" Data Source= " + myDBFileName + " ; " +
@" Extended Properties= " + Convert.ToChar( 34 ).ToString() +
@" Excel 8.0; " + ExcelConnectionOptions() + Convert.ToChar( 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 (this.MixedData == true)
strOpts += "Imex=2;";
if (this.Headers == true)
strOpts += "HDR=Yes;";
else
strOpts += "HDR=No;";
return strOpts;
}
======= 查询数据 ===========
OleDbConnection OleConn = new OleDbConnection(oleConStr.ConnectionString);
OleDbCommand cmd;
string SqlStr = " Select top 1 * from [ " + newMaTolName + " ] " ;
cmd = new OleDbCommand(SqlStr, OleConn);
OleDbDataAdapter OleDapt = new OleDbDataAdapter();
OleDapt.SelectCommand = cmd;
OleConn.Open();
DataSet myDS = new DataSet();
OleDapt.Fill(myDS);
注意:如果是Excel数据库需在表名后加 " $ " ;
======= 增加字段 ===========
SqlStr = " ALTER TABLE [ " + newMaTolName.Replace( " $ " , "" ) + " ] ADD IsRead decimal,RowNo long IDENTITY(1,1) " ;
cmd = new OleDbCommand(SqlStr, OleConn);
int affectRows = cmd.ExecuteNonQuery();
======= 在DataSet表中增加一列 =======
myDS 是已保存有数据的DataSet
if (myDS.Tables[ 0 ].Rows.Count < 1 )
{
return IsSuccess;
}
DataTable dt = myDS.Tables[ 0 ];
DataColumn dc = new DataColumn();
dc.DataType = Type.GetType( " System.String " );
dc.DefaultValue = 1 ;
dc.ColumnName = " MaTolName " ;
dt.Columns.Add(dc);
// 给该列赋值
for ( int i = 0 ; i < dt.Rows.Count; i ++ )
{
dt.Rows[i]["MaTolName"] = myMaTolName;
}
dt.DataSet.AcceptChanges();
======= 获取数据源的框架信息,如其中的表名等. ==============
System.Data.DataTable dt = OleConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null );
======= 获取与更新 DataGrid中的数据改变,并更新数据源. ==========================
DataTable dtChanges = dt.GetChanges();
OleDbCommand oleCmd;
OleDbDataAdapter oleda = new OleDbDataAdapter(oleCmd);
oleda.InsertCommand = new OleDbCommand(strInsert,oleConn);
oleda.UpdateCommand = new OleDbCommand(strUpdate,oleConn);
oleAdapter.Update(dtChanges);
private void CreateNewTable()
{
System.Data.DataTable table = new DataTable("Student");
DataColumn column;
DataRow row;
column = new DataColumn();
column.DataType = System.Type.GetType("System.Int32");
column.ColumnName = "SID";
column.AutoIncrement = true;
column.ReadOnly = false;
column.Unique = true;
table.Columns.Add(column);
column = new DataColumn();
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 = new DataColumn[1];
PrimaryKeyColumns[0] = table.Columns["SID"];
table.PrimaryKey = PrimaryKeyColumns;
table.AcceptChanges();
DataSet dataSet = new DataSet();
dataSet.Tables.Add(table);
for (int i = 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
DataTable dataTableDest = new DataTable();
// 将表dataTableSource的结构复制到新表dataTableDest中
dataTableDest = dataTableSource.Clone();
// 然后再复制数据到新表中
foreach (DataRow dr in dataTableSource.Rows)
{
//使用ImportRow()方法复制数据。若用dataTableDest.Rows.Add(dr)将会出错:System.ArgumentException: 该行已经属于另一个表。
dataTableDest.ImportRow(dr);
}
直接用下面的方法就行了
dataTableDest = dataTableSource.Copy();
======== 创建表 =================
DataTable dt = new DataTable( " TableName " );
// 增加列
dt.Columns.Add( " column0 " , System.Type.GetType( " System.String " ));
dt.Columns.Add( new DataColumn( " IsChild " , typeof ( bool )));
DataColumn dc = new DataColumn( " column1 " , System.Type.GetType( " System.Boolean " ));
dt.Columns.Add(dc);
// 增加行
DataRow dr = dt.NewRow();
dr[ " column0 " ] = " Good " ;
dr[ " column1 " ] = true ;
dt.Rows.Add(dr);
// Doesn't initialize the row
DataRow dr1 = dt.NewRow();
dt.Rows.Add(dr1);
// 选择行
// Search the second row 如果没有赋值,则用is null来select
DataRow[] drs = dt.Select( " column1 is null " );
DataRow[] drss = dt.Select( " column0 = 'Good' " );
// 复制表包括数据
DataTable dtNew = dt.Copy();
// 只复制表的架构
DataTable dtOnlyScheme = dt.Clone();
// 增加行并赋值
// Method 1
DataRow droperate = dt.Rows[ 0 ];
droperate[ " column0 " ] = " AXzhz " ;
droperate[ " column1 " ] = false ;
// Method 2
droperate[ 0 ] = " AXzhz " ;
droperate[ 1 ] = false ;
// Method 3
dt.Rows[ 0 ][ " column0 " ] = " AXzhz " ;
dt.Rows[ 0 ][ " column1 " ] = false ;
// Method 4
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.TextWriter tw = new System.IO.StringWriter();
// if TableName is empty, WriteXml() will throw Exception.
dtNeedCoveret.TableName = dtNeedCoveret.TableName.Length == 0 ? " Table_AX " :dtNeedCoveret.TableName;
dtNeedCoveret.WriteXml(tw);
dtNeedCoveret.WriteXmlSchema(tw);
System.IO.TextReader trDataTable = new System.IO.StringReader(xml.Substring( 0 , xml.IndexOf( " <?xml " )));
System.IO.TextReader trSchema = new System.IO.StringReader(xml.Substring(xml.IndexOf( " <?xml " )));
DataTable dtReturn = new DataTable();
dtReturn.ReadXmlSchema(trSchema);
dtReturn.ReadXml(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
dt.DefaultView.RowFilter = " column1 <> true " ;
// dt.DefaultView.RowFilter = "column1 = true";
dt.DefaultView.RowStateFilter = DataViewRowState.Added;
// 对表的数据排序
// Stupid method
DataRow[] drsss = dt.Select(String.Empty, " column0 DESC , column1 ASC " );
// Clever method
dt.DefaultView.Sort = " column0 , column1 ASC " ;
dt.DefaultView.Sort = " ID ,Name ASC " ;
dt = dt.DefaultView.ToTable();
// 表的合并
// 两个结构相同的DT合并
/**/ /// <summary>
/// 将两个列不同的DataTable合并成一个新的DataTable
/// </summary>
/// <param name="dt1">表1</param>
/// <param name="dt2">表2</param>
/// <returns>合并过的新表</returns>
private DataTable UnionSameDataTable(DataTable dt1, DataTable dt2)
{
DataTable dt3 = dt1.Clone();
object[] obj = new object[dt3.Columns.Count];
for (int i = 0; i < dt1.Rows.Count; i++)
{
dt1.Rows[i].ItemArray.CopyTo(obj, 0);
dt3.Rows.Add(obj);
}
for (int i = 0; i < dt2.Rows.Count; i++)
{
dt2.Rows[i].ItemArray.CopyTo(obj, 0);
dt3.Rows.Add(obj);
}
return dt3;
}
// 两个结构不同的DT合并
/**/ /// <summary>
/// 将两个列不同的DataTable合并成一个新的DataTable
/// </summary>
/// <param name="dt1">表1</param>
/// <param name="dt2">表2</param>
/// <returns>合并过的新表</returns>
private DataTable UniteDataTable(DataTable dt1, DataTable dt2)
{
DataTable dt3 = dt1.Clone();
for (int i = 0; i < dt2.Columns.Count; i++)
{
dt3.Columns.Add(dt2.Columns[i].ColumnName);
}
object[] obj = new object[dt3.Columns.Count];
for (int i = 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 (int i = 0; i < dt2.Rows.Count; i++)
{
for (int j = 0; j < dt2.Columns.Count; j++)
{
dt3.Rows[i][j + dt1.Columns.Count] = dt2.Rows[i][j].ToString();
}
}
}
else
{
DataRow dr3;
for (int i = 0; i < dt2.Rows.Count - dt1.Rows.Count; i++)
{
dr3 = dt3.NewRow();
dt3.Rows.Add(dr3);
}
for (int i = 0; i < dt2.Rows.Count; i++)
{
for (int j = 0; j < dt2.Columns.Count; j++)
{
dt3.Rows[i][j + dt1.Columns.Count] = dt2.Rows[i][j].ToString();
}
}
}
return dt3;
}
// 多个 结构相同的DataTable合并
public DataTable GetAllEntrysDataTable()
{
DataTable newDataTable = GetEntrysDataTable(0).Clone();
object[] obj = new object[newDataTable.Columns.Count];
for (int i = 0; i < entryGroups.GetEntryGroupCount(); i++)
{
for (int j = 0; j < GetEntrysDataTable(i).Rows.Count; j++)
{
GetEntrysDataTable(i).Rows[j].ItemArray.CopyTo(obj, 0);
newDataTable.Rows.Add(obj);
}
}
return newDataTable;
}
// 执行DataTable中的查询返回新的DataTable
// 方法一
/**/ /// <summary>
/// 执行DataTable中的查询返回新的DataTable
/// </summary>
/// <param name="dt">源数据DataTable</param>
/// <param name="condition">查询条件</param>
/// <returns></returns>
private DataTable GetNewDataTable(DataTable dt, string condition)
{
DataTable newdt = new DataTable();
newdt = dt.Clone();
DataRow[] dr = dt.Select(condition);
for (int i = 0; i < dr.Length; i++)
{
newdt.ImportRow((DataRow)dr[i]);
}
return newdt;//返回的查询结果
}
// 方法二
/**/ /// <summary>
/// 执行DataTable中的查询返回新的DataTable
/// </summary>
/// <param name="dt">源数据DataTable</param>
/// <param name="condition">查询条件</param>
/// <returns></returns>
private DataTable GetNewDataTable(DataTable dt, string condition)
{
DataTable newdt = new DataTable();
newdt = dt.Clone();
DataRow[] rows = dt.Select(condition);
foreach (DataRow row in rows)
{
newdt.Rows.Add(row.ItemArray);
}
return newdt;
}