提交datatable批量保存

1、编写存储过程，定义表类型type

create type ddClock as table(
userId nvarchar(50),
userCheckTime datetime
)
create type ddUserInfo as table(
userid nvarchar(50),
jobnumber nvarchar(20)
)

declare @userTable ddUserInfo
select * from @userTable
drop type ddUserInfo

create proc p_insertClock
@userTable ddUserInfo readonly,
@clockTable ddClock readonly
as
begin
    insert into ddUserInfo(userid,jobnumber)
    select userid,jobnumber from @userTable

    insert into ddClock(userid,userCheckTime)
    select userid,userCheckTime from @clockTable
end

 2、逻辑代码，

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Newtonsoft.Json;
using ClockLibrary.Model;
using System.Data;

namespace ClockLibrary
{
    public class ClockProcess
    {
        static string userURL = string.Empty;
        static AccessToken token = new AccessToken();
        static string _tokenURL = string.Empty;
        static string url= ClockLibrary.Util.Instance.GetXMLContentByKey("GetClockListURL");
        public ClockProcess()
        {
        }
        public static bool ProcessApi()
        {
            string CorpID = ClockLibrary.Util.Instance.GetXMLContentByKey("CorpID");
            string CorpSecret = ClockLibrary.Util.Instance.GetXMLContentByKey("CorpSecret");
            string AccessTokenURL = ClockLibrary.Util.Instance.GetXMLContentByKey("AccessTokenURL");
            _tokenURL = string.Format(AccessTokenURL, CorpID, CorpSecret);


            userURL = ClockLibrary.Util.Instance.GetXMLContentByKey("GetUserInfoURL");

            string tokenString = ClockLibrary.HttpHelper.Instance.HttpGet(_tokenURL, "");//HttpHelper.Intance.HttpGet(_tokenURL, "");
            token = JsonConvert.DeserializeObject(tokenString, typeof(AccessToken)) as AccessToken;

            url = string.Format(url, token.Access_Token);
            //string param = "{userId:'074838331729090102','workDateFrom': '2017-02-04 15:20:00','workDateTo': '2017-02-04 16:46:59'}"; ; //"{'userId': '0121','workDateFrom': '2017-02-03 00:00:00','workDateTo': '2017-02-4 23:59:59'}";
            ClockLibrary.Model.Params p = new ClockLibrary.Model.Params();
            p.workDateFrom = GetExitData();
            if (string.IsNullOrWhiteSpace(p.workDateFrom))
            {
                p.workDateFrom = DateTime.Now.ToString("yyyy-MM-dd")+" 00:00:00";
            }
            p.workDateTo = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
            string _p = JsonConvert.SerializeObject(p);
            string result = ClockLibrary.HttpHelper.Instance.HttpPost(url, _p, "UTF-8");
            ClockLibrary.Model.ReturnMessage error = JsonConvert.DeserializeObject(result, typeof(ClockLibrary.Model.ReturnMessage)) as ClockLibrary.Model.ReturnMessage;
            DateTime time = new DateTime(1970, 1, 1);

            DataTable userTable = CheckUserExit();
            DataTable _notExitUser = new DataTable();
            _notExitUser.Columns.Add("userid", typeof(string));
            _notExitUser.Columns.Add("jobnumber", typeof(string));
            DataTable _clock = new DataTable();
            _clock.Columns.Add("userid", typeof(string));
            _clock.Columns.Add("userCheckTime", typeof(DateTime));
            if (error.RecordResult != null)
            {
                foreach (var r in error.RecordResult)
                {
                    r._UserCheckTime = ClockLibrary.TimeHelper.IntToDateTime(int.Parse((r.UserCheckTime / 1000).ToString()));
                    DataRow _crow = _clock.NewRow();
                    _crow["userid"] = r.UserId;
                    _crow["userCheckTime"] = r._UserCheckTime;
                    _clock.Rows.Add(_crow);

                    DataRow[] row=userTable.Select("userid='"+r.UserId+"'");
                    if (row == null || row.Length == 0)
                    {
                        string strUser = ClockLibrary.HttpHelper.Instance.HttpGet(string.Format(userURL, token.Access_Token, r.UserId), "");
                        ClockLibrary.Model.User user = JsonConvert.DeserializeObject(strUser, typeof(ClockLibrary.Model.User)) as ClockLibrary.Model.User;
                        r.user = user;
                        DataRow _row = _notExitUser.NewRow();
                        _row["userid"] = r.UserId;
                        _row["jobnumber"] = user.jobnumber;
                        _notExitUser.Rows.Add(_row);
                    }

                }
                DataHelper.ExecuteProceSql(_notExitUser, _clock);
            }
            
            return true;
        }
        public static DataTable CheckUserExit()
        {
            return DataHelper.GetTable("select userid,jobnumber from ddUserInfo");
        }
        public static string GetExitData()
        {
            string maxTime = DataHelper.GetSingle("select max(userCheckTime) from ddClock").ToString();
            return maxTime;
        }
    }
}

