Asp.net 中数据量较小插入数据库

public bool ImportResourceExecuteTransAction()
{
DataTable dt = GetResourceByAPI();

string sqlCommend = "";

string connStr = SqlHelper.GetConnSting();
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
using (SqlTransaction trans = SqlHelper.BeginTransaction(connStr))
{
try
{
foreach (DataRow itemRow in dt.Rows)
{
sqlCommend = "if exists(select * from " + dt.TableName + " where EmployeeBadge = '" + itemRow["EmployeeBadge"].ToString() + "')" +
"begin return ;end else begin INSERT INTO " + dt.TableName + "([Id],[TenantID],[CompanyName],[EmployeeBadge],[EmployeeADAccount],[FirstName],[LastName],[FullName],[Gender],[Email],[Mobile],[JobTitle],[WorkCity],[WorkYears],[OnBoardDate],[ResourceStatus],[Lev1LeaderBadge],[DomainTime],[Department])" +
"VALUES('" + itemRow["ID"] + "'" + ",'" + itemRow["TenantID"] + "'" + ",'" + itemRow["CompanyName"] + "'" + ",'" + itemRow["EmployeeBadge"] + "'" + ",'" + itemRow["EmployeeADAccount"] + "'" + ",'" + itemRow["FirstName"] + "'" + ",'" + itemRow["LastName"] + "'" + ",'" + itemRow["FullName"] + "'" + ",'" +
itemRow["Gender"] + "'" + ",'" + itemRow["Email"] + "'" + ",'" + itemRow["Mobile"] + "'" + ",'" + itemRow["JobTitle"] + "'" + ",'" + itemRow["WorkCity"] + "'" + ",'" + itemRow["WorkYears"] + "'" + ",'" + itemRow["OnBoardDate"] + "'" + ",'" + itemRow["ResourceStatus"] + "'" + ",'" + itemRow["Lev1LeaderBadge"] + "'" + ",'" + itemRow["DomainTime"] + "'" + ",'" + itemRow["Department"] + "')end";

SqlHelper.ExecuteNonQuery(trans, CommandType.Text, sqlCommend);
}

trans.Commit();
conn.Close();
return true;
}
catch (Exception ex)
{
trans.Rollback();
return false;
}

}
}
}

#region 从接口中获取Resource数据

/// <summary>
/// 从接口中获取Resource数据
/// </summary>
/// <returns></returns>
public DataTable GetResourceByAPI()
{
string daasHostUrl = ConfigurationManager.AppSettings["DaaSHostUrl"];
//请求参数url+appID
string apiUrl = string.Format("{0}/webapi/api/WXInterfaceList?appid=1246F46E-8858-4B52-BDCF-E75630388466", daasHostUrl);

//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();

DataTable dt = new DataTable();

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];

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 = SqlHelper.GetConnSting();

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]";
}

return dt;
}

#endregion

#region 将json字符串转化为datatable

/// <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;
}

#endregion
}
}

转载于:https://www.cnblogs.com/zzw4756885/p/8820772.html