需求: 利用MySql数据库结合前端技术完成用户的注册(要求不使用Web服务技术),所以 Demo采用Socket技术实现Web通信.
第一部分:数据库创建
数据库采用mysql 5.7.18, 数据库名称为MyUser, 内部有一张表 user.字段有 Id,UserName,Psd,Tel
第二部分:数据库连接与Socket通信
创建控制台程序(服务端程序),添加以下类
1 MySqlHelper
建立MySqlHelper 类,用于实现数据库操作
1public class MysqlHelper 2 { 3 //数据库连接字符串 4 public static string Conn = "Database='Myuser';Data Source='localhost';User Id='root';Password='';charset='utf8'"; //XXX的为修改项 5 6 public static void SetConn(string UserName = "root", string Password="", string IP= "localhost", string Database="Myuser") 7 { 8 Conn = "datasource=" + IP + ";username=" + UserName + ";password=" + Password + ";database=" + Database + ";charset=utf8"; 9 10 } 11 /// <summary> 12 /// 给定连接的数据库用假设参数执行一个sql命令（不返回数据集） 13 /// </summary> 14 /// <param name="connectionString">一个有效的连接字符串</param> 15 /// <param name="cmdType">命令类型(存储过程, 文本, 等等)</param> 16 /// <param name="cmdText">存储过程名称或者sql命令语句</param> 17 /// <param name="commandParameters">执行命令所用参数的集合</param> 18 /// <returns>执行命令所影响的行数</returns> 19 public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters) 20 { 21 22 MySqlCommand cmd = new MySqlCommand(); 23 24 using (MySqlConnection conn = new MySqlConnection(connectionString)) 25 { 26 PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); 27 int val = cmd.ExecuteNonQuery(); 28 cmd.Parameters.Clear(); 29 return val; 30 } 31 } 32 33 /// <summary> 34 /// 用现有的数据库连接执行一个sql命令（不返回数据集） 35 /// </summary> 36 /// <param name="connection">一个现有的数据库连接</param> 37 /// <param name="cmdType">命令类型(存储过程, 文本, 等等)</param> 38 /// <param name="cmdText">存储过程名称或者sql命令语句</param> 39 /// <param name="commandParameters">执行命令所用参数的集合</param> 40 /// <returns>执行命令所影响的行数</returns> 41 public static int ExecuteNonQuery(MySqlConnection connection, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters) 42 { 43 44 MySqlCommand cmd = new MySqlCommand(); 45 46 PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters); 47 int val = cmd.ExecuteNonQuery(); 48 //cmd.Parameters.Clear(); 49 return val; 50 } 51 52 /// <summary> 53 ///使用现有的SQL事务执行一个sql命令（不返回数据集） 54 /// </summary> 55 /// <remarks> 56 ///举例: 57 /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24)); 58 /// </remarks> 59 /// <param name="trans">一个现有的事务</param> 60 /// <param name="cmdType">命令类型(存储过程, 文本, 等等)</param> 61 /// <param name="cmdText">存储过程名称或者sql命令语句</param> 62 /// <param name="commandParameters">执行命令所用参数的集合</param> 63 /// <returns>执行命令所影响的行数</returns> 64 public static int ExecuteNonQuery(MySqlTransaction trans, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters) 65 { 66 MySqlCommand cmd = new MySqlCommand(); 67 PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters); 68 int val = cmd.ExecuteNonQuery(); 69 cmd.Parameters.Clear(); 70 return val; 71 } 72 73 /// <summary> 74 /// 用执行的数据库连接执行一个返回数据集的sql命令 75 /// </summary> 76 /// <remarks> 77 /// 举例: 78 /// MySqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24)); 79 /// </remarks> 80 /// <param name="connectionString">一个有效的连接字符串</param> 81 /// <param name="cmdType">命令类型(存储过程, 文本, 等等)</param> 82 /// <param name="cmdText">存储过程名称或者sql命令语句</param> 83 /// <param name="commandParameters">执行命令所用参数的集合</param> 84 /// <returns>包含结果的读取器</returns> 85 public static MySqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters) 86 { 87 //创建一个MySqlCommand对象 88 MySqlCommand cmd = new MySqlCommand(); 89 //创建一个MySqlConnection对象 90 MySqlConnection conn = new MySqlConnection(connectionString); 91 92 //在这里我们用一个try/catch结构执行sql文本命令/存储过程，因为如果这个方法产生一个异常我们要关闭连接，因为没有读取器存在， 93 //因此commandBehaviour.CloseConnection 就不会执行 94 try 95 { 96 //调用 PrepareCommand 方法，对 MySqlCommand 对象设置参数 97 PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); 98 //调用 MySqlCommand 的 ExecuteReader 方法 99 MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); 100 //清除参数 101 cmd.Parameters.Clear(); 102 return reader; 103 } 104 catch 105 { 106 //关闭连接，抛出异常 107 conn.Close(); 108 throw; 109 } 110 } 111 112 /// <summary> 113 /// 返回DataSet 114 /// </summary> 115 /// <param name="connectionString">一个有效的连接字符串</param> 116 /// <param name="cmdType">命令类型(存储过程, 文本, 等等)</param> 117 /// <param name="cmdText">存储过程名称或者sql命令语句</param> 118 /// <param name="commandParameters">执行命令所用参数的集合</param> 119 /// <returns></returns> 120 public static DataSet GetDataSet(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters) 121 { 122 //创建一个MySqlCommand对象 123 MySqlCommand cmd = new MySqlCommand(); 124 //创建一个MySqlConnection对象 125 MySqlConnection conn = new MySqlConnection(connectionString); 126 127 //在这里我们用一个try/catch结构执行sql文本命令/存储过程，因为如果这个方法产生一个异常我们要关闭连接，因为没有读取器存在， 128 129 try 130 { 131 //调用 PrepareCommand 方法，对 MySqlCommand 对象设置参数 132 PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); 133 //调用 MySqlCommand 的 ExecuteReader 方法 134 MySqlDataAdapter adapter = new MySqlDataAdapter(); 135 adapter.SelectCommand = cmd; 136 DataSet ds = new DataSet(); 137 138 adapter.Fill(ds); 139 //清除参数 140 cmd.Parameters.Clear(); 141 conn.Close(); 142 return ds; 143 } 144 catch (Exception e) 145 { 146 throw e; 147 } 148 } 149 150 151 public static DataTable GetDataTable(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters) 152 { 153 //创建一个MySqlCommand对象 154 MySqlCommand cmd = new MySqlCommand(); 155 //创建一个MySqlConnection对象 156 MySqlConnection conn = new MySqlConnection(connectionString); 157 158 //在这里我们用一个try/catch结构执行sql文本命令/存储过程，因为如果这个方法产生一个异常我们要关闭连接，因为没有读取器存在， 159 160 try 161 { 162 //调用 PrepareCommand 方法，对 MySqlCommand 对象设置参数 163 PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); 164 //调用 MySqlCommand 的 ExecuteReader 方法 165 MySqlDataAdapter adapter = new MySqlDataAdapter(); 166 adapter.SelectCommand = cmd; 167 DataTable ds = new DataTable(); 168 169 adapter.Fill(ds); 170 //清除参数 171 cmd.Parameters.Clear(); 172 conn.Close(); 173 return ds; 174 } 175 catch (Exception e) 176 { 177 throw e; 178 } 179 } 180 181 182 /// <summary> 183 /// 用指定的数据库连接字符串执行一个命令并返回一个数据集的第一列 184 /// </summary> 185 /// <remarks> 186 ///例如: 187 /// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24)); 188 /// </remarks> 189 ///<param name="connectionString">一个有效的连接字符串</param> 190 /// <param name="cmdType">命令类型(存储过程, 文本, 等等)</param> 191 /// <param name="cmdText">存储过程名称或者sql命令语句</param> 192 /// <param name="commandParameters">执行命令所用参数的集合</param> 193 /// <returns>用 Convert.To{Type}把类型转换为想要的 </returns> 194 public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters) 195 { 196 MySqlCommand cmd = new MySqlCommand(); 197 198 using (MySqlConnection connection = new MySqlConnection(connectionString)) 199 { 200 PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters); 201 object val = cmd.ExecuteScalar(); 202 cmd.Parameters.Clear(); 203 return val; 204 } 205 } 206 207 /// <summary> 208 /// 用指定的数据库连接执行一个命令并返回一个数据集的第一列 209 /// </summary> 210 /// <remarks> 211 /// 例如: 212 /// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24)); 213 /// </remarks> 214 /// <param name="connection">一个存在的数据库连接</param> 215 /// <param name="cmdType">命令类型(存储过程, 文本, 等等)</param> 216 /// <param name="cmdText">存储过程名称或者sql命令语句</param> 217 /// <param name="commandParameters">执行命令所用参数的集合</param> 218 /// <returns>用 Convert.To{Type}把类型转换为想要的 </returns> 219 public static object ExecuteScalar(MySqlConnection connection, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters) 220 { 221 222 MySqlCommand cmd = new MySqlCommand(); 223 224 PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters); 225 object val = cmd.ExecuteScalar(); 226 cmd.Parameters.Clear(); 227 return val; 228 } 229 230 231 232 233 /// <summary> 234 /// 准备执行一个命令 235 /// </summary> 236 /// <param name="cmd">sql命令</param> 237 /// <param name="conn">OleDb连接</param> 238 /// <param name="trans">OleDb事务</param> 239 /// <param name="cmdType">命令类型例如 存储过程或者文本</param> 240 /// <param name="cmdText">命令文本,例如:Select * from Products</param> 241 /// <param name="cmdParms">执行命令的参数</param> 242 private static void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, CommandType cmdType, string cmdText, MySqlParameter[] cmdParms) 243 { 244 245 if (conn.State != ConnectionState.Open) 246 conn.Open(); 247 248 cmd.Connection = conn; 249 cmd.CommandText = cmdText; 250 251 if (trans != null) 252 cmd.Transaction = trans; 253 254 cmd.CommandType = cmdType; 255 256 if (cmdParms != null) 257 258 { 259 260 foreach (MySqlParameter parm in cmdParms) 261 cmd.Parameters.Add(parm); 262 } 263 } 264 265 }
2,User类与UserHelper类
建立用户类User与用户操作类UserHelper
User
1public class User 2 { 3 public int Id { get; set; } 4 public string UserName { get; set; } 5 public string Psd { get; set; } 6 public string PhoneNum { get; set; } 7 8 9 }
UserHelper
1public class UserHelper 2 { 3 4 5 /// <summary> 6 /// 获取用户列表 7 /// </summary> 8 /// <param name="cmdText"></param> 9 /// <returns></returns> 10 public static List<User> GetUsers() 11 { 12 var cmdText = "select * from Users"; 13 var data = MysqlHelper.GetDataSet(MysqlHelper.Conn, System.Data.CommandType.Text, cmdText, new MySql.Data.MySqlClient.MySqlParameter()); 14 List<User> userList = new List<User>(); 15 foreach (DataRow row in data.Tables[0].Rows) 16 { 17 User user = new User(); 18 user.Id = int.Parse(row[0].ToString()); 19 user.UserName=(row[1].ToString()); 20 user.Psd=(row[2].ToString()); 21 user.PhoneNum=(row[3].ToString()); 22 userList.Add(user); 23 } 24 return userList; 25 } 26 /// <summary> 27 /// 根据姓名查找用户 28 /// </summary> 29 /// <param name="name"></param> 30 /// <returns></returns> 31 32 public static User GetUserByName(string name) 33 { 34 var cmdText = "select * from Users Where UserName=?name"; 35 var pars = new MySql.Data.MySqlClient.MySqlParameter("?name", name); 36 var data = MysqlHelper.GetDataSet(MysqlHelper.Conn, System.Data.CommandType.Text, cmdText, pars); 37 User user = new User(); 38 if (data.Tables.Count == 0) 39 { 40 return null; 41 } 42 if (data.Tables[0].Rows.Count != 1) 43 { 44 return null; 45 } 46 foreach (DataRow row in data.Tables[0].Rows) 47 { 48 user.Id = int.Parse(row[0].ToString()); 49 user.UserName = (row[1].ToString()); 50 user.Psd = (row[2].ToString()); 51 user.PhoneNum = (row[3].ToString()); 52 ; 53 } 54 return user; 55 } 56 57 /// <summary> 58 /// 添加用户 59 /// </summary> 60 /// <param name="user"></param> 61 /// <returns></returns> 62 public static bool AddUser(User user) 63 { 64 var sqlInsert = "insert into Users(UserName,Psd,Tel) values" + 65 "('" + user.UserName + "','" + user.Psd + "','" + user.PhoneNum + "')"; 66 var parms = new MySqlParameter(); 67 68 var data = MysqlHelper.ExecuteNonQuery(MysqlHelper.Conn, System.Data.CommandType.Text, sqlInsert, parms); 69 70 return data>0; 71 72 73 } 74 75 }
3 Socket
网络上的两个程序通过一个双向的通信连接实现数据的交换，这个连接的一端称为一个socket.socket本质是编程接口(API)，对TCP/IP的封装，TCP/IP也要提供可供程序员做网络开发所用的接口，这就是Socket编程接口；
利用Socket技术,可以捕捉Http请求,获取数据.构建SocketHelper类,获取前端页面请求.默认Socket 端口为8086
1public class SocketHelper 2 { 3 4 5 static Socket m_socket = new Socket(AddressFamily.InterNetwork, SocketType.Stream, ProtocolType.Tcp); 6 /// <summary> 7 /// Socket初始化 8 /// </summary> 9 public static bool Init() 10 { 11 try 12 { 13 m_socket.Bind(new IPEndPoint(IPAddress.Any, 8086)); 14 m_socket.Listen(100); 15 m_socket.BeginAccept(new AsyncCallback(OnAccept), m_socket); 16 Console.WriteLine("开启Socket服务成功!!"); 17 Console.Read(); 18 return true; 19 } 20 catch (Exception ) 21 { 22 Console.WriteLine("开启Socket服务失败!!,请检查网络,端口8086是否被占用!!!"); 23 Console.Read(); 24 return false; 25 } 26 27 } 28 public static void Route(string path, Dictionary<string, string> param, Socket response) 29 { 30 if (param.Count == 1) 31 { 32 try 33 { 34 var userName = param["userName"]; 35 var searUser = UserHelper.GetUserByName(userName); 36 if (searUser != null) 37 { 38 HomePage(response, "该用户名已经被占用!!"); 39 Console.WriteLine("** - **"); 40 Console.WriteLine("用户名" + userName + "被占用"); 41 Console.WriteLine(""); 42 HomePage(response, "no"); 43 } 44 else 45 { 46 HomePage(response, "ok"); 47 } 48 } 49 catch (Exception e) 50 { 51 52 HomePage(response, e.Message); 53 Console.WriteLine("** - **"); 54 Console.WriteLine("发生错误,错误原因为" + e.Message); 55 Console.WriteLine(""); 56 } 57 58 59 return; 60 61 } 62 else if (param.Count == 3) { 63 try 64 { 65 User user = new User(); 66 user.UserName = param["userName"]; 67 user.Psd = param["psd"]; 68 user.PhoneNum = param["phoneNum"]; 69 var isSuccess = UserHelper.AddUser(user); 70 if (isSuccess) 71 { 72 HomePage(response, "ok"); 73 Console.WriteLine("** - **"); 74 Console.WriteLine("用户名为" + user.UserName + "已添加到数据库!!"); 75 Console.WriteLine(""); 76 } 77 else 78 { 79 HomePage(response, "no"); 80 Console.WriteLine("** - **"); 81 Console.WriteLine("用户名为" + user.UserName + "添加到数据库失败!!"); 82 Console.WriteLine(""); 83 } 84 } 85 catch (Exception e) 86 { 87 88 HomePage(response, e.Message); 89 Console.WriteLine("** - **"); 90 Console.WriteLine("发生错误,错误原因为" + e.Message); 91 Console.WriteLine(""); 92 } 93 } 94 else 95 { 96 HomePage(response, "参数错误!!"); 97 Console.WriteLine("** - **"); 98 Console.WriteLine("参数错误" ); 99 Console.WriteLine(""); 100 101 102 } 103 104 105 return; 106 107 } 108 109 public static void OnAccept(IAsyncResult ar) 110 { 111 try 112 { 113 Socket socket = ar.AsyncState as Socket; 114 Socket new_client = socket.EndAccept(ar); 115 socket.BeginAccept(new AsyncCallback(OnAccept), socket); 116 byte[] recv_buffer = new byte[1024 * 640]; 117 int real_recv = new_client.Receive(recv_buffer); 118 119 string recv_request = Encoding.UTF8.GetString(recv_buffer, 0, real_recv); 120 Console.WriteLine(recv_request); 121 122 Resolve(recv_request, new_client); 123 } 124 catch 125 { 126 127 } 128 } 129 130 public static void Resolve(string request, Socket response) 131 { 132 string[] strs = request.Split(new string[] { "\r\n" }, StringSplitOptions.None); 133 if (strs.Length > 0) 134 { 135 string[] items = strs[0].Split(' '); 136 Dictionary<string, string> param = new Dictionary<string, string>(); 137 138 if (strs.Contains("")) 139 { 140 string post_data = strs[strs.Length - 1]; 141 if (post_data != "") 142 { 143 string[] post_datas = post_data.Split('&'); 144 foreach (string s in post_datas) 145 { 146 param.Add(s.Split('=')[0], s.Split('=')[1]); 147 } 148 } 149 } 150 Route(items[1], param, response); 151 } 152 } 153 154 public static void HomePage(Socket response,string result) 155 { 156 string statusline = "HTTP/1.1 200 OK\r\n"; 157 byte[] statusline_to_bytes = Encoding.UTF8.GetBytes(statusline); 158 159 string content = result; 160 byte[] content_to_bytes = Encoding.UTF8.GetBytes(content); 161 162 string header = string.Format("Access-Control-Allow-Origin:*\r\nContent-Type:text/html;charset=UTF-8\r\nContent-Length:{0}\r\n", content_to_bytes.Length); 163 byte[] header_to_bytes = Encoding.UTF8.GetBytes(header); 164 165 response.Send(statusline_to_bytes); 166 response.Send(header_to_bytes); 167 response.Send(new byte[] { (byte)'\r', (byte)'\n' }); 168 response.Send(content_to_bytes); 169 response.Close(); 170 } 171 }
第三部分 前端页面与Ajax请求
前端页面设计为:
ajax请求代码:
1$('.red_button').click(function () { 2 if (user_Boolean && password_Boolean && varconfirm_Boolean && Mobile_Boolean == true) { 3 $.ajax({ 4 type: 'POST', 5 url: 'http://127.0.0.1:8086/', 6 data: { 7 'userName':$('#userName').val(), 8 'psd': $('#psd').val(), 9 'phoneNum': $('#phoneNum').val() 10 }, 11 success: function (data) { 12 if (data == "ok") { 13 alert("注册成功!!") 14 } else { 15 alert("data"); 16 } 17 18 } 19 20 }); 21 } 22 else { 23 alert("请完善信息"); 24 } 25});
至此,程序完成.运行程序进行验证.
1:启动服务端程序
2:启动前端页面，进行数据填写
3 添加结果
至此 ，Demo功能完成。之前没有试过利用Socket技术完成Ajax请求,这次也算是一次尝试,偶尔看到这个程序,以防以后需要 做个笔记..