MySql Socket 完成數據庫的增查Demo

? ? ? ? ?需求: 利用MySql數據庫結合前端技術完成用戶的注冊(要求不使用Web服務技術),所以 Demo采用Socket技術實現Web通信.

第一部分:數據庫創建

數據庫采用mysql 5.7.18, 數據庫名稱為MyUser, 內部有一張表 user.字段有 Id,UserName,Psd,Tel

第二部分:數據庫連接與Socket通信

創建控制臺程序(服務端程序),添加以下類

1 MySqlHelper

?建立MySqlHelper 類,用于實現數據庫操作

public class MysqlHelper{//數據庫連接字符串 public static string Conn = "Database='Myuser';Data Source='localhost';User Id='root';Password='';charset='utf8'"; //XXX的為修改項public static   void SetConn(string UserName = "root", string Password="", string IP= "localhost",   string Database="Myuser"){Conn = "datasource="   IP   ";username="   UserName   ";password="   Password   ";database="   Database   ";charset=utf8";}/// <summary> /// 給定連接的數據庫用假設參數執行一個sql命令（不返回數據集） /// </summary> /// <param name="connectionString">一個有效的連接字符串</param> /// <param name="cmdType">命令類型(存儲過程, 文本, 等等)</param> /// <param name="cmdText">存儲過程名稱或者sql命令語句</param> /// <param name="commandParameters">執行命令所用參數的集合</param> /// <returns>執行命令所影響的行數</returns> public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters){MySqlCommand cmd = new MySqlCommand();using (MySqlConnection conn = new MySqlConnection(connectionString)){PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);int val = cmd.ExecuteNonQuery();cmd.Parameters.Clear();return val;}}/// <summary> /// 用現有的數據庫連接執行一個sql命令（不返回數據集） /// </summary> /// <param name="connection">一個現有的數據庫連接</param> /// <param name="cmdType">命令類型(存儲過程, 文本, 等等)</param> /// <param name="cmdText">存儲過程名稱或者sql命令語句</param> /// <param name="commandParameters">執行命令所用參數的集合</param> /// <returns>執行命令所影響的行數</returns> public static int ExecuteNonQuery(MySqlConnection connection, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters){MySqlCommand cmd = new MySqlCommand();PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);int val = cmd.ExecuteNonQuery();//cmd.Parameters.Clear();return val;}/// <summary> ///使用現有的SQL事務執行一個sql命令（不返回數據集） /// </summary> /// <remarks> ///舉例: /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24)); /// </remarks> /// <param name="trans">一個現有的事務</param> /// <param name="cmdType">命令類型(存儲過程, 文本, 等等)</param> /// <param name="cmdText">存儲過程名稱或者sql命令語句</param> /// <param name="commandParameters">執行命令所用參數的集合</param> /// <returns>執行命令所影響的行數</returns> public static int ExecuteNonQuery(MySqlTransaction trans, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters){MySqlCommand cmd = new MySqlCommand();PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);int val = cmd.ExecuteNonQuery();cmd.Parameters.Clear();return val;}/// <summary> /// 用執行的數據庫連接執行一個返回數據集的sql命令 /// </summary> /// <remarks> /// 舉例: /// MySqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24)); /// </remarks> /// <param name="connectionString">一個有效的連接字符串</param> /// <param name="cmdType">命令類型(存儲過程, 文本, 等等)</param> /// <param name="cmdText">存儲過程名稱或者sql命令語句</param> /// <param name="commandParameters">執行命令所用參數的集合</param> /// <returns>包含結果的讀取器</returns> public static MySqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters){//創建一個MySqlCommand對象 MySqlCommand cmd = new MySqlCommand();//創建一個MySqlConnection對象 MySqlConnection conn = new MySqlConnection(connectionString);//在這里我們用一個try/catch結構執行sql文本命令/存儲過程，因為如果這個方法產生一個異常我們要關閉連接，因為沒有讀取器存在， //因此commandBehaviour.CloseConnection 就不會執行 try{//調用 PrepareCommand 方法，對 MySqlCommand 對象設置參數 PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);//調用 MySqlCommand 的 ExecuteReader 方法 MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);//清除參數 cmd.Parameters.Clear();return reader;}catch{//關閉連接，拋出異常 conn.Close();throw;}}/// <summary> /// 返回DataSet /// </summary> /// <param name="connectionString">一個有效的連接字符串</param> /// <param name="cmdType">命令類型(存儲過程, 文本, 等等)</param> /// <param name="cmdText">存儲過程名稱或者sql命令語句</param> /// <param name="commandParameters">執行命令所用參數的集合</param> /// <returns></returns> public static DataSet GetDataSet(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters){//創建一個MySqlCommand對象 MySqlCommand cmd = new MySqlCommand();//創建一個MySqlConnection對象 MySqlConnection conn = new MySqlConnection(connectionString);//在這里我們用一個try/catch結構執行sql文本命令/存儲過程，因為如果這個方法產生一個異常我們要關閉連接，因為沒有讀取器存在，try{//調用 PrepareCommand 方法，對 MySqlCommand 對象設置參數 PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);//調用 MySqlCommand 的 ExecuteReader 方法 MySqlDataAdapter adapter = new MySqlDataAdapter();adapter.SelectCommand = cmd;DataSet ds = new DataSet();adapter.Fill(ds);//清除參數 cmd.Parameters.Clear();conn.Close();return ds;}catch (Exception e){throw e;}}public static DataTable GetDataTable(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters){//創建一個MySqlCommand對象 MySqlCommand cmd = new MySqlCommand();//創建一個MySqlConnection對象 MySqlConnection conn = new MySqlConnection(connectionString);//在這里我們用一個try/catch結構執行sql文本命令/存儲過程，因為如果這個方法產生一個異常我們要關閉連接，因為沒有讀取器存在，try{//調用 PrepareCommand 方法，對 MySqlCommand 對象設置參數 PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);//調用 MySqlCommand 的 ExecuteReader 方法 MySqlDataAdapter adapter = new MySqlDataAdapter();adapter.SelectCommand = cmd;DataTable ds = new DataTable();adapter.Fill(ds);//清除參數 cmd.Parameters.Clear();conn.Close();return ds;}catch (Exception e){throw e;}}/// <summary> /// 用指定的數據庫連接字符串執行一個命令并返回一個數據集的第一列 /// </summary> /// <remarks> ///例如: /// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24)); /// </remarks> ///<param name="connectionString">一個有效的連接字符串</param> /// <param name="cmdType">命令類型(存儲過程, 文本, 等等)</param> /// <param name="cmdText">存儲過程名稱或者sql命令語句</param> /// <param name="commandParameters">執行命令所用參數的集合</param> /// <returns>用 Convert.To{Type}把類型轉換為想要的 </returns> public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters){MySqlCommand cmd = new MySqlCommand();using (MySqlConnection connection = new MySqlConnection(connectionString)){PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);object val = cmd.ExecuteScalar();cmd.Parameters.Clear();return val;}}/// <summary> /// 用指定的數據庫連接執行一個命令并返回一個數據集的第一列 /// </summary> /// <remarks> /// 例如: /// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24)); /// </remarks> /// <param name="connection">一個存在的數據庫連接</param> /// <param name="cmdType">命令類型(存儲過程, 文本, 等等)</param> /// <param name="cmdText">存儲過程名稱或者sql命令語句</param> /// <param name="commandParameters">執行命令所用參數的集合</param> /// <returns>用 Convert.To{Type}把類型轉換為想要的 </returns> public static object ExecuteScalar(MySqlConnection connection, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters){MySqlCommand cmd = new MySqlCommand();PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);object val = cmd.ExecuteScalar();cmd.Parameters.Clear();return val;}/// <summary> /// 準備執行一個命令 /// </summary> /// <param name="cmd">sql命令</param> /// <param name="conn">OleDb連接</param> /// <param name="trans">OleDb事務</param> /// <param name="cmdType">命令類型例如 存儲過程或者文本</param> /// <param name="cmdText">命令文本,例如:Select * from Products</param> /// <param name="cmdParms">執行命令的參數</param> private static void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, CommandType cmdType, string cmdText, MySqlParameter[] cmdParms){if (conn.State != ConnectionState.Open)conn.Open();cmd.Connection = conn;cmd.CommandText = cmdText;if (trans != null)cmd.Transaction = trans;cmd.CommandType = cmdType;if (cmdParms != null){foreach (MySqlParameter parm in cmdParms)cmd.Parameters.Add(parm);}}}

2,User類與UserHelper類

建立用戶類User與用戶操作類UserHelper

User

public class User{public int Id { get; set; }public string UserName { get; set; }public string Psd { get; set; }public string PhoneNum { get; set; }}

UserHelper

public   class UserHelper{/// <summary>/// 獲取用戶列表/// </summary>/// <param name="cmdText"></param>/// <returns></returns>public static  List<User> GetUsers(){var cmdText = "select * from Users";var data = MysqlHelper.GetDataSet(MysqlHelper.Conn, System.Data.CommandType.Text, cmdText, new MySql.Data.MySqlClient.MySqlParameter());List<User> userList = new List<User>();foreach (DataRow row in data.Tables[0].Rows){User user = new User();user.Id = int.Parse(row[0].ToString());user.UserName=(row[1].ToString());user.Psd=(row[2].ToString());user.PhoneNum=(row[3].ToString());userList.Add(user);}return userList;}/// <summary>/// 根據姓名查找用戶/// </summary>/// <param name="name"></param>/// <returns></returns>public static  User GetUserByName(string name){var cmdText = "select * from Users Where UserName=?name";var pars = new MySql.Data.MySqlClient.MySqlParameter("?name", name);var data = MysqlHelper.GetDataSet(MysqlHelper.Conn, System.Data.CommandType.Text, cmdText, pars);User user = new User();if (data.Tables.Count == 0){return null;}if (data.Tables[0].Rows.Count != 1){return null;}foreach (DataRow row in data.Tables[0].Rows){               user.Id = int.Parse(row[0].ToString());user.UserName = (row[1].ToString());user.Psd = (row[2].ToString());user.PhoneNum = (row[3].ToString());;}return user;}/// <summary>/// 添加用戶/// </summary>/// <param name="user"></param>/// <returns></returns>public static bool AddUser(User user){var sqlInsert = "insert into Users(UserName,Psd,Tel) values"  "('"   user.UserName   "','"   user.Psd   "','"   user.PhoneNum   "')";           var parms = new MySqlParameter();var data = MysqlHelper.ExecuteNonQuery(MysqlHelper.Conn, System.Data.CommandType.Text, sqlInsert, parms);return data>0;}}

3 Socket

網絡上的兩個程序通過一個雙向的通信連接實現數據的交換，這個連接的一端稱為一個socket.socket本質是編程接口(API)，對TCP/IP的封裝，TCP/IP也要提供可供程序員做網絡開發所用的接口，這就是Socket編程接口；

利用Socket技術,可以捕捉Http請求,獲取數據.構建SocketHelper類,獲取前端頁面請求.默認Socket 端口為8086

public class SocketHelper{static Socket m_socket = new Socket(AddressFamily.InterNetwork, SocketType.Stream, ProtocolType.Tcp);/// <summary>/// Socket初始化 /// </summary>public static bool  Init(){try{m_socket.Bind(new IPEndPoint(IPAddress.Any, 8086));m_socket.Listen(100);m_socket.BeginAccept(new AsyncCallback(OnAccept), m_socket);Console.WriteLine("開啟Socket服務成功!!");Console.Read();return true;}catch (Exception ){Console.WriteLine("開啟Socket服務失敗!!,請檢查網絡,端口8086是否被占用!!!");Console.Read();return false;}}public static void Route(string path, Dictionary<string, string> param, Socket response){if (param.Count == 1){try{var userName = param["userName"];var searUser = UserHelper.GetUserByName(userName);if (searUser != null){HomePage(response, "該用戶名已經被占用!!");Console.WriteLine("** - **");Console.WriteLine("用戶名"   userName   "被占用");Console.WriteLine("");HomePage(response, "no");}else{HomePage(response, "ok");}}catch (Exception e){HomePage(response, e.Message);Console.WriteLine("** - **");Console.WriteLine("發生錯誤,錯誤原因為"   e.Message);Console.WriteLine("");}return;}else if (param.Count == 3) {try{User user = new User();user.UserName = param["userName"];user.Psd = param["psd"];user.PhoneNum = param["phoneNum"];var isSuccess = UserHelper.AddUser(user);if (isSuccess){HomePage(response, "ok");Console.WriteLine("** - **");Console.WriteLine("用戶名為"   user.UserName   "已添加到數據庫!!");Console.WriteLine("");}else{HomePage(response, "no");Console.WriteLine("** - **");Console.WriteLine("用戶名為"   user.UserName   "添加到數據庫失敗!!");Console.WriteLine("");}}catch (Exception e){HomePage(response, e.Message);Console.WriteLine("** - **");Console.WriteLine("發生錯誤,錯誤原因為"   e.Message);Console.WriteLine("");}}else{HomePage(response, "參數錯誤!!");Console.WriteLine("** - **");Console.WriteLine("參數錯誤" );Console.WriteLine("");}return;}public static void OnAccept(IAsyncResult ar){try{Socket socket = ar.AsyncState as Socket;Socket new_client = socket.EndAccept(ar);socket.BeginAccept(new AsyncCallback(OnAccept), socket);byte[] recv_buffer = new byte[1024 * 640];int real_recv = new_client.Receive(recv_buffer);string recv_request = Encoding.UTF8.GetString(recv_buffer, 0, real_recv);Console.WriteLine(recv_request);Resolve(recv_request, new_client);}catch{}}public static void Resolve(string request, Socket response){string[] strs = request.Split(new string[] { "\r\n" }, StringSplitOptions.None);if (strs.Length > 0){string[] items = strs[0].Split(' ');Dictionary<string, string> param = new Dictionary<string, string>();if (strs.Contains("")){string post_data = strs[strs.Length - 1];if (post_data != ""){string[] post_datas = post_data.Split('&');foreach (string s in post_datas){param.Add(s.Split('=')[0], s.Split('=')[1]);}}}Route(items[1], param, response);}}public static void HomePage(Socket response,string result){string statusline = "HTTP/1.1 200 OK\r\n";byte[] statusline_to_bytes = Encoding.UTF8.GetBytes(statusline);string content = result;byte[] content_to_bytes = Encoding.UTF8.GetBytes(content);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);byte[] header_to_bytes = Encoding.UTF8.GetBytes(header);response.Send(statusline_to_bytes);response.Send(header_to_bytes);response.Send(new byte[] { (byte)'\r', (byte)'\n' });response.Send(content_to_bytes);response.Close();}}

第三部分前端頁面與Ajax請求

?前端頁面設計為:

ajax請求代碼:

$('.red_button').click(function () {if (user_Boolean && password_Boolean && varconfirm_Boolean && Mobile_Boolean == true) {$.ajax({type: 'POST',url: 'http://127.0.0.1:8086/',data: {'userName':$('#userName').val(),'psd': $('#psd').val(),'phoneNum': $('#phoneNum').val()},success: function (data) {if (data == "ok") {alert("注冊成功!!")} else {alert("data");}}});} else {alert("請完善信息");}
});