需求: 利用MySql資料庫結合前端技術完成用戶的註冊(要求不使用Web服務技術),所以 Demo採用Socket技術實現Web通信. 第一部分:資料庫創建 資料庫採用mysql 5.7.18, 資料庫名稱為MyUser, 內部有一張表 user.欄位有 Id,UserName,Psd,Tel 第二 ...
需求: 利用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("請完善信息"); } });
至此,程式完成.運行程式進行驗證.
1:啟動服務端程式
2:啟動前端頁面，進行數據填寫
3 添加結果
至此 ，Demo功能完成。之前沒有試過利用Socket技術完成Ajax請求,這次也算是一次嘗試,偶爾看到這個程式,以防以後需要 做個筆記..
