如何在C#中将gridview导出到MS Access(.mdb)中

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

我的问题是如何在C中将gridview导出到MS Access中#为此，我使用以下代码：

String accessConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:''...''test.accdb;";
String sqlConnectionString = ConfigurationManager.ConnectionStrings["College"].ConnectionString;
    //Make adapters for each table we want to export
    SqlDataAdapter adapter1 = new SqlDataAdapter();
  //  SqlDataAdapter adapter2 = new SqlDataAdapter();
    DataTable dtFillGrid = (DataTable)ViewState["FillGrid"];
    //Fills the data set with data from the SQL database
   // DataSet dataSet = new DataSet();
    adapter1.Fill(dtFillGrid);
  //  adapter2.Fill(dataSet, "Table2");
    //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();
    //This loop creates the structure of the database
    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();

但在这个代码中，我得到了一个错误：

找不到类型或命名空间名称ADOX

如何在C#中将gridview导出到MS Access(.mdb)中

根据accessConnectionString内容，您应该在机器中安装"数据连接组件"。可在下载http://www.microsoft.com/en-us/download/details.aspx?id=23734

您需要添加Microsoft ADO Ext. 2.7 for DDL and Security的引用（尽管版本号可能不同），该引用可以在引用对话框的COM部分下找到，然后在代码中添加using ADOX;。

这里我没有在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;
相关文章：