ASP.NET WebService中通过SQLBulkCopy方法批量导入数据到数据库

 本文主要介绍了通过编写webservice来调用api接口，将接口中的json数据通过SQLBULKCOPY方法批量导入数据库

以下是详细代码过程。有问题欢迎咨询博主。博主qq:1448519567

//webiservice中的一个方法，供使用者调用执行

public void GetPeopleDataByApi() {

//请求参数url+appID
string apiUrl = "";

//requestjson数据
HttpWebRequest request = (HttpWebRequest)HttpWebRequest.Create(apiUrl);

var jsonParas = "{\"token\":\"xx@xx.com\",\"des3Password\":\"Alihe@TT2\"}";

request.Method = "POST";
//request.Timeout = 5000;
request.ContentType = "application/json";
string signature = "D3A1C4B9-B57D-4AAC-9B53-E181B8A87A9A";
request.Headers.Add("signature", signature);

ServicePointManager.ServerCertificateValidationCallback = (sender, certificate, chain, sslPolicyErrors) => true;

string responseData = string.Empty;

//将URL编码后的字符串转化为字节
byte[] data = Encoding.UTF8.GetBytes(jsonParas);

//设置请求的request长度
request.ContentLength = data.Length;

//获得请求流
Stream writer = request.GetRequestStream();

//写入流
writer.Write(data, 0, data.Length);

//关闭请求流
writer.Close();

HttpWebResponse response;

//获得响应流
try
{
response = (HttpWebResponse)request.GetResponse();
}
catch (WebException ex)
{
response = ex.Response as HttpWebResponse;
}

StreamReader reader = new StreamReader(response.GetResponseStream(), Encoding.UTF8);
responseData = reader.ReadToEnd().ToString();

if (!string.IsNullOrEmpty(responseData))
{
JObject jsonData = JObject.Parse(responseData);
string[] values = jsonData.Properties().Select(item => item.Value.ToString()).ToArray();
// JArray array = new JArray(jsonData);
string result = values[1];

DataTable dt = ToDataTable(result);

dt.Columns["code"].ColumnName = "EmployeeBadge";
dt.Columns["Name"].ColumnName = "FullName";
dt.Columns["WORKCITY"].ColumnName = "WorkCity";
dt.Columns["mobile"].ColumnName = "Mobile";
dt.Columns["jobcname"].ColumnName = "JobTitle";
dt.Columns["joindate"].ColumnName = "OnBoardDate";
dt.Columns["LeaderBadge"].ColumnName = "Lev1LeaderBadge";
dt.Columns["Status"].ColumnName = "ResourceStatus";
dt.Columns["compid"].ColumnName = "CompanyName";
dt.Columns["ADName"].ColumnName = "EmployeeADAccount";
dt.Columns["depcname"].ColumnName = "Department";

dt.Columns.Add("ID", typeof(Int32)).SetOrdinal(0);
dt.Columns.Add("TenantID", typeof(Int32)).SetOrdinal(1);
dt.Columns["CompanyName"].SetOrdinal(2);
dt.Columns["EmployeeBadge"].SetOrdinal(3);
dt.Columns["EmployeeADAccount"].SetOrdinal(4);
dt.Columns.Add("FirstName", typeof(string)).SetOrdinal(5);
dt.Columns.Add("LastName", typeof(string)).SetOrdinal(6);
dt.Columns["FullName"].SetOrdinal(7);
dt.Columns["Gender"].SetOrdinal(8);
dt.Columns["Email"].SetOrdinal(9);
dt.Columns["Mobile"].SetOrdinal(10);
dt.Columns["JobTitle"].SetOrdinal(11);
dt.Columns["WorkCity"].SetOrdinal(12);
dt.Columns.Add("WorkYears", typeof(Int32)).SetOrdinal(13);
dt.Columns["OnBoardDate"].SetOrdinal(14);
dt.Columns["ResourceStatus"].SetOrdinal(15);
dt.Columns["Lev1LeaderBadge"].SetOrdinal(16);
dt.Columns.Add("DomainTime", typeof(Int32)).SetOrdinal(17);
dt.Columns["Department"].SetOrdinal(18);

dt.Columns.Remove("glgradeD");

string connStr = "Data Source=w01sql1501;Initial Catalog=ResourceUtilization_Tims;Integrated Security=True";

for (int i = 0; i < dt.Rows.Count; i++)
{
dt.Rows[i][0] = i + 1;
dt.Rows[i][1] = i + 1;
}
dt.TableName = "[dbo].[ResourceTemp]";

//string[] columnNames = GetTableColumnName(dt);
SqlBulkCopyInsert(connStr, dt.TableName, dt);
}
}

/// <summary>
/// 将DataTable中数据批量导入数据库
/// </summary>
/// <param name="connStr">数据库连接字符串</param>
/// <param name="strTableName">数据库表名</param>
/// <param name="dtDate">DataTable数据集</param>
public static void SqlBulkCopyInsert(string connStr,string strTableName,DataTable dtData)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
using (SqlCommand command = new SqlCommand("", conn))
{
try
{
conn.Open();
command.CommandText = "truncate table [ResourceUtilization_Tims].[dbo].[ResourceTemp]";
command.ExecuteNonQuery();
using (SqlBulkCopy sqlRevdBulkCoy = new SqlBulkCopy(connStr))
{
sqlRevdBulkCoy.DestinationTableName = strTableName;
sqlRevdBulkCoy.NotifyAfter = dtData.Rows.Count;
sqlRevdBulkCoy.WriteToServer(dtData);
sqlRevdBulkCoy.Close();
}
SqlDataAdapter adapter = new SqlDataAdapter("Sp_ResourceTemp_ImportResourceData", conn);
adapter.SelectCommand.CommandType = CommandType.StoredProcedure;//指定执行类型为存储过程

}
catch (Exception ex)
{
throw (ex);
}
}
}

}

/// <summary>
/// 获取列名
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public static string[] GetTableColumnName(DataTable dt)
{
string cols = string.Empty;
for (int i = 0; i < dt.Columns.Count; i++)
{
cols += (dt.Columns[i].ColumnName + ",");
}
cols = cols.TrimEnd(',');
return cols.Split(',');
}

/// <summary>
/// 将json字符串放进DataTable
/// </summary>
/// <param name="json"></param>
/// <returns></returns>
public static DataTable ToDataTable(string json)
{
DataTable dataTable = new DataTable(); //实例化
DataTable result;
try
{
JavaScriptSerializer javaScriptSerializer = new JavaScriptSerializer();
javaScriptSerializer.MaxJsonLength = Int32.MaxValue; //取得最大数值
ArrayList arrayList = javaScriptSerializer.Deserialize<ArrayList>(json);//将json字符串序列化成数组
if (arrayList.Count > 0)
{
foreach (Dictionary<string, object> dictionary in arrayList)
{
if (dictionary.Keys.Count<string>() == 0)
{
result = dataTable;
return result;
}
if (dataTable.Columns.Count == 0)
{
foreach (string current in dictionary.Keys)
{
dataTable.Columns.Add(current);
}
}
DataRow dataRow = dataTable.NewRow();
foreach (string current in dictionary.Keys)
{
dataRow[current] = dictionary[current];
}

// DataRow[] dataRows = dt.Select(columnName + "='" + fieldData + "'");
dataTable.Rows.Add(dataRow); //循环添加行到DataTable中
}
}
}
catch(Exception ex)
{
throw ex;
}
result = dataTable;
return result;
}