Jquery+Handler实现数据初始化
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="WebApplication1.UI.WebForm1" %> 2 3 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> 4 <html xmlns="http://www.w3.org/1999/xhtml"> 5 <script src="http://localhost:3397/Jscript/jquery-1.4.min.js" type="text/javascript"></script> 6 <head runat="server"> 7 <title></title> 8 <style type="text/css"> 9 #DivSqlText 10 { 11 height: 200px; 12 overflow: hidden; 13 overflow-y: scroll; 14 } 15 .successClass 16 { 17 src: "../Images/success.png"; 18 height: 20px; 19 width: 20px; 20 } 21 .failClass 22 { 23 src: "../Images/fail.jpg"; 24 height: 20px; 25 width: 20px; 26 } 27 </style> 28 <script type="text/javascript"> 29 var c = -1; 30 function startRequest(i) { 31 $.ajax({ 32 type: "post", 33 url: "../Handler/ExecuteSql.ashx", 34 async: false, 35 data: "sqlText=" + i, 36 success: function (msg) { 37 //alert(msg); 38 $("img").attr("src", "../Images/success.png"); 39 var divshow = $("#DivSqlText"); 40 divshow.append("<img height='10' width='10' src='../Images/right.jpg'/>" + msg); 41 divshow.scrollTop(divshow.attr("scrollHeight")); 42 } 43 }); 44 } 45 function ExecuteSql() { 46 c = c + 1; 47 if (c == 21) { 48 clearTimeout("ExecuteSql()", 500); 49 } 50 else { 51 startRequest(c); 52 setTimeout("ExecuteSql()", 500); 53 } 54 } 55 </script> 56 </head> 57 <body> 58 <form id="form1" runat="server" method="post"> 59 <div> 60 <table> 61 <tr> 62 <td> 63 <asp:Label Text="服务器地址:" runat="server" /> 64 </td> 65 <td> 66 <asp:TextBox ID="txtServerIP" runat="server" /> 67 </td> 68 </tr> 69 <tr> 70 <td> 71 <asp:Label ID="Label1" Text="数据库名称:" runat="server" /> 72 </td> 73 <td> 74 <asp:TextBox ID="txtDataBaseName" runat="server" /> 75 </td> 76 </tr> 77 <tr> 78 <td> 79 <asp:Label ID="Label2" Text="用户名:" runat="server" /> 80 </td> 81 <td> 82 <asp:TextBox ID="txtUserName" runat="server" /> 83 </td> 84 </tr> 85 <tr> 86 <td> 87 <asp:Label ID="Label3" Text="密码:" runat="server" /> 88 </td> 89 <td> 90 <asp:TextBox ID="txtPassword" runat="server" /> 91 </td> 92 </tr> 93 <tr> 94 <td colspan="2" align="center"> 95 96 <asp:Button ID="btnSend" Text="基础数据初始化" runat="server" OnClick="btnSend_Click" /> 97 98 <asp:Button ID="btnTestSend" Text="测试连接" runat="server" OnClick="btnTestSend_Click" /><%-- OnClientClick="ExecuteSql()" />--%> 99 </td> 100 </tr> 101 </table> 102 </div> 103 <table> 104 <tr> 105 <td> 106 <div id="DivSqlText" /> 107 </td> 108 </tr> 109 <tr> 110 <td> 111 <asp:TextBox ID="txtSqlText" TextMode="MultiLine" runat="server" Height="341px" Width="891px" 112 ForeColor="#FF66FF" /> 113 </td> 114 </tr> 115 </table> 116 </form> 117 </body> 118 </html>
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 protected void btnSend_Click(object sender, EventArgs e) 2 { 3 FileStream fs = new FileStream(Server.MapPath("~/Files/sql.txt"), FileMode.Open); 4 StreamReader read = new StreamReader(fs, Encoding.GetEncoding("gb2312")); 5 StringBuilder sb = new StringBuilder(); 6 7 while (read.Peek() >= 0) 8 { 9 //逐行读取数据 10 sb.Append(read.ReadLine()); 11 } 12 string[] str = sb.ToString().Replace("GO", "|").Split('|'); 13 sb.Clear(); 14 foreach (string s in str) 15 { 16 //TextBox tb = new TextBox(); 17 sb.Append(s + "|"); 18 //tb.Text = s + " | \n"; 19 //tb.ForeColor = System.Drawing.Color.DarkRed; 20 } 21 txtSqlText.Text = sb.ToString(); 22 } 23 24 protected void btnTestSend_Click(object sender, EventArgs e) 25 { 26 System.Data.SqlClient.SqlConnection conn = DBHelper.Connection; 27 Session.Add("conn", conn); 28 //for (int i = 0; i < 10; i++) 29 //{ 30 //string str; 31 //str = " <script language='javascript'>"; 32 //str += "ExecuteSql(" + i + ")"; 33 //str += " </script>"; 34 //Response.Write(str); 35 36 //btnSend.OnClientClick = "ExecuteSql("+i+")"; 37 38 Page.RegisterStartupScript("ggg", " <script>ExecuteSql(); </script>"); 39 40 41 //Response.Redirect("../Handler/ExecuteSql.ashx?sqlText=sdfsdf"); 42 //} 43 }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Text; 5 6 using System.Data.SqlClient; 7 using System.Data; 8 using System.Configuration; 9 10 namespace WebDLLHelper 11 { 12 public static class DBHelper 13 { 14 public static SqlConnection Connection 15 { 16 get 17 { 18 string connectionString = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString.ToString(); 19 SqlConnection connection = new SqlConnection(connectionString); 20 if (ConnectionState.Closed == connection.State) 21 { 22 connection.Open(); 23 Transaction = connection.BeginTransaction("SampleTransaction"); 24 IsOpen = true; 25 } 26 return connection; 27 } 28 } 29 30 private static SqlTransaction transaction; 31 public static SqlTransaction Transaction 32 { 33 get { return DBHelper.transaction; } 34 set { DBHelper.transaction = value; } 35 } 36 37 private static bool isOpen; 38 public static bool IsOpen 39 { 40 get { return DBHelper.isOpen; } 41 set { DBHelper.isOpen = value; } 42 } 43 44 public static int ExecuteCommand(string safeSql) 45 { 46 SqlCommand cmd = new SqlCommand(safeSql, Connection); 47 int result = cmd.ExecuteNonQuery(); 48 cmd.CommandTimeout = 20; 49 cmd.Connection.Close(); 50 return result; 51 } 52 53 public static DataTable GetTable(string safeSql) 54 { 55 DataSet ds = new DataSet(); 56 SqlCommand cmd = new SqlCommand(safeSql, Connection); 57 SqlDataAdapter da = new SqlDataAdapter(cmd); 58 da.Fill(ds); 59 cmd.Connection.Close(); 60 return ds.Tables[0]; 61 } 62 } 63 }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Web; 5 using System.Text; 6 using WebDLLHelper; 7 8 using System.Data.SqlClient; 9 using System.Data.Common; 10 using System.Web.SessionState; 11 12 namespace WebApplication1.Handler 13 { 14 /// <summary> 15 /// ExecuteSql 的摘要说明 16 /// </summary> 17 public class ExecuteSql : IHttpHandler, IRequiresSessionState 18 { 19 20 public void ProcessRequest(HttpContext context) 21 { 22 context.Response.ContentType = "text/plain"; 23 string sqlText = context.Request.Params["sqlText"]; 24 object ses = context.Session["conn"]; 25 SqlConnection conn = ses as SqlConnection; 26 try 27 { 28 StringBuilder sb = new StringBuilder(); 29 string[] strArray = sqlText.Split('|'); 30 SqlCommand command = conn.CreateCommand(); 31 // 指定transaction和connection对象 32 command.Connection = conn; 33 command.Transaction = DBHelper.Transaction; 34 35 command.CommandText = "insert into tabdemo values(newid(),null)"; 36 37 int count = command.ExecuteNonQuery(); 38 if (count > 0) 39 { 40 context.Response.Write("<font color=red> 第"+sqlText+" 条执行成功.</font><br>"); 41 } 42 else 43 { 44 context.Response.Write("<font color=red> 第" + sqlText + " 条执行失败.</font><br>"); 45 throw new Exception("插入失败"); 46 } 47 if (sqlText == "10") 48 DBHelper.Transaction.Commit(); 49 } 50 catch (Exception ex) 51 { 52 DBHelper.Transaction.Rollback(); 53 throw ex; 54 } 55 } 56 57 public bool IsReusable 58 { 59 get 60 { 61 return false; 62 } 63 } 64 } 65 }
(本来录制了视频,但是搞了半天没弄到上面来,所以截了张图,分享下)效果图如下: