前情提要
有时候我们会在朋友圈看到如下两种转发情况：一种是前面带缩略图的 ，一种是无缩略图的，当然有缩略图的不管是从用户体验，还是网站推广运营方都是更优的选择。
那我们看看微信分享朋友圈缩略图是 怎么一回事呢
注：微信6.5.5版本后，微信调整了分享规则。以前的没有通过认证公众号jssdk注入分享的都不是官方认可的分享。
必要前提：① 所打开的分享网页 域名必须经过备案（备案过的二级域名也行） ②公众号后台基本配置里面获取AppID和AppSecret 以添加服务器IP到白名单 显示如下：
代码实现
在HTML的Body下 加入如下代码：
<script src="http://res.wx.qq.com/open/js/jweixin-1.0.0.js"></script>
<script>
var dataForWeixin = {
appId: ‘@ViewBag.appid‘,
url: ‘@ViewBag.url‘,
jsapiTicket:‘@ViewBag.jsapiTicket‘,
title: ‘转发标题‘,
imgUrl: ‘服务器上需要显示的图片路径‘,
timestamp: ‘@ViewBag.timestamp‘,
nonceStr: ‘@ViewBag.nonceStr‘,
signature: ‘@ViewBag.signature‘,
jsApiList: [‘onMenuShareTimeline‘,‘onMenuShareAppMessage‘],
callback: function () { }
};
wx.config({
debug: false,
appId: dataForWeixin.appId,
timestamp: dataForWeixin.timestamp,
nonceStr: dataForWeixin.nonceStr,
signature: dataForWeixin.signature,
jsApiList: dataForWeixin.jsApiList
});
</script>
<div style="height:0px;overflow:hidden;">
<img src="服务器上需要显示的图片路径" />
</div>
在控制器中加入一个 获取微信分享接口的参数如下(再程序入口调用此方法即可)
/// <summary> /// 获取微信分享接口参数 /// </summary> public void GetWX() { string app_id = ConfigHelper.AppId; string AppSecret = ConfigHelper.AppSecret; writeLog.WriteLogs("app_id,AppSecret：" + app_id + AppSecret + ""); Wx_helper jssdk = new Wx_helper(app_id, AppSecret); Hashtable ht = jssdk.getSignPackage(); // 遍历哈希表 foreach (DictionaryEntry de in ht) { if (de.Key.ToString() == "appId") { ViewBag.appid = de.Value.ToString(); } if (de.Key.ToString() == "nonceStr") { ViewBag.nonceStr = de.Value.ToString(); } if (de.Key.ToString() == "timestamp") { ViewBag.timestamp = de.Value.ToString(); } if (de.Key.ToString() == "url") { ViewBag.url = de.Value.ToString(); } if (de.Key.ToString() == "signature") { ViewBag.signature = de.Value.ToString(); } if (de.Key.ToString() == "jsapiTicket") { ViewBag.jsapiTicket = de.Value.ToString(); } } }
上述代码中 Wx_helper(微信接口类)如下：
/// <summary> /// 微信接口类 /// </summary> public class Wx_helper : DBBase { private string appId; private string appSecret; private DataTable DT; public Wx_helper(string appId, string appSecret) { this.appId = appId; this.appSecret = appSecret; } //得到数据包，返回使用页面 public System.Collections.Hashtable getSignPackage() { string jsapiTicket = getJsApiTicket(); string url_req = HttpContext.Current.Request.Url.ToString(); //当前网页的URL string pageurl = HttpContext.Current.Request.Url.AbsoluteUri; writeLog.WriteLogs("当前网页的URL：" + pageurl + ""); //string url = "http://" + HttpContext.Current.Request.ServerVariables["Http_Host"] + HttpContext.Current.Request.ApplicationPath; string timestamp = Convert.ToString(ConvertDateTimeInt(DateTime.Now)); string nonceStr = createNonceStr(); // 这里参数的顺序要按照 key 值 ASCII 码升序排序 string rawstring = "jsapi_ticket=" + jsapiTicket + "&noncestr=" + nonceStr + "×tamp=" + timestamp + "&url=" + pageurl + ""; writeLog.WriteLogs("rawstring：" + rawstring + ""); string signature = SHA1_Hash(rawstring); System.Collections.Hashtable signPackage = new System.Collections.Hashtable(); signPackage.Add("appId", appId); signPackage.Add("nonceStr", nonceStr); signPackage.Add("timestamp", timestamp); signPackage.Add("url", pageurl); signPackage.Add("signature", signature); signPackage.Add("jsapiTicket", jsapiTicket); return signPackage; } //创建随机字符串 private string createNonceStr() { int length = 16; string chars = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789"; string str = ""; Random rad = new Random(); for (int i = 0; i < length; i++) { str += chars.Substring(rad.Next(0, chars.Length - 1), 1); } return str; } //SHA1哈希加密算法 public string SHA1_Hash(string str_sha1_in) { SHA1 sha1 = new SHA1CryptoServiceProvider(); byte[] bytes_sha1_in = System.Text.UTF8Encoding.Default.GetBytes(str_sha1_in); byte[] bytes_sha1_out = sha1.ComputeHash(bytes_sha1_in); string str_sha1_out = BitConverter.ToString(bytes_sha1_out); str_sha1_out = str_sha1_out.Replace("-", "").ToLower(); return str_sha1_out; } //得到ticket 如果文件里时间 超时则重新获取 private string getJsApiTicket() { //这里我从数据库读取 string strSql = "select jsapi_ticket,ticket_expires,add_time from dt_weixin_jsapiticket where ID=1"; DataSet ds = sqlhelp.ExecuteDataSet(strSql); DT = ds.Tables[0]; int expire_time = Convert.ToInt32(DT.Rows[0]["ticket_expires"]); string ticket = DT.Rows[0]["jsapi_ticket"].ToString(); string error = string.Empty; string accessToken = new WXCRMComm().GetAccessToken(out error); //获取系统的全局token writeLog.WriteLogs("获取系统的全局token ：" + accessToken + "," + error + ""); if (string.IsNullOrEmpty(error)) { writeLog.WriteLogs("GetAccessToken：" + error + ""); //计算时间判断是否过期 TimeSpan ts = DateTime.Now - DateTime.Parse(DT.Rows[0]["add_time"].ToString()); double chajunSecond = ts.TotalSeconds; if (chajunSecond >= 1200) { writeLog.WriteLogs("jsapiticket计算时间判断是否过期：已过期"); string url = "https://api.weixin.qq.com/cgi-bin/ticket/getticket?access_token=" + accessToken + "&type=jsapi"; Jsapi api = JsonConvert.DeserializeObject<Jsapi>(httpGet(url)); ticket = api.ticket; if (ticket != "") { expire_time = ConvertDateTimeInt(DateTime.Now) + 1200; //存入数据库操作 strSql = " update dt_weixin_jsapiticket set jsapi_ticket=‘" + ticket + "‘,ticket_expires=‘" + expire_time + "‘,add_time=‘" + DateTime.Now + "‘ where ID=1"; sqlhelp.ExecuteNonQuery(strSql); } } writeLog.WriteLogs("jsapiticket计算时间判断是否过期：没过期"); } return ticket; } /// <summary> /// 将c# DateTime时间格式转换为Unix时间戳格式 /// </summary> /// <param name="time">时间</param> /// <returns>double</returns> public int ConvertDateTimeInt(System.DateTime time) { int intResult = 0; System.DateTime startTime = TimeZone.CurrentTimeZone.ToLocalTime(new System.DateTime(1970, 1, 1)); intResult = Convert.ToInt32((time - startTime).TotalSeconds); return intResult; } //发起一个http请球，返回值 private string httpGet(string url) { try { WebClient MyWebClient = new WebClient(); MyWebClient.Credentials = CredentialCache.DefaultCredentials;//获取或设置用于向Internet资源的请求进行身份验证的网络凭据 Byte[] pageData = MyWebClient.DownloadData(url); //从指定网站下载数据 string pageHtml = System.Text.Encoding.Default.GetString(pageData); //如果获取网站页面采用的是GB2312，则使用这句 return pageHtml; } catch (WebException webEx) { Console.WriteLine(webEx.Message.ToString()); return null; } } #region 创建Json序列化 及反序列化类目 // /// <summary> /// 创建JSon类 保存文件 jsapi_ticket.json /// </summary> public class JSTicket { public string jsapi_ticket { get; set; } public double expire_time { get; set; } } /// <summary> /// 创建 JSon类 保存文件 access_token.json /// </summary> public class AccToken { public string access_token { get; set; } public double expires_in { get; set; } } /// <summary> /// 创建从微信返回结果的一个类 用于获取ticket /// </summary> public class Jsapi { public int errcode { get; set; } public string errmsg { get; set; } public string ticket { get; set; } public string expires_in { get; set; } } #endregion }
上述代码中负责获取和刷新的 WXCRMComm 类如下：
/// <summary> /// 负责获取或刷新AccessToken /// </summary> public class WXCRMComm { public WXCRMComm() { } private string appid; private string appsecret; BLLweixin_access_token tokenBLL = new BLL.BLLweixin_access_token(); //账户AccessToken 此处根据自己项目单独特别处理 BLLweixin_account accountBLL = new BLL.BLLweixin_account(); //公众平台账户 此处根据自己项目单独特别处理 /// <summary> /// 及时获得access_token值 /// access_token是公众号的全局唯一票据，公众号调用各接口时都需使用access_token。正常情况下access_token有效期为7200秒， /// 重复获取将导致上次获取的access_token失效。 /// 每日限额获取access_token.我们将access_token保存到数据库里，间隔时间为20分钟，从微信公众平台获得一次。 /// </summary> public string GetAccessToken(out string error) { string access_token = string.Empty; error = string.Empty; try { Model.weixin_account accountModel = accountBLL.GetModel(); //公众平台账户信息 if (accountModel == null || string.IsNullOrEmpty(accountModel.appid) || string.IsNullOrEmpty(accountModel.appsecret)) { error = "AppId或者AppSecret未填写,请在补全信息！"; return string.Empty; } //没有找到该账户则获取AccessToKen写入存储1200秒 if (!tokenBLL.Exists()) { var result = Senparc.Weixin.MP.CommonAPIs.CommonApi.GetToken(accountModel.appid, accountModel.appsecret); access_token = result.access_token; tokenBLL.Add(access_token); return access_token; } //获取公众账户的实体 Model.weixin_access_token tokenModel = tokenBLL.GetModel(); //计算时间判断是否过期 TimeSpan ts = DateTime.Now - tokenModel.add_time; double chajunSecond = ts.TotalSeconds; if (string.IsNullOrEmpty(tokenModel.access_token) || chajunSecond >= tokenModel.expires_in) { writeLog.WriteLogs("GetAccessToken：重新修改"); //从微信平台重新获得AccessToken var result = Senparc.Weixin.MP.CommonAPIs.CommonApi.GetToken(accountModel.appid, accountModel.appsecret); access_token = result.access_token; //更新到数据库里的AccessToken tokenModel.access_token = access_token; tokenModel.add_time = DateTime.Now; bool ret=tokenBLL.Update(tokenModel); writeLog.WriteLogs("GetAccessToken：重新修改"+ ret + ""); } else { writeLog.WriteLogs("GetAccessToken：获取旧的"); access_token = tokenModel.access_token; } } catch (Exception ex) { error = "获取AccessToken出错:" + ex.Message; } return access_token; } /// <summary> ///【强制刷新】access_token值 /// access_token是公众号的全局唯一票据，公众号调用各接口时都需使用access_token。正常情况下access_token有效期为7200秒， /// 重复获取将导致上次获取的access_token失效。 /// 每日限额获取access_token.我们将access_token保存到数据库里，间隔时间为20分钟，从微信公众平台获得一次。 /// </summary> /// <returns></returns> public string FlushAccessToken(out string error) { string access_token = string.Empty; error = string.Empty; try { Model.weixin_account accountModel = accountBLL.GetModel(); //公众平台账户信息 if (string.IsNullOrEmpty(accountModel.appid) || string.IsNullOrEmpty(accountModel.appsecret)) { error = "AppId或者AppSecret未填写,请在补全信息！"; return ""; } var result = Senparc.Weixin.MP.CommonAPIs.CommonApi.GetToken(accountModel.appid, accountModel.appsecret); access_token = result.access_token; //没有找到该账户则获取AccessToKen写入存储1200秒 if (!tokenBLL.Exists()) { tokenBLL.Add(access_token); } else { //获取公众账户的实体 Model.weixin_access_token tokenModel = tokenBLL.GetModel(); //更新到数据库里的AccessToken tokenModel.access_token = access_token; tokenModel.add_time = DateTime.Now; tokenBLL.Update(tokenModel); } } catch (Exception ex) { error = "获得AccessToken出错:" + ex.Message; } return access_token; } /// <summary> /// 获得所有关注用户的openid字符串(别的方法调用此方法) /// </summary> private IList<string> BaseUserOpenId(out string error) { IList<string> ret = new List<string>(); string access_token = GetAccessToken(out error); if (error != "") { return null; } Senparc.Weixin.MP.AdvancedAPIs.User.OpenIdResultJson openidJson = Senparc.Weixin.MP.AdvancedAPIs.UserApi.Get(access_token, string.Empty); if (openidJson.count == openidJson.total) { ret = openidJson.data.openid; } else { GetNextUserOpenId(openidJson.next_openid, ret); } return ret; } /// <summary> /// (基础方法)获得所有关注用户的openid字符串(递归算法) /// </summary> private void GetNextUserOpenId(string nexOpenid, IList<string> openidList) { string err = string.Empty; string access_token = GetAccessToken(out err); Senparc.Weixin.MP.AdvancedAPIs.User.OpenIdResultJson openidJson = Senparc.Weixin.MP.AdvancedAPIs.UserApi.Get(access_token, nexOpenid); if (openidJson == null || openidJson.count <= 0) { return; } else { for (int i = 0; i < openidJson.data.openid.Count; i++) { openidList.Add(openidJson.data.openid[i]); } GetNextUserOpenId(openidJson.next_openid, openidList); } } #region 消息群发处理=================================== /// <summary> /// 上传永久素材 /// </summary> public string UploadForeverMedia(string imgFullPath, out string error) { string accessToken = GetAccessToken(out error); if (!string.IsNullOrEmpty(error)) { return string.Empty; } var result = Senparc.Weixin.MP.AdvancedAPIs.MediaApi.UploadForeverMedia(accessToken, imgFullPath); if (result.errcode == 0) { return result.media_id; } error = result.errmsg; return string.Empty; } /// <summary> /// 删除永久素材 /// </summary> public bool DeleteForeverMedia(string mediaId, out string error) { string accessToken = GetAccessToken(out error); if (!string.IsNullOrEmpty(error)) { return false; } var result = Senparc.Weixin.MP.AdvancedAPIs.MediaApi.DeleteForeverMedia(accessToken, mediaId); if (result.errcode != 0) { error = result.errmsg; return false; } error = string.Empty; return true; } /// <summary> /// 群发消息 /// </summary> public bool SendGroupMessageByGroupId(List<Senparc.Weixin.MP.AdvancedAPIs.GroupMessage.NewsModel> ls, out string error) { string accessToken = GetAccessToken(out error); //新增素材 var result1 = Senparc.Weixin.MP.AdvancedAPIs.MediaApi.UploadNews(accessToken, 10000, ls.ToArray()); if (result1.errcode != 0) { error = result1.errmsg; return false; } //群发消息 var result2 = Senparc.Weixin.MP.AdvancedAPIs.GroupMessageApi.SendGroupMessageByGroupId(accessToken, "0", result1.media_id, Senparc.Weixin.MP.GroupMessageType.mpnews, true); if (result2.errcode != 0) { error = result2.errmsg; return false; } error = string.Empty; return true; } #endregion }
另外，微信接口类 继承 了 DBBase ，里面只有连接数据库对象，这个可以根据自己项目特点和需要处理 这里只做演示：
public class DBBase { protected static string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; public SqlHelper sqlhelp = new SqlHelper(connectionString); }
上述代码中SqlHelper 通用帮助类 也贴一下吧，仅供参考
/// <summary> /// SqlHelper操作类 /// </summary> public sealed partial class SqlHelper { /// <summary> /// 批量操作每批次记录数 /// </summary> public static int BatchSize = 2000; /// <summary> /// 超时时间 /// </summary> public static int CommandTimeOut = 600; /// <summary> ///初始化SqlHelper实例 /// </summary> /// <param name="connectionString">数据库连接字符串</param> public SqlHelper(string connectionString) { this.ConnectionString = connectionString; } /// <summary> /// 数据库连接字符串 /// </summary> public string ConnectionString { get; set; } #region 实例方法 #region ExecuteNonQuery /// <summary> /// 执行SQL语句,返回影响的行数 /// </summary> /// <param name="commandText">SQL语句</param> /// <param name="parms">查询参数</param> /// <returns>返回影响的行数</returns> public int ExecuteNonQuery(string commandText, params SqlParameter[] parms) { return ExecuteNonQuery(ConnectionString, CommandType.Text, commandText, parms); } /// <summary> /// 执行SQL语句,返回影响的行数 /// </summary> /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param> /// <param name="commandText">SQL语句或存储过程名称</param> /// <param name="parms">查询参数</param> /// <returns>返回影响的行数</returns> public int ExecuteNonQuery(CommandType commandType, string commandText, params SqlParameter[] parms) { return ExecuteNonQuery(ConnectionString, commandType, commandText, parms); } #endregion ExecuteNonQuery #region ExecuteScalar /// <summary> /// 执行SQL语句,返回结果集中的第一行第一列 /// </summary> /// <typeparam name="T">返回对象类型</typeparam> /// <param name="commandText">SQL语句</param> /// <param name="parms">查询参数</param> /// <returns>返回结果集中的第一行第一列</returns> public T ExecuteScalar<T>(string commandText, params SqlParameter[] parms) { return ExecuteScalar<T>(ConnectionString, commandText, parms); } /// <summary> /// 执行SQL语句,返回结果集中的第一行第一列 /// </summary> /// <param name="commandText">SQL语句</param> /// <param name="parms">查询参数</param> /// <returns>返回结果集中的第一行第一列</returns> public object ExecuteScalar(string commandText, params SqlParameter[] parms) { return ExecuteScalar(ConnectionString, CommandType.Text, commandText, parms); } /// <summary> /// 执行SQL语句,返回结果集中的第一行第一列 /// </summary> /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param> /// <param name="commandText">SQL语句或存储过程名称</param> /// <param name="parms">查询参数</param> /// <returns>返回结果集中的第一行第一列</returns> public object ExecuteScalar(CommandType commandType, string commandText, params SqlParameter[] parms) { return ExecuteScalar(ConnectionString, commandType, commandText, parms); } #endregion ExecuteScalar #region ExecuteDataReader /// <summary> /// 执行SQL语句,返回只读数据集 /// </summary> /// <param name="commandText">SQL语句</param> /// <param name="parms">查询参数</param> /// <returns>返回只读数据集</returns> private SqlDataReader ExecuteDataReader(string commandText, params SqlParameter[] parms) { return ExecuteDataReader(ConnectionString, CommandType.Text, commandText, parms); } /// <summary> /// 执行SQL语句,返回只读数据集 /// </summary> /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param> /// <param name="commandText">SQL语句或存储过程名称</param> /// <param name="parms">查询参数</param> /// <returns>返回只读数据集</returns> private SqlDataReader ExecuteDataReader(CommandType commandType, string commandText, params SqlParameter[] parms) { return ExecuteDataReader(ConnectionString, commandType, commandText, parms); } #endregion #region ExecuteDataRow /// <summary> /// 执行SQL语句,返回结果集中的第一行 /// </summary> /// <param name="commandText">SQL语句</param> /// <param name="parms">查询参数</param> /// <returns>返回结果集中的第一行</returns> public DataRow ExecuteDataRow(string commandText, params SqlParameter[] parms) { return ExecuteDataRow(ConnectionString, CommandType.Text, commandText, parms); } /// <summary> /// 执行SQL语句,返回结果集中的第一行 /// </summary> /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param> /// <param name="commandText">SQL语句或存储过程名称</param> /// <param name="parms">查询参数</param> /// <returns>返回结果集中的第一行</returns> public DataRow ExecuteDataRow(CommandType commandType, string commandText, params SqlParameter[] parms) { return ExecuteDataRow(ConnectionString, commandType, commandText, parms); } #endregion ExecuteDataRow #region ExecuteDataTable /// <summary> /// 执行SQL语句,返回结果集中的第一个数据表 /// </summary> /// <param name="commandText">SQL语句</param> /// <param name="parms">查询参数</param> /// <returns>返回结果集中的第一个数据表</returns> public DataTable ExecuteDataTable(string commandText, params SqlParameter[] parms) { return ExecuteDataTable(ConnectionString, CommandType.Text, commandText, parms); } /// <summary> /// 执行SQL语句,返回结果集中的第一个数据表 /// </summary> /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param> /// <param name="commandText">SQL语句或存储过程名称</param> /// <param name="parms">查询参数</param> /// <returns>返回结果集中的第一个数据表</returns> public DataTable ExecuteDataTable(CommandType commandType, string commandText, params SqlParameter[] parms) { return ExecuteDataSet(ConnectionString, commandType, commandText, parms).Tables[0]; } /// <summary> /// 执行SQL语句,返回结果集中的第一个数据表 /// </summary> /// <param name="sql">SQL语句</param> /// <param name="order">排序SQL,如"ORDER BY ID DESC"</param> /// <param name="pageSize">每页记录数</param> /// <param name="pageIndex">页索引</param> /// <param name="parms">查询参数</param> /// <param name="query">查询SQL</param> /// <returns></returns> public DataTable ExecutePageDataTable(string sql, string order, int pageSize, int pageIndex, SqlParameter[] parms = null, string query = null, string cte = null) { return ExecutePageDataTable(sql, order, pageSize, pageIndex, parms, query, cte); } #endregion ExecuteDataTable #region ExecuteDataSet /// <summary> /// 执行查询语句，返回DataSet /// </summary> /// <param name="SQLString">查询语句</param> /// <returns>DataSet</returns> public DataSet ExecuteDataSet(string SQLString) { return ExecuteDataSet(SQLString, ConnectionString); } /// <summary> /// 执行SQL语句,返回结果集 /// </summary> /// <param name="commandText">SQL语句</param> /// <param name="parms">查询参数</param> /// <returns>返回结果集</returns> public DataSet ExecuteDataSet(string commandText, params SqlParameter[] parms) { return ExecuteDataSet(ConnectionString, CommandType.Text, commandText, parms); } /// <summary> /// 执行SQL语句,返回结果集 /// </summary> /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param> /// <param name="commandText">SQL语句或存储过程名称</param> /// <param name="parms">查询参数</param> /// <returns>返回结果集</returns> public DataSet ExecuteDataSet(CommandType commandType, string commandText, params SqlParameter[] parms) { return ExecuteDataSet(ConnectionString, commandType, commandText, parms); } public object GetSingle(string SQLString, params SqlParameter[] cmdParms) { return GetSingle(SQLString, ConnectionString, cmdParms); } #endregion ExecuteDataSet #region 批量操作 /// <summary> /// 大批量数据插入 /// </summary> /// <param name="table">数据表</param> public void BulkInsert(DataTable table) { BulkInsert(ConnectionString, table); } /// <summary> /// 使用MySqlDataAdapter批量更新数据 /// </summary> /// <param name="table">数据表</param> public void BatchUpdate(DataTable table) { BatchUpdate(ConnectionString, table); } /// <summary> /// 分批次批量删除数据 /// </summary> /// <param name="sql">SQL语句</param> /// <param name="batchSize">每批次删除记录行数</param> /// <param name="interval">批次执行间隔(秒)</param> public void BatchDelete(string sql, int batchSize = 1000, int interval = 1) { BatchDelete(ConnectionString, sql, batchSize, interval); } /// <summary> /// 分批次批量更新数据 /// </summary> /// <param name="sql">SQL语句</param> /// <param name="batchSize">每批次更新记录行数</param> /// <param name="interval">批次执行间隔(秒)</param> public void BatchUpdate(string sql, int batchSize = 1000, int interval = 1) { BatchUpdate(ConnectionString, sql, batchSize, interval); } #endregion 批量操作 #endregion 实例方法 #region 静态方法 public static object GetSingle(string SQLString, string connectionString, params SqlParameter[] cmdParms) { using (SqlConnection connection = new SqlConnection(connectionString)) { using (SqlCommand cmd = new SqlCommand()) { try { PrepareCommand(cmd, connection, null, SQLString, cmdParms); object obj = cmd.ExecuteScalar(); cmd.Parameters.Clear(); if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) { return null; } else { return obj; } } catch (System.Data.SqlClient.SqlException e) { throw e; } } } } private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms) { if (conn.State != ConnectionState.Open) conn.Open(); cmd.Connection = conn; cmd.CommandText = cmdText; if (trans != null) cmd.Transaction = trans; cmd.CommandType = CommandType.Text;//cmdType; if (cmdParms != null) { foreach (SqlParameter parameter in cmdParms) { if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && (parameter.Value == null)) { parameter.Value = DBNull.Value; } cmd.Parameters.Add(parameter); } } } private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] parms) { if (connection.State != ConnectionState.Open) connection.Open(); command.Connection = connection; command.CommandTimeout = CommandTimeOut; // 设置命令文本(存储过程名或SQL语句) command.CommandText = commandText; // 分配事务 if (transaction != null) { command.Transaction = transaction; } // 设置命令类型. command.CommandType = commandType; if (parms != null && parms.Length > 0) { //预处理SqlParameter参数数组，将为NULL的参数赋值为DBNull.Value; foreach (SqlParameter parameter in parms) { if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && (parameter.Value == null)) { parameter.Value = DBNull.Value; } } command.Parameters.AddRange(parms); } } #region ExecuteNonQuery /// <summary> /// 执行SQL语句,返回影响的行数 /// </summary> /// <param name="connectionString">数据库连接字符串</param> /// <param name="commandText">SQL语句</param> /// <param name="parms">查询参数</param> /// <returns>返回影响的行数</returns> public static int ExecuteNonQuery(string connectionString, string commandText, params SqlParameter[] parms) { using (SqlConnection connection = new SqlConnection(connectionString)) { return ExecuteNonQuery(connection, CommandType.Text, commandText, parms); } } /// <summary> /// 执行SQL语句,返回影响的行数 /// </summary> /// <param name="connectionString">数据库连接字符串</param> /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param> /// <param name="commandText">SQL语句或存储过程名称</param> /// <param name="parms">查询参数</param> /// <returns>返回影响的行数</returns> public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, params SqlParameter[] parms) { using (SqlConnection connection = new SqlConnection(connectionString)) { return ExecuteNonQuery(connection, commandType, commandText, parms); } } /// <summary> /// 执行SQL语句,返回影响的行数 /// </summary> /// <param name="connection">数据库连接</param> /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param> /// <param name="commandText">SQL语句或存储过程名称</param> /// <param name="parms">查询参数</param> /// <returns>返回影响的行数</returns> public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] parms) { return ExecuteNonQuery(connection, null, commandType, commandText, parms); } /// <summary> /// 执行SQL语句,返回影响的行数 /// </summary> /// <param name="transaction">事务</param> /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param> /// <param name="commandText">SQL语句或存储过程名称</param> /// <param name="parms">查询参数</param> /// <returns>返回影响的行数</returns> public static int ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] parms) { return ExecuteNonQuery(transaction.Connection, transaction, commandType, commandText, parms); } /// <summary> /// 执行SQL语句,返回影响的行数 /// </summary> /// <param name="connection">数据库连接</param> /// <param name="transaction">事务</param> /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param> /// <param name="commandText">SQL语句或存储过程名称</param> /// <param name="parms">查询参数</param> /// <returns>返回影响的行数</returns> private static int ExecuteNonQuery(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] parms) { SqlCommand command = new SqlCommand(); PrepareCommand(command, connection, transaction, commandType, commandText, parms); int retval = command.ExecuteNonQuery(); command.Parameters.Clear(); return retval; } #endregion ExecuteNonQuery #region ExecuteScalar /// <summary> /// 执行SQL语句,返回结果集中的第一行第一列 /// </summary> /// <typeparam name="T">返回对象类型</typeparam> /// <param name="connectionString">数据库连接字符串</param> /// <param name="commandText">SQL语句</param> /// <param name="parms">查询参数</param> /// <returns>返回结果集中的第一行第一列</returns> public static T ExecuteScalar<T>(string connectionString, string commandText, params SqlParameter[] parms) { object result = ExecuteScalar(connectionString, commandText, parms); if (result != null) { return (T)Convert.ChangeType(result, typeof(T)); ; } return default(T); } /// <summary> /// 执行SQL语句,返回结果集中的第一行第一列 /// </summary> /// <param name="connectionString">数据库连接字符串</param> /// <param name="commandText">SQL语句</param> /// <param name="parms">查询参数</param> /// <returns>返回结果集中的第一行第一列</returns> public static object ExecuteScalar(string connectionString, string commandText, params SqlParameter[] parms) { using (SqlConnection connection = new SqlConnection(connectionString)) { return ExecuteScalar(connection, CommandType.Text, commandText, parms); } } /// <summary> /// 执行SQL语句,返回结果集中的第一行第一列 /// </summary> /// <param name="connectionString">数据库连接字符串</param> /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param> /// <param name="commandText">SQL语句或存储过程名称</param> /// <param name="parms">查询参数</param> /// <returns>返回结果集中的第一行第一列</returns> public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText, params SqlParameter[] parms) { using (SqlConnection connection = new SqlConnection(connectionString)) { return ExecuteScalar(connection, commandType, commandText, parms); } } /// <summary> /// 执行SQL语句,返回结果集中的第一行第一列 /// </summary> /// <param name="connection">数据库连接</param> /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param> /// <param name="commandText">SQL语句或存储过程名称</param> /// <param name="parms">查询参数</param> /// <returns>返回结果集中的第一行第一列</returns> public static object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] parms) { return ExecuteScalar(connection, null, commandType, commandText, parms); } /// <summary> /// 执行SQL语句,返回结果集中的第一行第一列 /// </summary> /// <param name="transaction">事务</param> /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param> /// <param name="commandText">SQL语句或存储过程名称</param> /// <param name="parms">查询参数</param> /// <returns>返回结果集中的第一行第一列</returns> public static object ExecuteScalar(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] parms) { return ExecuteScalar(transaction.Connection, transaction, commandType, commandText, parms); } /// <summary> /// 执行SQL语句,返回结果集中的第一行第一列 /// </summary> /// <param name="connection">数据库连接</param> /// <param name="transaction">事务</param> /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param> /// <param name="commandText">SQL语句或存储过程名称</param> /// <param name="parms">查询参数</param> /// <returns>返回结果集中的第一行第一列</returns> private static object ExecuteScalar(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] parms) { SqlCommand command = new SqlCommand(); PrepareCommand(command, connection, transaction, commandType, commandText, parms); object retval = command.ExecuteScalar(); command.Parameters.Clear(); return retval; } #endregion ExecuteScalar #region ExecuteDataReader /// <summary> /// 执行SQL语句,返回只读数据集 /// </summary> /// <param name="connection">数据库连接</param> /// <param name="commandText">SQL语句</param> /// <param name="parms">查询参数</param> /// <returns>返回只读数据集</returns> private static SqlDataReader ExecuteDataReader(string connectionString, string commandText, params SqlParameter[] parms) { SqlConnection connection = new SqlConnection(connectionString); return ExecuteDataReader(connection, null, CommandType.Text, commandText, parms); } /// <summary> /// 执行SQL语句,返回只读数据集 /// </summary> /// <param name="connection">数据库连接</param> /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param> /// <param name="commandText">SQL语句或存储过程名称</param> /// <param name="parms">查询参数</param> /// <returns>返回只读数据集</returns> private static SqlDataReader ExecuteDataReader(string connectionString, CommandType commandType, string commandText, params SqlParameter[] parms) { SqlConnection connection = new SqlConnection(connectionString); return ExecuteDataReader(connection, null, commandType, commandText, parms); } /// <summary> /// 执行SQL语句,返回只读数据集 /// </summary> /// <param name="connection">数据库连接</param> /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param> /// <param name="commandText">SQL语句或存储过程名称</param> /// <param name="parms">查询参数</param> /// <returns>返回只读数据集</returns> private static SqlDataReader ExecuteDataReader(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] parms) { return ExecuteDataReader(connection, null, commandType, commandText, parms); } /// <summary> /// 执行SQL语句,返回只读数据集 /// </summary> /// <param name="transaction">事务</param> /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param> /// <param name="commandText">SQL语句或存储过程名称</param> /// <param name="parms">查询参数</param> /// <returns>返回只读数据集</returns> private static SqlDataReader ExecuteDataReader(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] parms) { return ExecuteDataReader(transaction.Connection, transaction, commandType, commandText, parms); } /// <summary> /// 执行SQL语句,返回只读数据集 /// </summary> /// <param name="connection">数据库连接</param> /// <param name="transaction">事务</param> /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param> /// <param name="commandText">SQL语句或存储过程名称</param> /// <param name="parms">查询参数</param> /// <returns>返回只读数据集</returns> private static SqlDataReader ExecuteDataReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] parms) { SqlCommand command = new SqlCommand(); PrepareCommand(command, connection, transaction, commandType, commandText, parms); return command.ExecuteReader(CommandBehavior.CloseConnection); } #endregion #region ExecuteDataRow /// <summary> /// 执行SQL语句,返回结果集中的第一行 /// </summary> /// <param name="connectionString">数据库连接字符串</param> /// <param name="commandText">SQL语句</param> /// <param name="parms">查询参数</param> /// <returns>,返回结果集中的第一行</returns> public static DataRow ExecuteDataRow(string connectionString, string commandText, params SqlParameter[] parms) { DataTable dt = ExecuteDataTable(connectionString, CommandType.Text, commandText, parms); return dt.Rows.Count > 0 ? dt.Rows[0] : null; } /// <summary> /// 执行SQL语句,返回结果集中的第一行 /// </summary> /// <param name="connectionString">数据库连接字符串</param> /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param> /// <param name="commandText">SQL语句或存储过程名称</param> /// <param name="parms">查询参数</param> /// <returns>,返回结果集中的第一行</returns> public static DataRow ExecuteDataRow(string connectionString, CommandType commandType, string commandText, params SqlParameter[] parms) { DataTable dt = ExecuteDataTable(connectionString, commandType, commandText, parms); return dt.Rows.Count > 0 ? dt.Rows[0] : null; } /// <summary> /// 执行SQL语句,返回结果集中的第一行 /// </summary> /// <param name="connection">数据库连接</param> /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param> /// <param name="commandText">SQL语句或存储过程名称</param> /// <param name="parms">查询参数</param> /// <returns>,返回结果集中的第一行</returns> public static DataRow ExecuteDataRow(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] parms) { DataTable dt = ExecuteDataTable(connection, commandType, commandText, parms); return dt.Rows.Count > 0 ? dt.Rows[0] : null; } /// <summary> /// 执行SQL语句,返回结果集中的第一行 /// </summary> /// <param name="transaction">事务</param> /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param> /// <param name="commandText">SQL语句或存储过程名称</param> /// <param name="parms">查询参数</param> /// <returns>,返回结果集中的第一行</returns> public static DataRow ExecuteDataRow(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] parms) { DataTable dt = ExecuteDataTable(transaction, commandType, commandText, parms); return dt.Rows.Count > 0 ? dt.Rows[0] : null; } #endregion ExecuteDataRow #region ExecuteDataTable /// <summary> /// 执行SQL语句,返回结果集中的第一个数据表 /// </summary> /// <param name="connectionString">数据库连接字符串</param> /// <param name="commandText">SQL语句</param> /// <param name="parms">查询参数</param> /// <returns>返回结果集中的第一个数据表</returns> public static DataTable ExecuteDataTable(string connectionString, string commandText, params SqlParameter[] parms) { return ExecuteDataSet(connectionString, CommandType.Text, commandText, parms).Tables[0]; } /// <summary> /// 执行SQL语句,返回结果集中的第一个数据表 /// </summary> /// <param name="connectionString">数据库连接字符串</param> /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param> /// <param name="commandText">SQL语句或存储过程名称</param> /// <param name="parms">查询参数</param> /// <returns>返回结果集中的第一个数据表</returns> public static DataTable ExecuteDataTable(string connectionString, CommandType commandType, string commandText, params SqlParameter[] parms) { return ExecuteDataSet(connectionString, commandType, commandText, parms).Tables[0]; } /// <summary> /// 执行SQL语句,返回结果集中的第一个数据表 /// </summary> /// <param name="connection">数据库连接</param> /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param> /// <param name="commandText">SQL语句或存储过程名称</param> /// <param name="parms">查询参数</param> /// <returns>返回结果集中的第一个数据表</returns> public static DataTable ExecuteDataTable(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] parms) { return ExecuteDataSet(connection, commandType, commandText, parms).Tables[0]; } /// <summary> /// 执行SQL语句,返回结果集中的第一个数据表 /// </summary> /// <param name="transaction">事务</param> /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param> /// <param name="commandText">SQL语句或存储过程名称</param> /// <param name="parms">查询参数</param> /// <returns>返回结果集中的第一个数据表</returns> public static DataTable ExecuteDataTable(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] parms) { return ExecuteDataSet(transaction, commandType, commandText, parms).Tables[0]; } /// <summary> /// 获取空表结构 /// </summary> /// <param name="connectionString">数据库连接字符串</param> /// <param name="tableName">数据表名称</param> /// <returns>返回结果集中的第一个数据表</returns> public static DataTable ExecuteEmptyDataTable(string connectionString, string tableName) { return ExecuteDataSet(connectionString, CommandType.Text, string.Format("select * from {0} where 1=-1", tableName)).Tables[0]; } /// <summary> /// 执行SQL语句,返回结果集中的第一个数据表 /// </summary> /// <param name="connectionString">数据库连接字符串</param> /// <param name="sql">SQL语句</param> /// <param name="order">排序SQL,如"ORDER BY ID DESC"</param> /// <param name="pageSize">每页记录数</param> /// <param name="pageIndex">页索引</param> /// <param name="parms">查询参数</param> /// <param name="query">查询SQL</param> /// <param name="cte">CTE表达式</param> /// <returns></returns> public static DataTable ExecutePageDataTable(string connectionString, string sql, string order, int pageSize, int pageIndex, SqlParameter[] parms = null, string query = null, string cte = null) { string psql = string.Format(@" {3} SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY {1}) RowNumber,* FROM ( {0} ) t WHERE 1 = 1 {2} ) t WHERE RowNumber BETWEEN @RowNumber_Begin AND @RowNumber_End", sql, order, query, cte); List<SqlParameter> paramlist = new List<SqlParameter>() { new SqlParameter("@RowNumber_Begin", SqlDbType.Int){ Value = (pageIndex - 1) * pageSize + 1 }, new SqlParameter("@RowNumber_End", SqlDbType.Int){ Value = pageIndex * pageSize } }; if (parms != null) paramlist.AddRange(parms); return ExecuteDataTable(connectionString, psql, paramlist.ToArray()); } #endregion ExecuteDataTable #region ExecuteDataSet /// <summary> /// 执行查询语句，返回DataSet /// </summary> /// <param name="SQLString">查询语句</param> /// <returns>DataSet</returns> public static DataSet ExecuteDataSet(string SQLString, string connectionString) { using (SqlConnection connection = new SqlConnection(connectionString)) { DataSet ds = new DataSet(); try { connection.Open(); SqlDataAdapter command = new SqlDataAdapter(SQLString, connection); command.Fill(ds, "ds"); } catch (System.Data.SqlClient.SqlException ex) { throw new Exception(ex.Message); } return ds; } } /// <summary> /// 执行SQL语句,返回结果集 /// </summary> /// <param name="connectionString">数据库连接字符串</param> /// <param name="commandText">SQL语句</param> /// <param name="parms">查询参数</param> /// <returns>返回结果集</returns> public static DataSet ExecuteDataSet(string connectionString, string commandText, params SqlParameter[] parms) { return ExecuteDataSet(connectionString, CommandType.Text, commandText, parms); } /// <summary> /// 执行SQL语句,返回结果集 /// </summary> /// <param name="connectionString">数据库连接字符串</param> /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param> /// <param name="commandText">SQL语句或存储过程名称</param> /// <param name="parms">查询参数</param> /// <returns>返回结果集</returns> public static DataSet ExecuteDataSet(string connectionString, CommandType commandType, string commandText, params SqlParameter[] parms) { using (SqlConnection connection = new SqlConnection(connectionString)) { return ExecuteDataSet(connection, commandType, commandText, parms); } } /// <summary> /// 执行SQL语句,返回结果集 /// </summary> /// <param name="connection">数据库连接</param> /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param> /// <param name="commandText">SQL语句或存储过程名称</param> /// <param name="parms">查询参数</param> /// <returns>返回结果集</returns> public static DataSet ExecuteDataSet(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] parms) { return ExecuteDataSet(connection, null, commandType, commandText, parms); } /// <summary> /// 执行SQL语句,返回结果集 /// </summary> /// <param name="transaction">事务</param> /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param> /// <param name="commandText">SQL语句或存储过程名称</param> /// <param name="parms">查询参数</param> /// <returns>返回结果集</returns> public static DataSet ExecuteDataSet(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] parms) { return ExecuteDataSet(transaction.Connection, transaction, commandType, commandText, parms); } /// <summary> /// 执行SQL语句,返回结果集 /// </summary> /// <param name="connection">数据库连接</param> /// <param name="transaction">事务</param> /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param> /// <param name="commandText">SQL语句或存储过程名称</param> /// <param name="parms">查询参数</param> /// <returns>返回结果集</returns> private static DataSet ExecuteDataSet(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] parms) { SqlCommand command = new SqlCommand(); PrepareCommand(command, connection, transaction, commandType, commandText, parms); SqlDataAdapter adapter = new SqlDataAdapter(command); DataSet ds = new DataSet(); adapter.Fill(ds); if (commandText.IndexOf("@") > 0) { commandText = commandText.ToLower(); int index = commandText.IndexOf("where "); if (index < 0) { index = commandText.IndexOf("\nwhere"); } if (index > 0) { ds.ExtendedProperties.Add("SQL", commandText.Substring(0, index - 1)); //将获取的语句保存在表的一个附属数组里，方便更新时生成CommandBuilder } else { ds.ExtendedProperties.Add("SQL", commandText); //将获取的语句保存在表的一个附属数组里，方便更新时生成CommandBuilder } } else { ds.ExtendedProperties.Add("SQL", commandText); //将获取的语句保存在表的一个附属数组里，方便更新时生成CommandBuilder } foreach (DataTable dt in ds.Tables) { dt.ExtendedProperties.Add("SQL", ds.ExtendedProperties["SQL"]); } command.Parameters.Clear(); return ds; } #endregion ExecuteDataSet #region 批量操作 /// <summary> /// 大批量数据插入 /// </summary> /// <param name="connectionString">数据库连接字符串</param> /// <param name="table">数据表</param> public static void BulkInsert(string connectionString, DataTable table) { if (string.IsNullOrEmpty(table.TableName)) throw new Exception("DataTable.TableName属性不能为空"); using (SqlBulkCopy bulk = new SqlBulkCopy(connectionString)) { bulk.BatchSize = BatchSize; bulk.BulkCopyTimeout = CommandTimeOut; bulk.DestinationTableName = table.TableName; foreach (DataColumn col in table.Columns) { bulk.ColumnMappings.Add(col.ColumnName, col.ColumnName); } bulk.WriteToServer(table); bulk.Close(); } } /// <summary> /// 使用MySqlDataAdapter批量更新数据 /// </summary> /// <param name="connectionString">数据库连接字符串</param> /// <param name="table">数据表</param> public static void BatchUpdate(string connectionString, DataTable table) { SqlConnection connection = new SqlConnection(connectionString); SqlCommand command = connection.CreateCommand(); command.CommandTimeout = CommandTimeOut; command.CommandType = CommandType.Text; SqlDataAdapter adapter = new SqlDataAdapter(command); SqlCommandBuilder commandBulider = new SqlCommandBuilder(adapter); commandBulider.ConflictOption = ConflictOption.OverwriteChanges; SqlTransaction transaction = null; try { connection.Open(); transaction = connection.BeginTransaction(); //设置批量更新的每次处理条数 adapter.UpdateBatchSize = BatchSize; //设置事物 adapter.SelectCommand.Transaction = transaction; if (table.ExtendedProperties["SQL"] != null) { adapter.SelectCommand.CommandText = table.ExtendedProperties["SQL"].ToString(); } adapter.Update(table); transaction.Commit();/////提交事务 } catch (SqlException ex) { if (transaction != null) transaction.Rollback(); throw ex; } finally { connection.Close(); connection.Dispose(); } } /// <summary> /// 分批次批量删除数据 /// </summary> /// <param name="connectionString">数据库连接字符串</param> /// <param name="sql">SQL语句</param> /// <param name="batchSize">每批次更新记录行数</param> /// <param name="interval">批次执行间隔(秒)</param> public static void BatchDelete(string connectionString, string sql, int batchSize = 1000, int interval = 1) { sql = sql.ToLower(); if (batchSize < 1000) batchSize = 1000; if (interval < 1) interval = 1; while (ExecuteScalar(connectionString, sql.Replace("delete", "select top 1 1")) != null) { ExecuteNonQuery(connectionString, CommandType.Text, sql.Replace("delete", string.Format("delete top ({0})", batchSize))); System.Threading.Thread.Sleep(interval * 1000); } } /// <summary> /// 分批次批量更新数据 /// </summary> /// <param name="connectionString">数据库连接字符串</param> /// <param name="sql">SQL语句</param> /// <param name="batchSize">每批次更新记录行数</param> /// <param name="interval">批次执行间隔(秒)</param> public static void BatchUpdate(string connectionString, string sql, int batchSize = 1000, int interval = 1) { if (batchSize < 1000) batchSize = 1000; if (interval < 1) interval = 1; string existsSql = Regex.Replace(sql, @"[\w\s.=,‘]*from", "select top 1 1 from", RegexOptions.IgnoreCase); existsSql = Regex.Replace(existsSql, @"set[\w\s.=,‘]* where", "where", RegexOptions.IgnoreCase); existsSql = Regex.Replace(existsSql, @"update", "select top 1 1 from", RegexOptions.IgnoreCase); while (ExecuteScalar<int>(connectionString, existsSql) != 0) { ExecuteNonQuery(connectionString, CommandType.Text, Regex.Replace(sql, "update", string.Format("update top ({0})", batchSize), RegexOptions.IgnoreCase)); System.Threading.Thread.Sleep(interval * 1000); } } #endregion 批量操作 #endregion 静态方法 }
最后 在原项目数据库需要新建三张数据表:分别记录公众号信息、access_token信息以及分享信息（dt_weixin_access_token、dt_weixin_account、dt_weixin_jsapiticket）
数据表结构如下：
USE [数据库名称] GO /****** Object: Table [dbo].[dt_weixin_access_token] Script Date: 2018-04-07 10:49:17 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[dt_weixin_access_token]( [id] [int] IDENTITY(1,1) NOT NULL, [account_id] [int] NULL, [access_token] [nvarchar](1000) NULL, [expires_in] [int] NULL CONSTRAINT [DF__dt_weixin__expir__233F2673] DEFAULT ((0)), [count] [int] NULL CONSTRAINT [DF__dt_weixin__count__24334AAC] DEFAULT ((0)), [add_time] [datetime] NULL CONSTRAINT [DF__dt_weixin__add_t__25276EE5] DEFAULT (getdate()), CONSTRAINT [PK_DT_WEIXIN_ACCESS_TOKEN] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[dt_weixin_account] Script Date: 2018-04-07 10:49:17 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[dt_weixin_account]( [id] [int] IDENTITY(1,1) NOT NULL, [name] [nvarchar](100) NULL, [originalid] [nvarchar](50) NULL, [wxcode] [nvarchar](50) NULL, [token] [nvarchar](300) NULL, [appid] [nvarchar](100) NULL, [appsecret] [nvarchar](150) NULL, [is_push] [tinyint] NULL CONSTRAINT [DF__dt_weixin__is_pu__19B5BC39] DEFAULT ((0)), [sort_id] [int] NULL CONSTRAINT [DF__dt_weixin__sort___1AA9E072] DEFAULT ((99)), [add_time] [datetime] NULL CONSTRAINT [DF__dt_weixin__add_t__1B9E04AB] DEFAULT (getdate()), CONSTRAINT [PK_DT_WEIXIN_ACCOUNT] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[dt_weixin_jsapiticket] Script Date: 2018-04-07 10:49:17 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[dt_weixin_jsapiticket]( [id] [int] NOT NULL, [jsapi_ticket] [varchar](500) NULL, [ticket_expires] [varchar](500) NULL, [add_time] [datetime] NULL CONSTRAINT [DF_dt_weixin_jsapiticket_add_time] DEFAULT (getdate()), CONSTRAINT [PK_dt_weixin_jsapiticket] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO SET IDENTITY_INSERT [dbo].[dt_weixin_access_token] ON GO INSERT [dbo].[dt_weixin_access_token] ([id], [account_id], [access_token], [expires_in], [count], [add_time]) VALUES (1, 1, N‘8_NX5IydeBm6ZEnUAzyXNKDIVJEJy-hzTbTaXd7w-q51P96XDcmFP2OYnJRyJ7rBAt9peJ-C5ad8RPkIDe8Vsm_LCdmOvyrVfWuotnTpCngXZFUVdbgCqOH03LBmWGMg69HRCp5ActsK2o269hUQJaAHAHDW‘, 1200, 1, CAST(N‘2018-04-06 22:23:14.870‘ AS DateTime)) GO SET IDENTITY_INSERT [dbo].[dt_weixin_access_token] OFF GO SET IDENTITY_INSERT [dbo].[dt_weixin_account] ON GO INSERT [dbo].[dt_weixin_account] ([id], [name], [originalid], [wxcode], [token], [appid], [appsecret], [is_push], [sort_id], [add_time]) VALUES (1, N‘WBC‘, N‘gh_f43eded4a607‘, N‘szsanfang‘, NULL, N‘AppId‘, N‘appSecret‘, 0, 99, CAST(N‘2018-04-03 14:51:19.310‘ AS DateTime)) GO SET IDENTITY_INSERT [dbo].[dt_weixin_account] OFF GO INSERT [dbo].[dt_weixin_jsapiticket] ([id], [jsapi_ticket], [ticket_expires], [add_time]) VALUES (1, N‘kgt8ON7yVITDhtdwci0qea8xYacVzuqZEqxNGwp-1WE0DTSI2wkMf1-e__jGosg516Xz2u9M-xsbXOd4eZBcMw‘, N‘1523025795‘, CAST(N‘2018-04-06 22:23:15.000‘ AS DateTime)) GO EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘自增ID‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘dt_weixin_access_token‘, @level2type=N‘COLUMN‘,@level2name=N‘id‘ GO EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘公众账户ID‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘dt_weixin_access_token‘, @level2type=N‘COLUMN‘,@level2name=N‘account_id‘ GO EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘access_token值‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘dt_weixin_access_token‘, @level2type=N‘COLUMN‘,@level2name=N‘access_token‘ GO EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘有效期(秒)‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘dt_weixin_access_token‘, @level2type=N‘COLUMN‘,@level2name=N‘expires_in‘ GO EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘总数‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘dt_weixin_access_token‘, @level2type=N‘COLUMN‘,@level2name=N‘count‘ GO EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘创建时间‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘dt_weixin_access_token‘, @level2type=N‘COLUMN‘,@level2name=N‘add_time‘ GO EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘公众平台access_token存储‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘dt_weixin_access_token‘ GO EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘自增ID‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘dt_weixin_account‘, @level2type=N‘COLUMN‘,@level2name=N‘id‘ GO EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘公众号名称‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘dt_weixin_account‘, @level2type=N‘COLUMN‘,@level2name=N‘name‘ GO EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘公众号原始ID‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘dt_weixin_account‘, @level2type=N‘COLUMN‘,@level2name=N‘originalid‘ GO EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘微信号‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘dt_weixin_account‘, @level2type=N‘COLUMN‘,@level2name=N‘wxcode‘ GO EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘令牌必须与微信平台对应‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘dt_weixin_account‘, @level2type=N‘COLUMN‘,@level2name=N‘token‘ GO EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘appid(仅用于高级接口)‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘dt_weixin_account‘, @level2type=N‘COLUMN‘,@level2name=N‘appid‘ GO EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘appsecret(仅用于高级接口)‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘dt_weixin_account‘, @level2type=N‘COLUMN‘,@level2name=N‘appsecret‘ GO EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘是否支持网站内容推送‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘dt_weixin_account‘, @level2type=N‘COLUMN‘,@level2name=N‘is_push‘ GO EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘排序号‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘dt_weixin_account‘, @level2type=N‘COLUMN‘,@level2name=N‘sort_id‘ GO EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘添加时间‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘dt_weixin_account‘, @level2type=N‘COLUMN‘,@level2name=N‘add_time‘ GO EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘微信公众平台账户‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘dt_weixin_account‘ GO
本地写完代码 可以放到绑定过域名的 服务器 进行日志打印调试，看看各项数据是否正常拿到。
最后实现转发 效果如下：