bcp导入excel
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 | using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; using System.Data.OleDb; using System.Data.SqlClient; namespace MyExcel { public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void button1_Click( object sender, EventArgs e) { string connString = "Data Source=.;Initial Catalog=MyProgram;Integrated Security=True" ; System.Windows.Forms.OpenFileDialog fd = new OpenFileDialog(); if (fd.ShowDialog() == DialogResult.OK) { TransferData(fd.FileName, "sheet1" , connString); } } public void TransferData( string excelFile, string sheetName, string connectionString) { DataSet ds = new DataSet(); try { string strConn= "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelFile + ";" + "Extended Properties=Excel 8.0;" ; OleDbConnection conn = new OleDbConnection(strConn); conn.Open(); string strExcel = "" ; OleDbDataAdapter myCommand = null ; strExcel = string .Format( "select * from [{0}$]" , sheetName); myCommand = new OleDbDataAdapter(strExcel, strConn); myCommand.Fill(ds, sheetName); string strSql = string .Format( "if object_id('{0}') is null create table {0}(" , sheetName); foreach (System.Data.DataColumn c in ds.Tables[0].Columns) { strSql += string .Format( "[{0}] varchar(255)," , c.ColumnName); } strSql = strSql.Trim( ',' ) + ")" ; using (System.Data.SqlClient.SqlConnection sqlconn = new System.Data.SqlClient.SqlConnection(connectionString)) { sqlconn.Open(); System.Data.SqlClient.SqlCommand command = sqlconn.CreateCommand(); command.CommandText = strSql; command.ExecuteNonQuery(); sqlconn.Close(); } //用bcp导入数据 using (System.Data.SqlClient.SqlBulkCopy bcp = new System.Data.SqlClient.SqlBulkCopy(connectionString)) { bcp.SqlRowsCopied += new System.Data.SqlClient.SqlRowsCopiedEventHandler(bcp_SqlRowsCopied); bcp.BatchSize = 100; //每次传输的行数 bcp.NotifyAfter = 100; //进度提示的行数 bcp.DestinationTableName = sheetName; //目标表 bcp.WriteToServer(ds.Tables[0]); } } catch (Exception ex) { System.Windows.Forms.MessageBox.Show(ex.Message); } } void bcp_SqlRowsCopied( object sender, System.Data.SqlClient.SqlRowsCopiedEventArgs e) { this .Text = e.RowsCopied.ToString(); this .Update(); textBox1.Text = "已经完成" + e.RowsCopied.ToString(); } } } |
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· C++代码改造为UTF-8编码问题的总结
· DeepSeek 解答了困扰我五年的技术问题
· 为什么说在企业级应用开发中,后端往往是效率杀手?
· 用 C# 插值字符串处理器写一个 sscanf
· Java 中堆内存和栈内存上的数据分布和特点
· 为DeepSeek添加本地知识库
· .NET程序员AI开发基座:Microsoft.Extensions.AI
· 精选4款基于.NET开源、功能强大的通讯调试工具
· 数据不出内网:基于Ollama+OneAPI构建企业专属DeepSeek智能中台
· 大模型工具KTransformer的安装