使用c#asp.net进行批量插入 [英] bulk insertion using c# asp.net

问题描述

早安朋友

如何使用c#asp.net在oracle数据库中进行批量插入

谢谢你
Sushil Dharmar

Good Morning friends

how to do an bulk insert in the oracle database using c# asp.net

thanking you
Sushil Dharmar

推荐答案

SqlCommand cmd = new SqlCommand();
SqlConnection cs = new SqlConnection("YOUR CONNECTION STRING");
List<string> a = new List<string>(); 
protected void insert()
{
    cs.Open();
    for (int i = 0; i < a.Count; i++)
    {
        cmd.Connection = cs;
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = "INSERT INTO TABLENAME(NAME,PHONE) VALUES(@NAME,@PHONE)";
        cmd.Parameters.AddWithValue("@NAME", a[i]);
        cmd.Parameters.AddWithValue("@PHONE", a[i]);
        cmd.ExecuteNonQuery();
    }
    cs.Close();
}</string></string>

这只是一个例子..
这里的"a"是列表类型，但是您的大数据来自数据表或数组，或者您需要从中插入数据的其他任何东西.

this is only example..
here "a" is list type but there is your bulk data its from datatable or array or any thing else from which you need to instert data.

下面的代码非常有用，可以使用以下命令将excel导入数据库批量插入

below code is usefull importing excel into database using bulk insert

public static DataTable GetDataTableFromExcel(string SourceFilePath)
    {
        try
        {
            DataTable dtNew = new DataTable();
            DataSet ds = new DataSet();
            string ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                                                                "Data Source=" + SourceFilePath + ";" +
                                                                "Extended Properties=Excel 8.0;";


            using (OleDbConnection cn = new OleDbConnection(ConnectionString))
            {
                cn.Open();

                DataTable dbSchema = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                if (dbSchema == null || dbSchema.Rows.Count < 1)
                {
                    throw new Exception("Error: Could not determine the name of the first worksheet.");
                }
                for (int i = 0; i < dbSchema.Rows.Count; i = i + 2)
                {
                    string WorkSheetName = dbSchema.Rows[i]["TABLE_NAME"].ToString();

                    OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM [" + WorkSheetName + "]", cn);
                    DataTable dt = new DataTable(WorkSheetName);
                    da.Fill(dt);
                    da.Fill(ds);
                }
                dtNew = ds.Tables[0];
                return dtNew;
            }
        }
        catch (Exception ex)
        {           
            throw new Exception(ex.Message.ToString());
        }
    }






protected void btnImport_Click(object sender, EventArgs e)
    {
        OdbcConnection connection;
        SqlBulkCopy bulkCopy;
            fn1 = System.IO.Path.GetFileName(fyle.PostedFile.FileName);
            if ((fyle.PostedFile != null) && (fyle.PostedFile.ContentLength > 0))
            {
                string fn = System.IO.Path.GetFileName(fyle.PostedFile.FileName);
                string[] ext = fn.Split(new char[] { '.' });
                int count = ext.Length;
                if (ext[count-1].ToString() == "xls")
                {
                    fn2 = ext[0].ToString().Trim() + "-" + String.Format("{0:ddMMyyyyHHmmss}", DateTime.Now) + "." + ext[count-1].ToString().Trim();
                    fn1 = System.IO.Path.GetFileName(fyle.PostedFile.FileName);
                    string SaveLocation = Server.MapPath("EmpWorkFromIChart") + "\\" + fn2;
                    path = SaveLocation;
                    fyle.PostedFile.SaveAs(SaveLocation);
                    DataTable dt = GetDataTableFromExcel(path);
                            using (SqlConnection con =new SqlConnection(ConfigurationSettings.AppSettings.Get("GisHRoneconn")))
                            {
                                con.Open();
                                using (SqlBulkCopy copy = new SqlBulkCopy(con))
                                {
                                    copy.ColumnMappings.Add(0, 0);
                                    copy.ColumnMappings.Add(1, 1);
                                    copy.ColumnMappings.Add(2, 2);
                                    copy.ColumnMappings.Add(3, 3);
                                    copy.ColumnMappings.Add(4, 4);
                                    copy.ColumnMappings.Add(5, 5);
                                    copy.ColumnMappings.Add(6, 6);
                                    copy.ColumnMappings.Add(7, 7);
                                    copy.ColumnMappings.Add(8, 8);
                                    copy.ColumnMappings.Add(9, 9);
                                    copy.ColumnMappings.Add(10, 10);
                                    copy.ColumnMappings.Add(11, 11);
                                    copy.ColumnMappings.Add(12, 12);
                                    copy.ColumnMappings.Add(13, 13);
                                    copy.ColumnMappings.Add(14, 14);
                                    copy.ColumnMappings.Add(15, 15);
                                    copy.ColumnMappings.Add(16, 16);
                                    copy.ColumnMappings.Add(17, 17);
                                    copy.ColumnMappings.Add(18, 18);
                                    copy.DestinationTableName = "<table name="">";
                                    copy.WriteToServer(dt);
                                }
                            }
                       }
              }
}</table>

这篇关于使用c#asp.net进行批量插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！