如何在C#中将GridView导出到MS Access

本文关键字：MS Access GridView 中将 | 更新日期: 2023-09-27 18:23:57

我想在C#中将网格导出到MS Access

这是我尝试过的代码：

String accessConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:''...''test.accdb;"; 
String sqlConnectionString = ConfigurationManager.ConnectionStrings["College"].ConnectionString; 
SqlDataAdapter adapter1 = new SqlDataAdapter(); 
adapter1.Fill(dtFillGrid); 
ADOX.Catalog catalog = new ADOX.Catalog(); 
catalog.Create(accessConnectionString); 
OleDbConnection accessConnection = new OleDbConnection(accessConnectionString);
OleDbCommand command = new OleDbCommand(); 
command.Connection = accessConnection; 
command.CommandType = CommandType.Text; 
accessConnection.Open(); 
foreach (DataTable table in dtFillGrid.Rows) { 
    String columnsCommandText = "(";
    foreach (DataColumn column in table.Columns) { 
        String columnName = column.ColumnName; 
        String dataTypeName = column.DataType.Name; 
        String sqlDataTypeName = getSqlDataTypeName(dataTypeName); 
        columnsCommandText += "[" + columnName + "] " + sqlDataTypeName + ","; 
    }   
    columnsCommandText = columnsCommandText.Remove(columnsCommandText.Length - 1);   
    columnsCommandText += ")";
    command.CommandText = "CREATE TABLE " + table.TableName + columnsCommandText; 
    command.ExecuteNonQuery(); 
}
//This loop fills the database with all information 
foreach (DataTable table in dtFillGrid.Rows) { 
    foreach (DataRow row in table.Rows) { 
        String commandText = "INSERT INTO " + table.TableName + " VALUES (";
        foreach (var item in row.ItemArray) { 
            commandText += "'" + item.ToString() + "',";
        } 
        commandText = commandText.Remove(commandText.Length - 1); 
        commandText += ")"; 
        command.CommandText = commandText; command.ExecuteNonQuery(); 
    } 
} 
accessConnection.Close();

我该怎么做？

如何在C#中将GridView导出到MS Access

我建议您创建MDB数据库，使用sql创建所需的表，然后在运行时绑定网格视图，或者循环处理结果并进行批量导入。

请参见此处：http://msdn.microsoft.com/en-us/library/windows/desktop/ms677200（v=vs.85）.aspx

using ADOX;  // add a COM reference to "Microsoft ADO Ext. x.x for DDL and Security" 
static void CreateMdb(string fileNameWithPath)
{
  ADOX.Catalog cat = new ADOX.Catalog();
  string connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Jet OLEDB:Engine Type=5";
  cat.Create(String.Format(connstr, fileNameWithPath));
  cat = null;
}

如果您对付款没有问题，您可以使用"Spire doc"将数据网格保存到msAccess中，如下所示：

private void btnExportToAccess_Click(object sender, EventArgs e)
{
    Spire.DataExport.Access.AccessExport accessExport = new              
    Spire.DataExport.Access.AccessExport();
    accessExport.DataSource = Spire.DataExport.Common.ExportSource.DataTable;
    accessExport.DataTable = this.dataGridView1.DataSource as DataTable;
    accessExport.DatabaseName = @"..'..'ToMdb.mdb";
    accessExport.TableName = "ExportFromDatatable";
    accessExport.SaveToFile();
}

这里有一个链接，你可以在这里找到更多的澄清

这里我不是在MS Access中导出网格视图，而是在以下代码的帮助下使用C#在MS Access创建了一个数据库：

    ADOX.Catalog cat = new ADOX.Catalog();
    ADOX.Table table = new ADOX.Table();
    //Create the table and it's fields. 
    table.Name = "Table1";
    table.Columns.Append("PartNumber", ADOX.DataTypeEnum.adVarWChar, 6); // text[6]
    table.Columns.Append("AnInteger", ADOX.DataTypeEnum.adInteger, 10); // Integer 
    try
    {
        cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=d:/m2.accdb;" + "Jet OLEDB:Engine Type=5");
        cat.Tables.Append(table);
        OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;" + "Data Source=d:/m2.accdb");
       conn.Open();
             OleDbCommand cmd = new OleDbCommand();
             cmd.Connection = conn;
             cmd.CommandText = "INSERT INTO Table1([PartNumber],[AnInteger]) VALUES (@FirstName,@LastName)";
             cmd.Parameters.Add("@FirstName", OleDbType.VarChar).Value = "neha";
             cmd.Parameters.Add("@LastName", OleDbType.VarChar).Value = 20;
             cmd.ExecuteNonQuery();
            conn.Close();                                 
    }
    catch (Exception ex)
    {
        result = false;
    }
    cat = null;

是的，我告诉你们，我们不需要通过gridview将数据导出到MS Access，我们可以使用C#直接在MS Access中创建数据库

相关文章：