复习一下ADO.NET，和数据库进行的数据库交互。 内容来自www.wkfxw.com

工具/原料

Vs2012

sqlserver2008

方法/步骤

首先我们新建一个窗体project

添加一个窗体。

添加一个helper类。

窗体简单的布局如下

DBHELPER类的代码如下;

class DBHelper

{

private static string strConn = ConfigurationManager.ConnectionStrings["strConn"].ConnectionString;

private static SqlConnection conn = new SqlConnection(strConn);

/// <summary>

/// 获得数据库连接对象

/// </summary>

public static SqlConnection Conn

{

get {

return conn;

}

}

/// <summary>

/// 打开数据库连接

/// </summary>

public static void ConnOpen()

{

if (conn.State == System.Data.ConnectionState.Closed)

conn.Open();

}

/// <summary>

/// 关闭数据库连接

/// </summary>

public static void ConnClose()

{

if (conn.State == System.Data.ConnectionState.Open)

conn.Close();

}

/// <summary>

/// 对数据进行操作，增，删，改

/// </summary>

/// <param name="strSql">数据操作的Sql语句</param>

/// <returns></returns>

public static int ExecuteNonQuery(string strSql)

{

int i = 0;

try

{

SqlCommand comm = new SqlCommand();

comm.CommandText = strSql;//将数据操作语句赋值给CommandText属性

comm.Connection = conn;

ConnOpen();

i = comm.ExecuteNonQuery();

}

catch (Exception e)

{

}

finally

{

ConnClose();

}

return i;

}

/// <summary>

/// 对数据进行操作，增，删，改

/// </summary>

/// <param name="strSql">数据操作的Sql语句</param>

/// <returns></returns>

public static int ExecuteNonQuery(string strSql,SqlParameter[] param)

{

int i = 0;

try

{

SqlCommand comm = new SqlCommand(strSql,conn);

comm.Parameters.AddRange(param);

ConnOpen();

i = comm.ExecuteNonQuery();

}

catch (Exception e)

{

}

finally

{

ConnClose();

}

return i;

}

}

具体实现用户register功能

按钮事件代码如下

/// <summary>

/// 按钮事件

/// </summary>

/// <param name="sender"></param>

/// <param name="e"></param>

private void btnRegist_Click(object sender, EventArgs e)

{

if (Check())

{

string strName = txtName.Text.Trim();

string strUserName = txtUserName.Text.Trim();

string strPwd = PwdForMD5(txtPwd.Text);//得到密码并加密

string strDesc = txtDesc.Text;

//InsertData(strName, strUserName, strPwd, strDesc);//采取sql语句拼凑方式,易被Sql注入式攻击

//InsertDataForParameter(strName, strUserName, strPwd, strDesc);// 采用参数形式执行,可以防止注入式攻击,参数是一个个添加

//InsertDataForParameterRange(strName, strUserName, strPwd, strDesc);//参数形式第二步，将参数加入到数组中，一次全部添加

IsertDataForDbHelper(strName, strUserName, strPwd, strDesc);//参数形式，调用DbHelper类中的方法，传入语句和参数即可

}

}

上面的代码有三个方法是注释的，三种都可以实现只是参数的添加不一样

InsertData方法

#region InsertData 采取sql语句拼凑方式,易被Sql注入式攻击

/// <summary>

/// 注册方法一

/// </summary>

/// <param name="strName"></param>

/// <param name="strUserName"></param>

/// <param name="strPwd"></param>

/// <param name="strDesc"></param>

private void InsertData(string strName, string strUserName, string strPwd, string strDesc)

{

//

string strSql = "insert into [User]([Name],UserName,Password,TypeID,[Desc]) ";

strSql += " values('" + strName + "','" + strUserName + "','" + strPwd + "',1,'" + strDesc + "')";

if (DBHelper.ExecuteNonQuery(strSql) > 0)

{

MessageBox.Show("提交成功！", "系统提示");

}

else

MessageBox.Show("提交失败！", "系统提示");

}

#endregion

InsertDataForParameter 方法

#region InsertDataForParameter 采用参数形式执行,可以防止注入式攻击,参数是一个个添加

/// <summary>

/// 采用参数形式执行命令

/// </summary>

/// <param name="strName"></param>

/// <param name="strUserName"></param>

/// <param name="strPwd"></param>

/// <param name="strDesc"></param>

private void InsertDataForParameter(string strName, string strUserName, string strPwd, string strDesc)

