i am converting datset table to access .mdb file but it take more than hour.how we can convert sql table to .mdb file quickly.
public void DataSetToAccess(DataSet dataSet)
{
try
{
//Create an empty Access file that we will fill with data from the data set
ADOX.Catalog catalog = new ADOX.Catalog();
catalog.Create(accessConnectionString);
//Create an Access connection and a command that we'll use
OleDbConnection accessConnection = new OleDbConnection(accessConnectionString);
OleDbCommand command = new OleDbCommand();
command.Connection = accessConnection;
command.CommandType = CommandType.Text;
accessConnection.Open();
int i = 0;
//This loop creates the structure of the database
foreach (DataTable table in dataSet.Tables)
{
String columnsCommandText = "(";
foreach (DataColumn column in table.Columns)
{
String columnName = column.ColumnName;
String dataTypeName = column.DataType.Name;
String sqlDataTypeName = SqlDbType.Text.ToString();
columnsCommandText += "[" + columnName + "] " + sqlDataTypeName + ",";
}
columnsCommandText = columnsCommandText.Remove(columnsCommandText.Length - 1);
columnsCommandText += ")";
string cellValue = "";
if (table.Rows.Count > 0)
{
cellValue = table.Rows[0][0].ToString();
}
else
{
i = i + 1;
cellValue = "E" + i;
}
command.CommandText = "CREATE TABLE " + cellValue + columnsCommandText;
command.ExecuteNonQuery();
}
int r = 0;
foreach (DataTable table in dataSet.Tables)
{
string cellValue1 = "";
foreach (DataRow row in table.Rows)
{
if (table.Rows.Count > 0)
{
cellValue1 = table.Rows[0][0].ToString();
// table.Columns.Remove("table");
}
else
{
r = r + 1;
cellValue1 = "E" + r;
}
String commandText = "INSERT INTO " + cellValue1 + " VALUES (";
foreach (var item in row.ItemArray)
{
string Result2 = string.Join(" ", item.ToString().Split(' ').Select(x => x.Trim('\'')));
commandText += "'" + Result2 + "',";
}
commandText = commandText.Remove(commandText.Length - 1);
commandText += ")";
command.CommandText = commandText;
command.ExecuteNonQuery();
}
}
accessConnection.Close();
}
catch (Exception ex)
{
throw ex;
}
Mageshwaran RPosted Nov 25, 2019, 10:46 AM
Gajendra JangidPosted Jun 25, 2019, 11:39 PM