3、get和post方法

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Net;
using System.IO;

namespace ClockLibrary
{
    public class HttpHelper
    {
        public static readonly HttpHelper Instance = new HttpHelper();
        public string HttpGet(string Url, string postDataStr)
        {
            HttpWebRequest request = (HttpWebRequest)WebRequest.Create(Url + (postDataStr == "" ? "" : "?") + postDataStr);
            request.Method = "GET";
            request.ContentType = "application/json; charset=utf-8";

            HttpWebResponse response = (HttpWebResponse)request.GetResponse();
            Stream myResponseStream = response.GetResponseStream();
            StreamReader myStreamReader = new StreamReader(myResponseStream, Encoding.GetEncoding("utf-8"));
            string retString = myStreamReader.ReadToEnd();
            myStreamReader.Close();
            myResponseStream.Close();

            return retString;
        }
        public string HttpPost(string Url, string postDataStr, string encodeName)
        {
            HttpWebRequest request = (HttpWebRequest)WebRequest.Create(Url);
            request.Method = "POST";
            //request.ContentType = "application/x-www-form-urlencoded";
            request.ContentType = "application/json";
            //request.ContentLength = Encoding.UTF8.GetByteCount(postDataStr);
            // request.CookieContainer = cookie;
            Stream myRequestStream = request.GetRequestStream();
            StreamWriter myStreamWriter = new StreamWriter(myRequestStream, Encoding.GetEncoding(encodeName));
            myStreamWriter.Write(postDataStr);
            myStreamWriter.Close();

            HttpWebResponse response = (HttpWebResponse)request.GetResponse();

            //response.Cookies = cookie.GetCookies(response.ResponseUri);
            Stream myResponseStream = response.GetResponseStream();
            StreamReader myStreamReader = new StreamReader(myResponseStream, Encoding.GetEncoding(encodeName));
            string retString = myStreamReader.ReadToEnd();
            myStreamReader.Close();
            myResponseStream.Close();

            return retString;
        }
    }
}