{

string strSql = "insert into [User]([Name],UserName,Password,TypeID,[Desc]) ";

strSql += " values(@Name,@UserName,@Password,1,@Desc)";

SqlParameter param1 = new SqlParameter();

param1.ParameterName="@Name";

param1.SqlDbType = SqlDbType.VarChar;

param1.Size = 16;

param1.Value = strName;

SqlParameter param2 = new SqlParameter("@UserName", SqlDbType.VarChar, 16);

param2.Value = strUserName;

SqlParameter param3 = new SqlParameter("@Password", SqlDbType.VarChar, 64);

param3.Value = strPwd;

SqlParameter param4 = new SqlParameter("@Desc", SqlDbType.VarChar, 256);

param4.Value = strDesc;

SqlCommand comm = new SqlCommand(strSql,DBHelper.Conn);

comm.Parameters.Add(param1);

comm.Parameters.Add(param2);

comm.Parameters.Add(param3);

comm.Parameters.Add(param4);

DBHelper.ConnOpen();

if (comm.ExecuteNonQuery() > 0)

{

MessageBox.Show("提交成功！", "系统提示");

}

else

MessageBox.Show("提交失败！", "系统提示");

DBHelper.ConnClose();

}

#endregion

InsertDataForParameterRange 方法

#region InsertDataForParameterRange 参数形式第二步，将参数加入到数组中，一次全部添加

/// <summary>

/// 采用参数形式执行命令

/// </summary>

/// <param name="strName"></param>

/// <param name="strUserName"></param>

/// <param name="strPwd"></param>

/// <param name="strDesc"></param>

private void InsertDataForParameterRange(string strName, string strUserName, string strPwd, string strDesc)

{

string strSql = "insert into [User]([Name],UserName,Password,TypeID,[Desc]) ";

strSql += " values(@Name,@UserName,@Password,1,@Desc)";

SqlParameter[] param = {

new SqlParameter("@Name", SqlDbType.VarChar, 16) ,

new SqlParameter("@UserName", SqlDbType.VarChar, 16),

new SqlParameter("@Password", SqlDbType.VarChar, 64),

new SqlParameter("@Desc", SqlDbType.VarChar, 256)

};

param[0].Value = strName;

param[1].Value = strUserName;

param[2].Value = strPwd;

param[3].Value = strDesc;

SqlCommand comm = new SqlCommand(strSql, DBHelper.Conn);

//将参数数组一次追加

comm.Parameters.AddRange(param);

DBHelper.ConnOpen();

if (comm.ExecuteNonQuery() > 0)

{

MessageBox.Show("提交成功！", "系统提示");

}

else

MessageBox.Show("提交失败！", "系统提示");

DBHelper.ConnClose();

}

#endregion

IsertDataForDbHelper 方法

#region IsertDataForDbHelper 参数形式，调用DbHelper类中的方法，传入语句和参数即可

private void IsertDataForDbHelper(string strName, string strUserName, string strPwd, string strDesc)

{

string strSql = "insert into [User]([Name],UserName,Password,TypeID,[Desc]) ";

strSql += " values(@Name,@UserName,@Password,1,@Desc)";

SqlParameter[] param = {

new SqlParameter("@Name", SqlDbType.VarChar, 16) ,

new SqlParameter("@UserName", SqlDbType.VarChar, 16),

new SqlParameter("@Password", SqlDbType.VarChar, 64),

new SqlParameter("@Desc", SqlDbType.VarChar, 256)

};

param[0].Value = strName;

param[1].Value = strUserName;

param[2].Value = strPwd;

param[3].Value = strDesc;

//调用DBHelper中的方法

if (DBHelper.ExecuteNonQuery(strSql, param) > 0)

{

MessageBox.Show("提交成功！", "系统提示");

}

else

MessageBox.Show("提交失败！", "系统提示");

}

#endregion

数据验证

#region 数据验证

/// <summary>

/// 提交前的数据验证

/// </summary>

/// <returns></returns>

private bool Check()

{

string strName = txtName.Text.Trim();

if (string.IsNullOrEmpty(strName))//strName == "")//

{

MessageBox.Show("用户姓名不能为空！", "系统提示");

return false;

}

string strUserName = txtUserName.Text.Trim();

if (string.IsNullOrEmpty(strUserName))//strName == "")//

{

MessageBox.Show("用户名不能为空！", "系统提示");

return false;

}

string strPwd = txtPwd.Text;

if (string.IsNullOrEmpty(strPwd))//strName == "")//

{

MessageBox.Show("用户密码不能为空！", "系统提示");

return false;

}

string strRePwd = txtRePwd.Text;

if (strRePwd != strPwd)

{

MessageBox.Show("密码不一致！", "系统提示");

return false;

}

return true;

}

#endregion

加密

#region 加密

/// <summary>

/// 加密密码

/// </summary>

/// <param name="strPwd">密码</param>

/// <returns></returns>

private string PwdForMD5(string strPwd)

{

//定义一个MD5加密的类的对象

MD5CryptoServiceProvider md5 = new MD5CryptoServiceProvider();

//将密码转换成UTF8格式的字节数组

byte[] bytes = Encoding.UTF8.GetBytes(strPwd);

//加密

bytes= md5.ComputeHash(bytes);

//将字节数组转换成字符串

return BitConverter.ToString(bytes);

}

#endregion