c#/. NET using HttpWebRequest, SqlBulkCopy get data from API BULK INSERT db

Little Brother Novice programmer, code technology and article level are not. The articles are written for their own code to write a simple record, may be the veteran programmer nutritional value is not high, look no offense.

　　

I have a need at work: getting data from somewhere on the API interface (about 1W) and inserting it into the database.

Lou Zhu just graduated rookie, did not do bulk insert operation. Baidu Search to learn that sqlbulkcopy can be achieved. SqlBulkCopy related principle, I do not know now do not play with, later make up!

(Don't ask why not use Google, the company intranet even MSDN. microsoft.com! In addition my company is the development of c#/. NET, not java. ）

　　

First of all, to get the data from the interface naturally need to send the request, the XML format data into the Memory table DataTable.

　　

  　　　　 #regionSend HTTP Fetch data/// <summary>        ///send HTTP Fetch data/// </summary>        /// <param name= "url" >Interface Address</param>        /// <param name= "method" >interface Method</param>        /// <returns></returns>         PublicDataTable Getdataastabel (stringUrlstringmethod) {DataSet DS=NewSystem.Data.DataSet (); Try{HttpWebRequest Request= Httpwebrequest.create (url + method) asHttpWebRequest; Request. Method="GET"; WebResponse response=request.                GetResponse (); Stream Stream=Response.                GetResponseStream (); stringstr =""; using(StreamReader sr =NewStreamReader (Stream)) {STR=Sr.                ReadToEnd (); } str.                Trim (); Ds. READXML (NewStringReader (str), System.Data.XmlReadMode.Auto); returnDs. tables[0]; }            Catch(Exception ex) {MessageBox.Show ("Getdataastabel method Error:"+Ex.                Message); return NULL; }        }        #endregion

Second, use the SqlBulkCopy class to put the data into the database. SqlBulkCopy . WriteToServer () provides 4 overloaded methods, as follows:

　　 public void WriteToServer (DataRow[] rows);

　　 public void WriteToServer (DataTable table);

　　 public void WriteToServer (IDataReader Reader);

　　 public void WriteToServer (DataTable table, datarowstate rowState);

Obviously the second most simple and rude, everybody likes. That is why the above code returns to the DataTable . When you create a new SqlBulkCopy instance, you can set the check constraints, lock tables, triggers, transactions, and so on at the time of insertion by sqlbulkcopyoptions. Sqlbc.destinationtablename is the name of the table in DB in which to insert data. Sqlbc.batchsize is the amount of data that is placed into the database per batch.

By default, SqlBulkCopy will assume that the column of the source data DataTable corresponds to the column of the target table one by one, and once the two are different (the column or column names are inconsistent) the insertion fails with an error. Therefore, we need to add the source column name to the target column name with the public sqlbulkcopycolumnmapping Add (string SourceColumn, String destinationcolumn). Data format does not have to match,SqlBulkCopy will help convert. Of course, the case of a non-time format string converted to DateTime is undoubtedly an error. Finally, Qlbc.writetoserver ().

#regionWrite data to the database/// <summary>        ///write data to the database/// </summary>        /// <param name= "TableName" >database table name</param>        /// <param name= "DT" >Data</param>        /// <returns></returns>         Public BOOLWriteToServer (stringtablename,datatable DT) {            BOOLExecresult =false; SqlConnection Sqlcon=NewSqlConnection (sqlconnectionstring);            Sqlcon.open (); SqlTransaction Sqltran= Sqlcon.begintransaction ();//Start a transactionSqlBulkCopy SQLBC =NewSqlBulkCopy (Sqlcon, Sqlbulkcopyoptions.default, Sqltran); Sqlbc.destinationtablename=TableName; Sqlbc.batchsize=10000;  for(inti =0; i < dt. Columns.count; i++) {sqlBC.ColumnMappings.Add (dt. Columns[i]. Columnname,dt. Columns[i].            ColumnName); }            Try{sqlbc.writetoserver (dt);                Sqltran.commit (); Execresult=true; }            Catch(Exception ex) {Execresult=false;                Sqltran.rollback (); MessageBox.Show ("Database error:"+Ex.            Message); }            finally{sqlbc.close ();            Sqlcon.close (); }            returnExecresult; }        #endregion

Related article: http://www.cnblogs.com/zfanlong1314/archive/2013/02/05/2892998.html

It's a recommendation and my own mark.

　

c#/. NET using HttpWebRequest, SqlBulkCopy get data from API Bulk INSERT DB