C# 在数据库中插入数据集记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1258637/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Insert dataset records in database
提问by web dunia
I need to insert the dataset records(C#) into the MS Accessdatabase table. I need to do the bulk insertion of records.
我需要将数据集记录（C#）插入到 MS Accessdatabase 表中。我需要批量插入记录。
How can I do this in C#
我怎样才能在 C# 中做到这一点
回答by J?rn Schou-Rode
For this sort of task, consider using the data adapterabstraction. With an Microsoft Access database, you can use the OleDbDataAdapterimplementation as shown in the example below:
对于此类任务，请考虑使用数据适配器抽象。对于 Microsoft Access 数据库，您可以使用OleDbDataAdapter如下示例所示的实现：
// Prerequisite: The data to be inserted is available in a DataTable/DataSet.
var data = new DataTable();
data.Columns.Add("CompanyName", typeof(string));
data.Columns.Add("Phone", typeof(string));
data.Rows.Add("Foo", "12345678");
data.Rows.Add("Bar", "87654321");
// Now, open a database connection using the Microsoft.Jet.OLEDB provider.
// The "using" statement ensures that the connection is closed no matter what.
using (var connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data source=Northwind.mdb"))
{
connection.Open();
// Create an OleDbDataAdapter and provide it with an INSERT command.
var adapter = new OleDbDataAdapter();
adapter.InsertCommand = new OleDbCommand("INSERT INTO Shippers (CompanyName, Phone) VALUES (@CompanyName , @Phone)", connection);
adapter.InsertCommand.Parameters.Add("CompanyName", OleDbType.VarChar, 40, "CompanyName");
adapter.InsertCommand.Parameters.Add("Phone", OleDbType.VarChar, 24, "Phone");
// Hit the big red button!
adapter.Update(data);
}
You can do the same against other brands of database engines as well by replacing OleDbCommand, OleDbDataAdapterand OleDbConnectionwith the appropriate implementations for your database engine. For Microsoft SQL Server, look for classes prefixed with Sql, eg. SqlCommand.
您也可以对其他品牌的数据库引擎执行相同的操作，方法是替换OleDbCommand,OleDbDataAdapter并OleDbConnection使用适合您的数据库引擎的实现。对于 Microsoft SQL Server，查找以 为前缀的类Sql，例如。SqlCommand.
回答by akbar
public void insert_dataset(DataSet ds,string ret_table, string table, string fileds, ArrayList arr_data)
{
ArrayList arr_rec=new ArrayList();
string[] str_fields = fileds.Split(',');
for (int i=0;i<ds.Tables[ret_table].Rows.Count;i++)
{
for (int j = 0; j < str_fields.Length; j++)
{
arr_rec.Add(ds.Tables[ret_table].Rows[i].ItemArray[j]);
}
insert_table(table, fileds, arr_rec);
arr_rec.Clear();
}
}
public void insert_table(string table,string fileds,ArrayList arr_data)
{
string str_command, str_params;
string[] str_fields = fileds.Split(',');
for (int i = 0; i < str_fields.Length; i++)
{
str_fields[i] = "@" + str_fields[i].Trim();
}
str_params = string.Join(",", str_fields);
str_command = "INSERT INTO " + table + "(" + fileds + ") values(" + str_params + ")";
con = new OleDbConnection();
//for sql
//con=new SqlConnection();
con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + frm_main.cur_directory + "\db_temp1.mdb;Persist Security Info=True";
//for sql
//con.ConnectionString="server=(local);trusted_connection=yes;database=telephon;";
cmd = con.CreateCommand();
//for sql
//cmd=new SqlCommand();
cmd.Connection = con;
con.Open();
cmd.CommandText =str_command;
// cmd.Parameters.AddWithValue("@ACagname", "2");
for (int i = 0; i < arr_data.Count; i++)
{
cmd.Parameters.AddWithValue(str_fields[i],arr_data[i]);
}
cmd.ExecuteNonQuery();
con.Close();
}