4、ExecuteProceSql执行存过过程，提交datatable，批量保存

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace ClockLibrary
{
    public class DataHelper
    {
        static string connectionString = ClockLibrary.Util.Instance.GetXMLContentByKey("connectionString");
        /// <summary>
        /// 
        /// </summary>
        /// <param name="userTable"></param>
        /// <param name="clockTable"></param>
        /// <returns></returns>
        public static bool ExecuteProceSql(DataTable userTable,DataTable clockTable)
        {
            using (SqlConnection connection = new SqlConnection())
            {
                using (SqlCommand command = new SqlCommand())
                {
                    try
                    {
                        connection.ConnectionString = connectionString;
                        connection.Open();
                        command.Connection = connection;
                        command.CommandText = "p_insertClock";
                        command.CommandType = CommandType.StoredProcedure;

                        command.Parameters.Add(new SqlParameter("@userTable", userTable));
                        command.Parameters.Add(new SqlParameter("@clockTable", clockTable));
                        return command.ExecuteNonQuery()>0;
                    }
                    catch
                    {

                    }
                    finally
                    {

                    }
                    return false;
                }
            }
        }
        public static DataTable GetTable(string sql)
        {
            return GetTable(sql, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static DataTable GetTable(string sql, params SqlParameter[] param)
        {
            using (SqlConnection connection = new SqlConnection())
            {
                using (SqlCommand command = new SqlCommand())
                {
                    try
                    {
                        connection.ConnectionString = connectionString;
                        connection.Open();
                        command.Connection = connection;
                        command.CommandText = sql;
                        if (param != null && param.Length > 0)
                        {
                            command.Parameters.AddRange(param);
                        }
                        SqlDataAdapter adapter = new SqlDataAdapter();
                        adapter.SelectCommand = command;
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        return ds.Tables[0];
                    }
                    catch
                    {

                    }
                    finally
                    {

                    }
                    return null;
                }
            }
        } 
        public static object GetSingle(string sql)
        {
            return GetSingle(sql,null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static object GetSingle(string sql,params SqlParameter[] param)
        {
            using (SqlConnection connection = new SqlConnection())
            {
                using (SqlCommand command = new SqlCommand())
                {
                    try
                    {
                        connection.ConnectionString = connectionString;
                        connection.Open();
                        command.Connection = connection;
                        command.CommandText = sql;
                        if (param != null && param.Length > 0)
                        {
                            command.Parameters.AddRange(param);
                        }
                        return command.ExecuteScalar();
                    }
                    catch
                    {

                    }
                    finally
                    {
                        
                    }
                    return null;
                }
            }
        }
    }
}

 5、配置信息

<?xml version="1.0" encoding="utf-8" ?>

<DingDing_JSAPI UpdateTime="2016-10-25 10:22:33">
  <!--钉钉——企业ID-->
  <CorpID>dingc80df9647f0c3db735c2f4657eb6378f</CorpID>
  <!--钉钉——管理组的凭证密钥-->
  <CorpSecret>FHf5pd1bkYKj-c7jeWAmaqesAc4nzRNSCYfeHMnI5c4tqepow2K8pCP7Vlf8V5AU</CorpSecret>

  <!--钉钉——根据企业ID和密钥，生成的访问令牌-->
  <AccessToken></AccessToken>
  <!--钉钉——生成的JSAPI-Ticket-->
  <JSapi_ticket></JSapi_ticket>

  <!--钉钉——获取AccessToken时，调用的API接口URL-->
  <AccessTokenURL>https://oapi.dingtalk.com/gettoken?corpid={0}&amp;corpsecret={1}</AccessTokenURL>
  <!--钉钉——根据AccessToken，调用的API接口URL，返回JSAPI-Ticket-->
  <JSapi_ticketURL>https://oapi.dingtalk.com/get_jsapi_ticket?access_token={0}&amp;type=jsapi</JSapi_ticketURL>


  <!--钉钉——免登——通过CODE换取用户身份-->
  <GetUserIDURL>https://oapi.dingtalk.com/user/getuserinfo?access_token={0}&amp;code={1}</GetUserIDURL>

  <!--钉钉——通讯录——获取成员详情-->
  <GetUserInfoURL>https://oapi.dingtalk.com/user/get?access_token={0}&amp;userid={1}</GetUserInfoURL>
  
  <!--钉钉——获取打卡list-->
  <GetClockListURL>https://oapi.dingtalk.com/attendance/list?access_token={0}</GetClockListURL>
  
  <!--时间间隔-->
  <interval>1</interval>
  <hour>10</hour>
  <minute>54</minute>
  <second>50</second>
  <connectionString>server=172.16.0.145;uid=sa;pwd=123.abc;database=AttendSystem_20161211;Max Pool Size=300;</connectionString>
</DingDing_JSAPI>