PageRenderTime 51ms CodeModel.GetById 22ms RepoModel.GetById 0ms app.codeStats 0ms

/dotNETCMS/Foosun.Web/Update/FS3/Update.aspx.cs

#
C# | 988 lines | 654 code | 57 blank | 277 comment | 83 complexity | 106f88da6698235e075896d30d9b1e80 MD5 | raw file

Large files files are truncated, but you can click here to view the full file

  1. using System;
  2. using System.Data;
  3. using System.Data.SqlClient;
  4. using System.Data.OleDb;
  5. using System.Data.Common;
  6. using System.Configuration;
  7. using System.Text.RegularExpressions;
  8. using System.Collections;
  9. using System.Web;
  10. using System.Web.Security;
  11. using System.Web.UI;
  12. using System.Web.UI.WebControls;
  13. using System.Web.UI.WebControls.WebParts;
  14. using System.Web.UI.HtmlControls;
  15. namespace Foosun.Web.Update.FS3
  16. {
  17. public partial class Update : System.Web.UI.Page
  18. {
  19. public static string SourConnstr = string.Empty;
  20. public static string TagConnstr = Foosun.Config.DBConfig.CmsConString;
  21. public static readonly string Prefix = Foosun.Config.UIConfig.dataRe;
  22. public static int GisSQL = 1;
  23. protected void Page_Load(object sender, EventArgs e)
  24. {
  25. Page.Server.ScriptTimeout = 1000;
  26. string gSet = Request.QueryString["set"];
  27. if (gSet != null && gSet != string.Empty)
  28. {
  29. string gtype = Request.QueryString["type"];
  30. string GConnstr = Request.QueryString["connstr"];
  31. string MConnstr = Request.QueryString["mConnstr"];
  32. string isSQL = Request.QueryString["isSQL"];
  33. if (GConnstr != null && GConnstr != string.Empty)
  34. {
  35. SourConnstr = GConnstr;
  36. if (isSQL != "1")
  37. {
  38. GisSQL = 0;
  39. //if (gtype == "news" || gtype == "class" || gtype == "special" || gtype == "gen")
  40. //{
  41. SourConnstr = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=" + Server.MapPath(GConnstr) + ";Persist Security Info=True;";
  42. //}
  43. //else
  44. //{
  45. // SourConnstr = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=" + Server.MapPath(MConnstr) + ";Persist Security Info=True;";
  46. //}
  47. }
  48. else
  49. {
  50. GisSQL = 1;
  51. }
  52. StatConvertTodotNETCMS(gtype, isSQL);
  53. }
  54. else
  55. {
  56. Response.Write("请正确填写连接字符串或者ACCESS路径");
  57. Response.End();
  58. }
  59. }
  60. }
  61. public void StatConvertTodotNETCMS(string gtype, string isSQL)
  62. {
  63. IDataReader dr = null;
  64. int i = 0;
  65. int m = 0;
  66. string sql = string.Empty;
  67. switch (gtype)
  68. {
  69. case "news":
  70. sql = "select * from FS_News order by id asc";
  71. if (GisSQL != 1)
  72. {
  73. OleDbConnection con = new OleDbConnection(SourConnstr);
  74. con.Open();
  75. OleDbCommand cmd = new OleDbCommand(sql, con);
  76. dr = cmd.ExecuteReader();
  77. }
  78. else
  79. {
  80. dr = SqlHelper.ExecuteReader(SourConnstr, CommandType.Text, sql, null);
  81. }
  82. while (dr.Read())
  83. {
  84. #region 转换开头
  85. string NewsID = dr["NewsID"].ToString();
  86. try
  87. {
  88. if (NewsID.Length > 5)
  89. {
  90. NewsID = NewsID.Substring(5);
  91. }
  92. int NewsType = 0;//IsURL
  93. if (dr["HeadNewsTF"].ToString() == "1")
  94. {
  95. NewsType = 2;
  96. }
  97. if (dr["PicNewsTF"].ToString() == "1")
  98. {
  99. NewsType = 1;
  100. }
  101. string ClassID = dr["ClassID"].ToString();
  102. ClassID = ClassID + "000000000000000";
  103. ClassID = ClassID.Substring(5, 12);
  104. string NewsProperty = "0,0,0,0,0,0,0,0";
  105. DateTime CreatTime = DateTime.Now;
  106. if (Foosun.Common.Input.IsDate(dr["AddDate"].ToString()))
  107. {
  108. CreatTime = DateTime.Parse(dr["AddDate"].ToString());
  109. }
  110. int ContentPicTF = 0;
  111. string CheckStat = "0|0|0|0";
  112. int isLock = 0;
  113. if (dr["AuditTF"].ToString() == "0")
  114. {
  115. CheckStat = "1|1|0|0";
  116. isLock = 1;
  117. }
  118. int isRecyle = 0;
  119. if (dr["DelTF"].ToString() == "1")
  120. {
  121. isRecyle = 1;
  122. }
  123. string TSTYLE = dr["TitleStyle"].ToString();
  124. string TSTYLE1 = "";
  125. string TSTYLE2 = "0";
  126. string TSTYLE3 = "0";
  127. if (TSTYLE != null && TSTYLE != string.Empty && TSTYLE.Length == 9)
  128. {
  129. TSTYLE1 = TSTYLE.Substring(1, 6);
  130. TSTYLE2 = TSTYLE.Substring(7, 1);
  131. TSTYLE3 = TSTYLE.Substring(8, 1);
  132. if (TSTYLE1 == "UUUUUU")
  133. {
  134. TSTYLE1 = "";
  135. }
  136. }
  137. SqlParameter param = new SqlParameter("@Content", dr["Content"].ToString());
  138. //推荐,滚动,热点,幻灯,头条,公告,WAP,精彩
  139. string insertsql = "insert into " + Prefix + "News(NewsID,NewsType,OrderID,NewsTitle,sNewsTitle,TitleColor,TitleITF,TitleBTF,CommLinkTF,SubNewsTF,URLaddress,PicURL,SPicURL,ClassID";
  140. insertsql += ",SpecialID,Author,Souce,Tags,NewsProperty,NewsPicTopline,Templet,Content,Metakeywords,Metadesc,naviContent,Click,CreatTime,EditTime,SavePath,FileName,FileEXName";
  141. insertsql += ",isDelPoint,Gpoint,iPoint,GroupNumber,ContentPicTF,ContentPicURL,ContentPicSize,CommTF,DiscussTF,TopNum,VoteTF,CheckStat,isLock,isRecyle,SiteID,DataLib,DefineID,isVoteTF";
  142. insertsql += ",Editor,isHtml,isConstr,isFiles,vURL";
  143. insertsql += ") values (";
  144. insertsql += "'" + NewsID.Trim() + "'," + NewsType + ",0,'" + dr["Title"].ToString() + "','" + dr["SubTitle"].ToString() + "','" + TSTYLE1 + "'," + Convert.ToInt16(TSTYLE2) + "," + Convert.ToInt16(TSTYLE3) + "," + Convert.ToInt16(dr["ShowReviewTF"].ToString()) + ",0,'" + dr["HeadNewsPath"].ToString() + "','" + dr["PicPath"].ToString() + "','','" + ClassID + "'";
  145. insertsql += ",'0','" + dr["Author"].ToString() + "','" + dr["TxtSource"].ToString() + "','" + (dr["KeyWords"].ToString()).Replace(",", "|") + "','" + NewsProperty + "',0,'" + dr["NewsTemplet"].ToString() + "',@Content,'','','" + dr["NaviWords"].ToString() + "'," + Convert.ToInt32(dr["ClickNum"].ToString()) + ",'" + CreatTime + "','" + CreatTime + "','" + dr["Path"].ToString() + "','" + dr["FileName"].ToString() + "','." + dr["FileExtName"].ToString() + "'";
  146. insertsql += "," + Convert.ToInt32(dr["BrowPop"].ToString()) + ",0,0,''," + ContentPicTF + ",'','0|0',1,0,0,0,'" + CheckStat + "'," + isLock + "," + isRecyle + ",'0','" + Prefix + "news',0,0";
  147. insertsql += ",'" + dr["Editer"].ToString() + "',0,0,0,''";
  148. insertsql += ")";
  149. SqlHelper.ExecuteNonQuery(TagConnstr, CommandType.Text, insertsql, param);
  150. i++;
  151. }
  152. catch(Exception ex)
  153. {
  154. m++;
  155. Foosun.Common.Public.saveConvertLogFiles("新闻ID:"+NewsID, ex.ToString());
  156. }
  157. #endregion
  158. }
  159. Response.Write("√ 成功转换新闻" + i + "条,转换失败" + m + "条.");
  160. Response.End();
  161. dr.Close();
  162. //con.Close();
  163. break;
  164. case "class":
  165. sql = "select * from FS_NewsClass order by id asc";
  166. if (GisSQL != 1)
  167. {
  168. OleDbConnection con = new OleDbConnection(SourConnstr);
  169. con.Open();
  170. OleDbCommand cmd = new OleDbCommand(sql, con);
  171. dr = cmd.ExecuteReader();
  172. }
  173. else
  174. {
  175. dr = SqlHelper.ExecuteReader(SourConnstr, CommandType.Text, sql, null);
  176. }
  177. while (dr.Read())
  178. {
  179. string ClassID = dr["ClassID"].ToString();
  180. try
  181. {
  182. ClassID = ClassID + "000000000000000";
  183. ClassID = ClassID.Substring(5, 12);
  184. //string GetExsit1 = "select count(*) from " + Prefix + "news_class where ClassID='" + ClassID + "'";
  185. //int CCTF1 = Convert.ToInt32(SqlHelper.ExecuteNonQuery(TagConnstr, CommandType.Text, GetExsit1, null));
  186. //if (CCTF1 > 0)
  187. //{
  188. // ClassID = Foosun.Common.Rand.Number(12);
  189. //}
  190. string ParentID = dr["ParentID"].ToString();
  191. if (ParentID != "0")
  192. {
  193. ParentID = ParentID + "000000000000000";
  194. ParentID = ParentID.Substring(5, 12);
  195. }
  196. int Checkint = 0;
  197. DateTime Addtime = DateTime.Now;
  198. if (Foosun.Common.Input.IsDate(dr["AddTime"].ToString()))
  199. {
  200. Addtime = DateTime.Parse(dr["AddTime"].ToString());
  201. }
  202. string insertsql = "insert into " + Prefix + "news_Class(ClassID,ClassCName,ClassEName,ParentID,IsURL,OrderID,URLaddress,[Domain],ClassTemplet,ReadNewsTemplet,SavePath,SaveClassframe,Checkint,ClassSaveRule";
  203. insertsql += ",ClassIndexRule,NewsSavePath,NewsFileRule,PicDirPath,ContentPicTF,ContentPICurl,ContentPicSize,InHitoryDay,DataLib,SiteID,NaviShowtf,NaviPIC,NaviContent,MetaKeywords,MetaDescript,isDelPoint,Gpoint";
  204. insertsql += ",iPoint,GroupNumber,FileName,isLock,isRecyle,NaviPosition,NewsPosition,isComm,Defineworkey,CreatTime,isPage,PageContent,ModelID,isunHTML";
  205. insertsql += ") values (";
  206. insertsql += "'" + ClassID + "','" + dr["ClassCName"].ToString() + "','" + dr["ClassEName"].ToString() + "','" + ParentID + "'," + Convert.ToInt32(dr["IsOutClass"].ToString()) + ",0,'" + dr["ClassLink"].ToString() + "','" + dr["DoMain"].ToString() + "','" + dr["ClassTemp"].ToString() + "','" + dr["NewsTemp"].ToString() + "','" + dr["SaveFilePath"].ToString() + "','',0,'" + dr["ClassEName"].ToString() + "/index.html'";
  207. insertsql += ",'{@year04}-{@month}/{@day}','{@year04}{@month}','{@year04}-{@month}-{@day}-{@hour}-{@minute}','',0,'','0|0',0,'" + Prefix + "news','0'," + Convert.ToInt32(dr["ShowTF"].ToString()) + ",'','','',''," + Convert.ToInt32(dr["BrowPop"].ToString()) + ",0";
  208. insertsql += ",0,'','." + dr["FileExtName"].ToString() + "',0," + Convert.ToInt32(dr["DelFlag"].ToString()) + ",'','',1,'','" + Addtime + "',0,'','',0";
  209. insertsql += ")";
  210. SqlHelper.ExecuteNonQuery(TagConnstr, CommandType.Text, insertsql, null);
  211. i++;
  212. }
  213. catch (Exception ex)
  214. {
  215. m++;
  216. Foosun.Common.Public.saveConvertLogFiles("栏目ID:" + ClassID, ex.ToString());
  217. }
  218. }
  219. dr.Close();
  220. Response.Write("√ 成功转换新闻栏目" + i + "条,转换失败" + m + "条.如果存在此栏目,将转换失败");
  221. Response.End();
  222. break;
  223. case "special":
  224. sql = "select * from FS_Special order by ID asc";
  225. if (GisSQL != 1)
  226. {
  227. OleDbConnection con = new OleDbConnection(SourConnstr);
  228. con.Open();
  229. OleDbCommand cmd = new OleDbCommand(sql, con);
  230. dr = cmd.ExecuteReader();
  231. }
  232. else
  233. {
  234. dr = SqlHelper.ExecuteReader(SourConnstr, CommandType.Text, sql, null);
  235. }
  236. while (dr.Read())
  237. {
  238. string SpecialID = dr["SpecialID"].ToString();
  239. try
  240. {
  241. SpecialID = SpecialID.Substring(5);
  242. string ParentID ="0";
  243. int Checkint = 0;
  244. DateTime Addtime = DateTime.Now;
  245. if (Foosun.Common.Input.IsDate(dr["AddTime"].ToString()))
  246. {
  247. Addtime = DateTime.Parse(dr["AddTime"].ToString());
  248. }
  249. string insertsql = "insert into " + Prefix + "news_special(SpecialID,SpecialCName,specialEName,ParentID,[Domain],isDelPoint,Gpoint,[iPoint],GroupNumber,saveDirPath,SavePath,FileName,FileEXName,NaviPicURL";
  250. insertsql += ",NaviContent,SiteID,Templet,isLock,isRecyle,CreatTime,NaviPosition,ModelID";
  251. insertsql += ") values (";
  252. insertsql += "'" + SpecialID + "','" + dr["CName"].ToString() + "','" + dr["EName"].ToString() + "','" + ParentID + "','',0,0,0,'','','" + dr["SaveFilePath"].ToString() + "','" + dr["EName"].ToString() + "','." + dr["FileExtName"].ToString() + "',''";
  253. insertsql += ",'" + dr["IndexNaviWord"].ToString() + "','0','" + dr["Templet"].ToString() + "',0,0,'" + Addtime + "','','0'";
  254. insertsql += ")";
  255. SqlHelper.ExecuteNonQuery(TagConnstr, CommandType.Text, insertsql, null);
  256. i++;
  257. }
  258. catch (Exception ex)
  259. {
  260. m++;
  261. Foosun.Common.Public.saveConvertLogFiles("专题ID:" + SpecialID, ex.ToString());
  262. }
  263. }
  264. dr.Close();
  265. Response.Write("√ 成功转换新闻专题" + i + "条,转换失败" + m + "条");
  266. Response.End();
  267. break;
  268. case "user":
  269. sql = "select * from FS_Members order by id asc";
  270. if (GisSQL != 1)
  271. {
  272. OleDbConnection con = new OleDbConnection(SourConnstr);
  273. con.Open();
  274. OleDbCommand cmd = new OleDbCommand(sql, con);
  275. dr = cmd.ExecuteReader();
  276. }
  277. else
  278. {
  279. dr = SqlHelper.ExecuteReader(SourConnstr, CommandType.Text, sql, null);
  280. }
  281. while (dr.Read())
  282. {
  283. string UserNumber = Foosun.Common.Rand.Number(12, true);
  284. try
  285. {
  286. int Checkint = 0;
  287. DateTime BothYear = DateTime.Now;
  288. if (Foosun.Common.Input.IsDate(dr["Birthday"].ToString()))
  289. {
  290. BothYear = DateTime.Parse(dr["Birthday"].ToString());
  291. }
  292. DateTime RegTime = DateTime.Now;
  293. if (Foosun.Common.Input.IsDate(dr["RegTime"].ToString()))
  294. {
  295. RegTime = DateTime.Parse(dr["RegTime"].ToString());
  296. }
  297. DateTime LastLoginTime = DateTime.Now;
  298. if (Foosun.Common.Input.IsDate(dr["LastLoginTime"].ToString()))
  299. {
  300. LastLoginTime = DateTime.Parse(dr["LastLoginTime"].ToString());
  301. }
  302. string insertsql = "insert into " + Prefix + "sys_User(UserNum,UserName,UserPassword,NickName,RealName,isAdmin,UserGroupNumber,PassQuestion,PassKey,CertType,CertNumber,Email,mobile,Sex";
  303. insertsql += ",birthday,Userinfo,UserFace,userFacesize,marriage,iPoint,gPoint,cPoint,ePoint,aPoint,isLock,RegTime,LastLoginTime,OnlineTime,OnlineTF,LoginNumber,FriendClass";
  304. insertsql += ",LoginLimtNumber,LastIP,SiteID,Addfriend,isOpen,ParmConstrNum,isIDcard,IDcardFiles,Addfriendbs,EmailATF,EmailCode,isMobile,BindTF,MobileCode";
  305. insertsql += ") values (";
  306. insertsql += "'" + UserNumber + "','" + dr["MemName"].ToString() + "','" + dr["Password"].ToString() + "','" + dr["MemName"].ToString() + "','" + dr["Name"].ToString() + "',0,'00000000001','" + dr["PassQuestion"].ToString() + "','" + dr["PassAnswer"].ToString() + "',0,'','" + dr["Email"].ToString() + "',''," + Convert.ToInt32(dr["Sex"].ToString()) + "";
  307. insertsql += ",'" + BothYear + "','','" + dr["HeadPic"].ToString() + "','50|50',0," + Convert.ToInt32(dr["Point"].ToString()) + ",0,0,0,0," + Convert.ToInt32(dr["Lock"].ToString()) + ",'" + RegTime + "','" + LastLoginTime + "',0,0," + Convert.ToInt32(dr["LoginNum"].ToString()) + ",''";
  308. insertsql += ",0,'" + dr["LastLoginIP"].ToString() + "','0',0," + Convert.ToInt32(dr["OpenInfTF"].ToString()) + ",0,0,'','',0,'',0,0,''";
  309. insertsql += ")";
  310. SqlHelper.ExecuteNonQuery(TagConnstr, CommandType.Text, insertsql, null);
  311. i++;
  312. }
  313. catch (Exception ex)
  314. {
  315. m++;
  316. Foosun.Common.Public.saveConvertLogFiles("用户ID:" + UserNumber, ex.ToString());
  317. }
  318. }
  319. dr.Close();
  320. Response.Write("√ 成功转换会员" + i + "条,转换失败" + m + "条");
  321. Response.End();
  322. break;
  323. case "gen":
  324. //(1关键字 2来源 3作者 4编辑 5内部链接)
  325. sql = "select * from FS_Routine order by id asc";
  326. if (GisSQL != 1)
  327. {
  328. OleDbConnection con = new OleDbConnection(SourConnstr);
  329. con.Open();
  330. OleDbCommand cmd = new OleDbCommand(sql, con);
  331. dr = cmd.ExecuteReader();
  332. }
  333. else
  334. {
  335. dr = SqlHelper.ExecuteReader(SourConnstr, CommandType.Text, sql, null);
  336. }
  337. while (dr.Read())
  338. {
  339. string ID = dr["ID"].ToString();
  340. try
  341. {
  342. //0表示关键字,1表示作者,2表示来源,3表示内部连接
  343. int G_Type = 0;
  344. switch (dr["Type"].ToString())
  345. {
  346. case "1":
  347. G_Type = 0;
  348. break;
  349. case "2":
  350. G_Type = 2;
  351. break;
  352. case "3":
  353. G_Type = 1;
  354. break;
  355. case "4":
  356. G_Type = 1;
  357. break;
  358. case "5":
  359. G_Type = 3;
  360. break;
  361. }
  362. string insertsql = "insert into " + Prefix + "news_Gen(Cname,gType,URL,EmailURL,isLock,SiteID";
  363. insertsql += ") values (";
  364. insertsql += "'" + dr["Name"].ToString() + "'," + G_Type + ",'" + dr["Url"].ToString() + "','',0,'0'";
  365. insertsql += ")";
  366. i++;
  367. SqlHelper.ExecuteNonQuery(TagConnstr, CommandType.Text, insertsql, null);
  368. }
  369. catch (Exception ex)
  370. {
  371. m++;
  372. Foosun.Common.Public.saveConvertLogFiles("常规ID:" + ID, ex.ToString());
  373. }
  374. }
  375. dr.Close();
  376. Response.Write("√ 成功转换常规管理" + i + "条,转换失败" + m + "条");
  377. Response.End();
  378. break;
  379. }
  380. }
  381. public abstract class SqlHelper
  382. {
  383. //数据库表通用前缀
  384. // Hashtable to store cached parameters
  385. private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
  386. /// <summary>
  387. /// Execute a SqlCommand (that returns no resultset) against the database specified in the connection string
  388. /// using the provided parameters.
  389. /// </summary>
  390. /// <remarks>
  391. /// e.g.:
  392. /// int result = ExecuteNonQuery( CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
  393. /// </remarks>
  394. /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
  395. /// <param name="commandText">the stored procedure name or T-SQL command</param>
  396. /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
  397. /// <returns>an int representing the number of rows affected by the command</returns>
  398. public static int ExecuteNonQuery(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
  399. {
  400. return ExecuteNonQuery(SourConnstr, cmdType, cmdText, commandParameters);
  401. }
  402. /// <summary>
  403. /// Execute a SqlCommand (that returns no resultset) against the database specified in the connection string
  404. /// using the provided parameters.
  405. /// </summary>
  406. /// <remarks>
  407. /// e.g.:
  408. /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
  409. /// </remarks>
  410. /// <param name="connectionString">a valid connection string for a SqlConnection</param>
  411. /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
  412. /// <param name="commandText">the stored procedure name or T-SQL command</param>
  413. /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
  414. /// <returns>an int representing the number of rows affected by the command</returns>
  415. public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
  416. {
  417. SqlCommand cmd = new SqlCommand();
  418. using (SqlConnection conn = new SqlConnection(connectionString))
  419. {
  420. PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
  421. int val = cmd.ExecuteNonQuery();
  422. cmd.Parameters.Clear();
  423. return val;
  424. }
  425. }
  426. /// <summary>
  427. /// Execute a SqlCommand (that returns no resultset) against an existing database connection
  428. /// using the provided parameters.
  429. /// </summary>
  430. /// <remarks>
  431. /// e.g.:
  432. /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
  433. /// </remarks>
  434. /// <param name="conn">an existing database connection</param>
  435. /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
  436. /// <param name="commandText">the stored procedure name or T-SQL command</param>
  437. /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
  438. /// <returns>an int representing the number of rows affected by the command</returns>
  439. public static int ExecuteNonQuery(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
  440. {
  441. SqlCommand cmd = new SqlCommand();
  442. PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
  443. int val = cmd.ExecuteNonQuery();
  444. cmd.Parameters.Clear();
  445. return val;
  446. }
  447. /// <summary>
  448. /// Execute a SqlCommand (that returns no resultset) using an existing SQL Transaction
  449. /// using the provided parameters.
  450. /// </summary>
  451. /// <remarks>
  452. /// e.g.:
  453. /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
  454. /// </remarks>
  455. /// <param name="trans">an existing sql transaction</param>
  456. /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
  457. /// <param name="commandText">the stored procedure name or T-SQL command</param>
  458. /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
  459. /// <returns>an int representing the number of rows affected by the command</returns>
  460. public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
  461. {
  462. SqlCommand cmd = new SqlCommand();
  463. PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
  464. int val = cmd.ExecuteNonQuery();
  465. cmd.Parameters.Clear();
  466. return val;
  467. }
  468. /// <summary>
  469. /// Execute a SqlCommand that returns a resultset against the database specified in the connection string
  470. /// using the provided parameters.
  471. /// </summary>
  472. /// <remarks>
  473. /// e.g.:
  474. /// SqlDataReader r = ExecuteReader(CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
  475. /// </remarks>
  476. /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
  477. /// <param name="commandText">the stored procedure name or T-SQL command</param>
  478. /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
  479. /// <returns>A SqlDataReader containing the results</returns>
  480. public static SqlDataReader ExecuteReader(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
  481. {
  482. return ExecuteReader(SourConnstr, cmdType, cmdText, commandParameters);
  483. }
  484. /// <summary>
  485. /// Execute a SqlCommand that returns a resultset against the database specified in the connection string
  486. /// using the provided parameters.
  487. /// </summary>
  488. /// <remarks>
  489. /// e.g.:
  490. /// SqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
  491. /// </remarks>
  492. /// <param name="connectionString">a valid connection string for a SqlConnection</param>
  493. /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
  494. /// <param name="commandText">the stored procedure name or T-SQL command</param>
  495. /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
  496. /// <returns>A SqlDataReader containing the results</returns>
  497. public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
  498. {
  499. SqlCommand cmd = new SqlCommand();
  500. SqlConnection conn = new SqlConnection(connectionString);
  501. // we use a try/catch here because if the method throws an exception we want to
  502. // close the connection throw code, because no datareader will exist, hence the
  503. // commandBehaviour.CloseConnection will not work
  504. try
  505. {
  506. PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
  507. SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
  508. cmd.Parameters.Clear();
  509. return rdr;
  510. }
  511. catch
  512. {
  513. conn.Close();
  514. throw;
  515. }
  516. }
  517. /// <summary>
  518. /// 执行对默认数据库有自定义排序的分页的查询
  519. /// </summary>
  520. /// <param name="connectionString">连接字符串
  521. /// <param name="SqlAllFields">查询字段,如果是多表查询,请将必要的表名或别名加上,如:a.id,a.name,b.score</param>
  522. /// <param name="SqlTablesAndWhere">查询的表如果包含查询条件,也将条件带上,但不要包含order by子句,也不要包含"from"关键字,如:students a inner join achievement b on a.... where ....</param>
  523. /// <param name="IndexField">用以分页的不能重复的索引字段名,最好是主表的自增长字段,如果是多表查询,请带上表名或别名,如:a.id</param>
  524. /// <param name="OrderASC">排序方式,如果为true则按升序排序,false则按降序排</param>
  525. /// <param name="OrderFields">排序字段以及方式如:a.OrderID desc,CnName desc</OrderFields>
  526. /// <param name="PageIndex">当前页的页码</param>
  527. /// <param name="PageSize">每页记录数</param>
  528. /// <param name="RecordCount">输出参数,返回查询的总记录条数</param>
  529. /// <param name="PageCount">输出参数,返回查询的总页数</param>
  530. /// <returns>返回查询结果</returns>
  531. public static SqlDataReader ExecuteReaderPage(string connectionString, string SqlAllFields, string SqlTablesAndWhere, string IndexField, string GroupClause, string OrderFields, int PageIndex, int PageSize, out int RecordCount, out int PageCount, params SqlParameter[] commandParameters)
  532. {
  533. SqlCommand cmd = new SqlCommand();
  534. SqlConnection conn = new SqlConnection(connectionString);
  535. try
  536. {
  537. conn.Open();
  538. RecordCount = 0;
  539. PageCount = 0;
  540. if (PageSize <= 0)
  541. {
  542. PageSize = 10;
  543. }
  544. string SqlCount = "select count(*) from " + SqlTablesAndWhere;
  545. cmd.Connection = conn;
  546. cmd.CommandType = CommandType.Text;
  547. cmd.CommandText = SqlCount;
  548. if (commandParameters != null)
  549. {
  550. foreach (SqlParameter parm in commandParameters)
  551. cmd.Parameters.Add(parm);
  552. }
  553. RecordCount = (int)cmd.ExecuteScalar();
  554. if (RecordCount % PageSize == 0)
  555. {
  556. PageCount = RecordCount / PageSize;
  557. }
  558. else
  559. {
  560. PageCount = RecordCount / PageSize + 1;
  561. }
  562. if (PageIndex > PageCount)
  563. PageIndex = PageCount;
  564. if (PageIndex < 1)
  565. PageIndex = 1;
  566. string Sql = null;
  567. if (PageIndex == 1)
  568. {
  569. Sql = "select top " + PageSize + " " + SqlAllFields + " from " + SqlTablesAndWhere + " " + GroupClause + " " + OrderFields;
  570. }
  571. else
  572. {
  573. Sql = "select top " + PageSize + " " + SqlAllFields + " from ";
  574. if (SqlTablesAndWhere.ToLower().IndexOf(" where ") > 0)
  575. {
  576. string _where = Regex.Replace(SqlTablesAndWhere, @"\ where\ ", " where (", RegexOptions.IgnoreCase | RegexOptions.Compiled);
  577. Sql += _where + ") and (";
  578. }
  579. else
  580. {
  581. Sql += SqlTablesAndWhere + " where (";
  582. }
  583. Sql += IndexField + " not in (select top " + (PageIndex - 1) * PageSize + " " + IndexField + " from " + SqlTablesAndWhere + " " + OrderFields;
  584. Sql += ")) " + GroupClause + " " + OrderFields;
  585. }
  586. cmd.CommandText = Sql;
  587. SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
  588. cmd.Parameters.Clear();
  589. return rdr;
  590. }
  591. catch
  592. {
  593. conn.Close();
  594. throw;
  595. }
  596. }
  597. /// <summary>
  598. /// Execute a SqlCommand that returns a resultset against the database specified in the connection string
  599. /// using the provided parameters.
  600. /// </summary>
  601. /// <remarks>
  602. /// e.g.:
  603. /// SqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
  604. /// </remarks>
  605. /// <param name="connectionString">a valid connection string for a SqlConnection</param>
  606. /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
  607. /// <param name="commandText">the stored procedure name or T-SQL command</param>
  608. /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
  609. /// <returns>A SqlDataReader containing the results</returns>
  610. public static SqlDataReader ExecuteReader(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
  611. {
  612. SqlCommand cmd = new SqlCommand();
  613. // we use a try/catch here because if the method throws an exception we want to
  614. // close the connection throw code, because no datareader will exist, hence the
  615. // commandBehaviour.CloseConnection will not work
  616. PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
  617. SqlDataReader rdr = cmd.ExecuteReader();
  618. cmd.Parameters.Clear();
  619. return rdr;
  620. }
  621. /// <summary>
  622. /// Execute a SqlCommand that returns the first column of the first record against the database specified in the connection string
  623. /// using the provided parameters.
  624. /// </summary>
  625. /// <remarks>
  626. /// e.g.:
  627. /// Object obj = ExecuteScalar(CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
  628. /// </remarks>
  629. /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
  630. /// <param name="commandText">the stored procedure name or T-SQL command</param>
  631. /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
  632. /// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
  633. public static object ExecuteScalar(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
  634. {
  635. return ExecuteScalar(SourConnstr, cmdType, cmdText, commandParameters);
  636. }
  637. /// <summary>
  638. /// Execute a SqlCommand that returns the first column of the first record against the database specified in the connection string
  639. /// using the provided parameters.
  640. /// </summary>
  641. /// <remarks>
  642. /// e.g.:
  643. /// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
  644. /// </remarks>
  645. /// <param name="connectionString">a valid connection string for a SqlConnection</param>
  646. /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
  647. /// <param name="commandText">the stored procedure name or T-SQL command</param>
  648. /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
  649. /// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
  650. public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
  651. {
  652. SqlCommand cmd = new SqlCommand();
  653. using (SqlConnection connection = new SqlConnection(connectionString))
  654. {
  655. PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
  656. object val = cmd.ExecuteScalar();
  657. cmd.Parameters.Clear();
  658. return val;
  659. }
  660. }
  661. /// <summary>
  662. /// Execute a SqlCommand that returns the first column of the first record against an existing database connection
  663. /// using the provided parameters.
  664. /// </summary>
  665. /// <remarks>
  666. /// e.g.:
  667. /// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
  668. /// </remarks>
  669. /// <param name="conn">an existing database connection</param>
  670. /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
  671. /// <param name="commandText">the stored procedure name or T-SQL command</param>
  672. /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
  673. /// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
  674. public static object ExecuteScalar(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
  675. {
  676. SqlCommand cmd = new SqlCommand();
  677. PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
  678. object val = cmd.ExecuteScalar();
  679. cmd.Parameters.Clear();
  680. return val;
  681. }
  682. /// <summary>
  683. /// Execute a SqlCommand that returns the first column of the first record against an existing database connection
  684. /// using the provided parameters.
  685. /// </summary>
  686. /// <remarks>
  687. /// e.g.:
  688. /// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
  689. /// </remarks>
  690. /// <param name="trans">an existing sql transaction</param>
  691. /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
  692. /// <param name="commandText">the stored procedure name or T-SQL command</param>
  693. /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
  694. /// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
  695. public static object ExecuteScalar(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
  696. {
  697. SqlCommand cmd = new SqlCommand();
  698. PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
  699. object val = cmd.ExecuteScalar();
  700. cmd.Parameters.Clear();
  701. return val;
  702. }
  703. /// <summary>
  704. /// Execute a SqlCommand that returns the DataTable object against the database specified in the connection string
  705. /// using the provided parameters.
  706. /// </summary>
  707. /// <remarks>
  708. /// e.g.:
  709. /// DataTable tb = ExecuteTable( CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
  710. /// </remarks>
  711. /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
  712. /// <param name="commandText">the stored procedure name or T-SQL command</param>
  713. /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
  714. /// <returns>A DataTable containing the results</returns>
  715. public static DataTable ExecuteTable(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
  716. {
  717. return ExecuteTable(SourConnstr, cmdType, cmdText, commandParameters);
  718. }
  719. /// <summary>
  720. /// Execute a SqlCommand that returns the DataTable object against the database specified in the connection string
  721. /// using the provided parameters.
  722. /// </summary>
  723. /// <remarks>
  724. /// e.g.:
  725. /// DataTable tb = ExecuteTable(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
  726. /// </remarks>
  727. /// <param name="connectionString">a valid connection string for a SqlConnection</param>
  728. /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
  729. /// <param name="commandText">the stored procedure name or T-SQL command</param>
  730. /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
  731. /// <returns>A DataTable containing the results</returns>
  732. public static DataTable ExecuteTable(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
  733. {
  734. SqlCommand cmd = new SqlCommand();
  735. using (SqlConnection connection = new SqlConnection(connectionString))
  736. {
  737. PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
  738. SqlDataAdapter ap = new SqlDataAdapter();
  739. ap.SelectCommand = cmd;
  740. DataSet st = new DataSet();
  741. ap.Fill(st, "Result");
  742. cmd.Parameters.Clear();
  743. return st.Tables["Result"];
  744. }
  745. }
  746. /// <summary>
  747. /// Execute a SqlCommand that returns the DataTable object against an existing database connection
  748. /// using the provided parameters.
  749. /// </summary>
  750. /// <remarks>
  751. /// e.g.:
  752. /// DataTable tb = ExecuteTable(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
  753. /// </remarks>
  754. /// <param name="conn">an existing database connection</param>
  755. /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
  756. /// <param name="commandText">the stored procedure name or T-SQL command</param>
  757. /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
  758. /// <returns>A DataTable containing the results </returns>
  759. public static DataTable ExecuteTable(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
  760. {
  761. SqlCommand cmd = new SqlCommand();
  762. PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
  763. SqlDataAdapter ap = new SqlDataAdapter();
  764. ap.SelectCommand = cmd;
  765. DataSet st = new DataSet();
  766. ap.Fill(st, "Result");
  767. cmd.Parameters.Clear();
  768. return st.Tables["Result"];
  769. }
  770. /// <summary>
  771. /// 执行对默认数据库有自定义排序的分页的查询
  772. /// </summary>
  773. /// <param name="SqlAllFields">查询字段,如果是多表查询,请将必要的表名或别名加上,如:a.id,a.name,b.score</param>
  774. /// <param name="SqlTablesAndWhere">查询的表如果包含查询条件,也将条件带上,但不要包含order by子句,也不要包含"from"关键字,如:students a inner join achievement b on a.... where ....</param>
  775. /// <param name="IndexField">用以分页的不能重复的索引字段名,最好是主表的自增长字段,如果是多表查询,请带上表名或别名,如:a.id</param>
  776. /// <param name="OrderASC">排序方式,如果为true则按升序排序,false则按降序排</param>
  777. /// <param name="OrderFields">排序字段以及方式如:a.OrderID desc,CnName desc</OrderFields>
  778. /// <param name="PageIndex">当前页的页码</param>
  779. /// <param name="PageSize">每页记录数</param>
  780. /// <param name="RecordCount">输出参数,返回查询的总记录条数</param>
  781. /// <param name="PageCount">输出参数,返回查询的总页数</param>
  782. /// <returns>返回查询结果</returns>
  783. public static DataTable ExecutePage(string SqlAllFields, string SqlTablesAndWhere, string IndexField, string OrderFields, int PageIndex, int PageSize, out int RecordCount, out int PageCount, params SqlParameter[] commandParameters)
  784. {
  785. return ExecutePage(SourConnstr, SqlAllFields, SqlTablesAndWhere, IndexField, OrderFields, PageIndex, PageSize, out RecordCount, out PageCount, commandParameters);
  786. }
  787. /// <summary>
  788. /// 执行有自定义排序的分页的查询
  789. /// </summary>
  790. /// <param name="connectionString">SQL数据库连接字符串</param>
  791. /// <param name="SqlAllFields">查询字段,如果是多表查询,请将必要的表名或别名加上,如:a.id,a.name,b.score</param>
  792. /// <param name="SqlTablesAndWhere">查询的表如果包含查询条件,也将条件带上,但不要包含order by子句,也不要包含"from"关键字,如:students a inner join achievement b on a.... where ....</param>
  793. /// <param name="IndexField">用以分页的不能…

Large files files are truncated, but you can click here to view the full file