如何将数据集导出到.mdb文件 [英] How to Export Dataset to .mdb file

问题描述

朋友，

我正在使用带有c清晰编码的Windows应用程序，我有一个导出选项来生成.mdb文件.我有一个至少包含20个表的数据集，每个表将具有100多个行和最少15列.如果我使用"FOR循环"导出按行或按列的方式意味着需要很长时间来处理，那么是否有任何快速的流程可以将数据从Dataset导出到.mdb?

Hi Friends,

I am using windows application with c sharp coding, I have a export option to generate .mdb file. I have a Dataset which contains minimum of 20 tables and each table will have more than 100 rows and minimum of 15 columns. if i use "FOR loop " for exporting row wise or column wise means it will take long time to process , so is there a any quick process to export data from Dataset to .mdb ?

推荐答案

social.msdn说，如何将数据集/数据表导出到MDB


微软支持说， http://support.microsoft.com/kb/317881

social.msdn says, How export dataset/datatable to MDB


microsoft support says,http://support.microsoft.com/kb/317881

您好Saravana，

我找到了解决方案，在解决方案中添加了以下代码，并根据您的数据集修改了编码代码.调用此函数并传递此参数

strDirectory =您的.MDB保存路径
dtt =您的数据集

私有void Exportmdb(string strDirectory，System.Data.DataSet dtt)
{
试试
{

目录cat = new Catalog();
UpdateProgress(");
字符串str ="provider = Microsoft.Jet.OleDb.4.0; Data Source =" + strDirectory;
cat.Create(str);
cat = null;
OleDbConnection sceConnection =新的OleDbConnection(@"Provider = Microsoft.Jet.OLEDB.4.0; Data Source =" + strDirectory);
sceConnection.Open();
foreach(dtt.tables中的System.Data.DataTable dttemp)
{
字符串tableName = dttemp.TableName;

StringBuilder stbSqlGetHeaders = new StringBuilder();
stbSqlGetHeaders.Append(创建表" + tableName +(");
int z = 0;
StringBuilder stbSqlQuery = new StringBuilder();
StringBuilder stbFields = new StringBuilder();
StringBuilder stbParameters = new StringBuilder();


foreach(dttemp.Columns中的DataColumn col)
{
字符串datatyp = col.DataType.Name.ToString().Trim().ToLower();
如果(z！= 0)stbSqlGetHeaders.Append(，"); ;
字符串strName = col.ColumnName;
字符串strType = col.DataType.Name.ToString().Trim().ToLower();
如果(strType.Equals("))抛出新的ArgumentException("DataType空");
如果(strType.Equals("int32"))strType ="Number";
如果(strType.Equals("int64"))strType ="Number";
如果(strType.Equals("int16"))strType ="Number";
如果(strType.Equals("float"))strType ="float";
如果(strType.Equals("double"))strType ="Double";
如果(strType.Equals("decimal"))strType ="Double";
如果(strType.Equals("string"))strType ="memo";
如果(strType.Equals("boolean"))strType ="Bit";
如果(strType.Equals("datetime"))strType ="datetime";
如果(strType.Equals("byte []"))strType ="Image";

stbSqlGetHeaders.Append("[" + strName +]" + strType);
z ++;

stbFields.Append("[" + col.ColumnName +]");

stbParameters.Append("@" + col.ColumnName.ToLower());

如果(col.ColumnName！= dttemp.Columns [dttemp.Columns.Count-1] .ColumnName)
{
stbFields.Append(，");
stbParameters.Append(，");
}

}
stbSqlGetHeaders.Append()");
OleDbCommand sceCreateTableCommand;
字符串strCreateTableQuery = stbSqlGetHeaders.ToString();
sceCreateTableCommand =新的OleDbCommand(strCreateTableQuery，sceConnection);

sceCreateTableCommand.ExecuteNonQuery();

stbSqlQuery.Append(插入到" + tableName +(");
OleDbCommand comm =新的OleDbCommand();

stbSqlQuery.Append(stbFields.ToString()+)");
stbSqlQuery.Append("values(");
stbSqlQuery.Append(stbParameters.ToString()+)");

字符串strTotalRows = dttemp.Rows.Count.ToString();

foreach(dttemp.Rows中的DataRow行)
{
OleDbCommand sceInsertCommand =新的OleDbCommand(stbSqlQuery.ToString()，sceConnection);
foreach(dttemp.Columns中的DataColumn col)
{
字符串colnameparam = col.ColumnName;
字符串colparam = col.ColumnName.ToLower();
字符串datatyp1 = col.DataType.Name.ToString().Trim().ToLower();
如果(datatyp1.Substring(0，3)=="str")
{
如果(row [colnameparam] .ToString()！=")
{
sceInsertCommand.Parameters.Add("@" + colparam.Trim()，OleDbType.LongVarWChar).Value = row [colnameparam];
}
其他
{
sceInsertCommand.Parameters.Add("@" + colparam.Trim()，OleDbType.LongVarWChar).Value = DBNull.Value;
}
}
否则if(datatyp1.Substring(0，3)=="dat")
{
如果(row [colnameparam] .ToString()！=")
{
sceInsertCommand.Parameters.Add("@" + colparam.Trim()，OleDbType.Date).Value = row [colnameparam];
}
其他
{
sceInsertCommand.Parameters.Add("@" + colparam.Trim()，OleDbType.Date).Value = DBNull.Value;
}
}
否则if(datatyp1.Substring(0，3)=="byt")
{
如果(row [colnameparam] .ToString()！=")
{
sceInsertCommand.Parameters.Add("@" + colparam.Trim()，OleDbType.LongVarBinary).Value = row [colnameparam];
}
其他
{
sceInsertCommand.Parameters.Add("@" + colparam.Trim()，OleDbType.LongVarBinary).Value = DBNull.Value;
}
}
否则if(datatyp1.Substring(0，3)=="int")
{
如果(row [colnameparam] .ToString()！=")
{
sceInsertCommand.Parameters.Add("@" + colparam.Trim()，OleDbType.BigInt).Value = row [colnameparam];
}
其他
{
sceInsertCommand.Parameters.Add("@" + colparam.Trim()，OleDbType.BigInt).Value = DBNull.Value;
}
}
否则if(datatyp1.Substring(0，3)=="boo")
{
如果(row [colnameparam] .ToString()！=")
{
sceInsertCommand.Parameters.Add("@" + colparam.Trim()，OleDbType.Boolean).Value = row [colnameparam];
}
其他
{
sceInsertCommand.Parameters.Add("@" + colparam.Trim()，OleDbType.Boolean).Value = DBNull.Value;
}
}
否则，如果(datatyp1.Substring(0，3)=="flo")
{
如果(row [colnameparam] .ToString()！=")
{
sceInsertCommand.Parameters.Add("@" + colparam.Trim()，OleDbType.Double).Value = row [colnameparam];
}
其他
{
sceInsertCommand.Parameters.Add("@" + colparam.Trim()，OleDbType.Double).Value = DBNull.Value;
}
}
否则if(datatyp1.Substring(0，3)=="dou")
{
如果(row [colnameparam] .ToString()！=")
{
sceInsertCommand.Parameters.Add("@" + colparam.Trim()，OleDbType.Double).Value = row [colnameparam];
}
其他
{
sceInsertCommand.Parameters.Add("@" + colparam.Trim()，OleDbType.Double).Value = DBNull.Value;
}
}
否则if(datatyp1.Substring(0，3)=="dec")
{
如果(row [colnameparam] .ToString()！=")
{
sceInsertCommand.Parameters.Add("@" + colparam.Trim()，OleDbType.Decimal).Value = row [colnameparam];
}
其他
{
sceInsertCommand.Parameters.Add("@" + colparam.Trim()，OleDbType.Decimal).Value = DBNull.Value;
}
}
}
sceInsertCommand.ExecuteNonQuery();
}
}
}
catch(ex ex例外)
{
MessageBox.Show(例如消息，导出数据"，MessageBoxButtons.OK，MessageBoxIcon.Error，MessageBoxDefaultButton.Button1);
}

}

Hi Saravana,

I found solution for this, add below code in your solution and modify code accoding to your Dataset. call this function and pass parameters of this

strDirectory = your .MDB Savingpath
dtt=your Dataset

private void Exportmdb(string strDirectory, System.Data.DataSet dtt)
{
try
{

Catalog cat = new Catalog();
UpdateProgress("");
string str = "provider=Microsoft.Jet.OleDb.4.0;Data Source=" + strDirectory;
cat.Create(str);
cat = null;
OleDbConnection sceConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + strDirectory);
sceConnection.Open();
foreach (System.Data.DataTable dttemp in dtt.Tables)
{
string tableName = dttemp.TableName;

StringBuilder stbSqlGetHeaders = new StringBuilder();
stbSqlGetHeaders.Append("create table " + tableName + " (");
int z = 0;
StringBuilder stbSqlQuery = new StringBuilder();
StringBuilder stbFields = new StringBuilder();
StringBuilder stbParameters = new StringBuilder();


foreach (DataColumn col in dttemp.Columns)
{
string datatyp = col.DataType.Name.ToString().Trim().ToLower();
if (z != 0) stbSqlGetHeaders.Append(", "); ;
String strName = col.ColumnName;
String strType = col.DataType.Name.ToString().Trim().ToLower();
if (strType.Equals("")) throw new ArgumentException("DataType Empty");
if (strType.Equals("int32")) strType = "Number";
if (strType.Equals("int64")) strType = "Number";
if (strType.Equals("int16")) strType = "Number";
if (strType.Equals("float")) strType = "float";
if (strType.Equals("double")) strType = "Double";
if (strType.Equals("decimal")) strType = "Double";
if (strType.Equals("string")) strType = "memo";
if (strType.Equals("boolean")) strType = "Bit";
if (strType.Equals("datetime")) strType = "datetime";
if (strType.Equals("byte[]")) strType = "Image";

stbSqlGetHeaders.Append("[" + strName + "] " + strType);
z++;

stbFields.Append("[" + col.ColumnName + "]");

stbParameters.Append("@" + col.ColumnName.ToLower());

if (col.ColumnName != dttemp.Columns[dttemp.Columns.Count - 1].ColumnName)
{
stbFields.Append(", ");
stbParameters.Append(", ");
}

}
stbSqlGetHeaders.Append(")");
OleDbCommand sceCreateTableCommand;
string strCreateTableQuery = stbSqlGetHeaders.ToString();
sceCreateTableCommand = new OleDbCommand(strCreateTableQuery, sceConnection);

sceCreateTableCommand.ExecuteNonQuery();

stbSqlQuery.Append("insert into " + tableName + " (");
OleDbCommand comm = new OleDbCommand();

stbSqlQuery.Append(stbFields.ToString() + ") ");
stbSqlQuery.Append("values (");
stbSqlQuery.Append(stbParameters.ToString() + ") ");

string strTotalRows = dttemp.Rows.Count.ToString();

foreach (DataRow row in dttemp.Rows)
{
OleDbCommand sceInsertCommand = new OleDbCommand(stbSqlQuery.ToString(), sceConnection);
foreach (DataColumn col in dttemp.Columns)
{
string colnameparam = col.ColumnName;
string colparam = col.ColumnName.ToLower();
string datatyp1 = col.DataType.Name.ToString().Trim().ToLower();
if (datatyp1.Substring(0, 3) == "str")
{
if (row[colnameparam].ToString() != "")
{
sceInsertCommand.Parameters.Add("@" + colparam.Trim(), OleDbType.LongVarWChar).Value = row[colnameparam];
}
else
{
sceInsertCommand.Parameters.Add("@" + colparam.Trim(), OleDbType.LongVarWChar).Value = DBNull.Value;
}
}
else if (datatyp1.Substring(0, 3) == "dat")
{
if (row[colnameparam].ToString() != "")
{
sceInsertCommand.Parameters.Add("@" + colparam.Trim(), OleDbType.Date).Value = row[colnameparam];
}
else
{
sceInsertCommand.Parameters.Add("@" + colparam.Trim(), OleDbType.Date).Value = DBNull.Value;
}
}
else if (datatyp1.Substring(0, 3) == "byt")
{
if (row[colnameparam].ToString() != "")
{
sceInsertCommand.Parameters.Add("@" + colparam.Trim(), OleDbType.LongVarBinary).Value = row[colnameparam];
}
else
{
sceInsertCommand.Parameters.Add("@" + colparam.Trim(), OleDbType.LongVarBinary).Value = DBNull.Value;
}
}
else if (datatyp1.Substring(0, 3) == "int")
{
if (row[colnameparam].ToString() != "")
{
sceInsertCommand.Parameters.Add("@" + colparam.Trim(), OleDbType.BigInt).Value = row[colnameparam];
}
else
{
sceInsertCommand.Parameters.Add("@" + colparam.Trim(), OleDbType.BigInt).Value = DBNull.Value;
}
}
else if (datatyp1.Substring(0, 3) == "boo")
{
if (row[colnameparam].ToString() != "")
{
sceInsertCommand.Parameters.Add("@" + colparam.Trim(), OleDbType.Boolean).Value = row[colnameparam];
}
else
{
sceInsertCommand.Parameters.Add("@" + colparam.Trim(), OleDbType.Boolean).Value = DBNull.Value;
}
}
else if (datatyp1.Substring(0, 3) == "flo")
{
if (row[colnameparam].ToString() != "")
{
sceInsertCommand.Parameters.Add("@" + colparam.Trim(), OleDbType.Double).Value = row[colnameparam];
}
else
{
sceInsertCommand.Parameters.Add("@" + colparam.Trim(), OleDbType.Double).Value = DBNull.Value;
}
}
else if (datatyp1.Substring(0, 3) == "dou")
{
if (row[colnameparam].ToString() != "")
{
sceInsertCommand.Parameters.Add("@" + colparam.Trim(), OleDbType.Double).Value = row[colnameparam];
}
else
{
sceInsertCommand.Parameters.Add("@" + colparam.Trim(), OleDbType.Double).Value = DBNull.Value;
}
}
else if (datatyp1.Substring(0, 3) == "dec")
{
if (row[colnameparam].ToString() != "")
{
sceInsertCommand.Parameters.Add("@" + colparam.Trim(), OleDbType.Decimal).Value = row[colnameparam];
}
else
{
sceInsertCommand.Parameters.Add("@" + colparam.Trim(), OleDbType.Decimal).Value = DBNull.Value;
}
}
}
sceInsertCommand.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Export Data", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1);
}

}

阅读以下内容: http://www.xtremedotnettalk.com/showthread.php?t = 93599 [ ^ ]

Read the following : http://www.xtremedotnettalk.com/showthread.php?t=93599[^]

这篇关于如何将数据集导出到.mdb文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！