ado.net增删改查及存储过程

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

 

namespace exercise_20180305
{
public partial class demo : System.Web.UI.Page
{
string connStr = ConfigurationManager.ConnectionStrings["sq_ruanmou"].ToString();
SqlConnection con = null;
SqlCommand cmd = null;
SqlDataReader read = null;
public void OpenDB(){
con = new SqlConnection(connStr);
con.Open();
}

 

protected void Page_Load(object sender, EventArgs e)
{

}

protected void txtButton_Click(object sender, EventArgs e)
{
try
{
string qq = txtQQ.Text.Trim();
string pwd = txtPwd.Text.Trim();
if (string.IsNullOrEmpty(qq) || string.IsNullOrEmpty(pwd))
{
Response.Write("QQ和密码不能为空");
}
else
{
//string connStr = "Database=sq_ruanmou;Server=.;Integrated Security=false;Uid=sa;Password=Zz608003;";
//string connStr= ConfigurationManager.ConnectionStrings["sq_ruanmou"].ToString();
//SqlConnection con =new SqlConnection(connStr);
//con.Open();

//string sql = string.Format("select UserId from UserInfor where QQ='{0}' and Pwd='{1}'", qq, pwd);
//using (cmd = new SqlCommand(sql, con))
//{
// using (read = cmd.ExecuteReader())
// {
// if (read.HasRows)
// {
// Response.Write("登录成功");
// }
// else
// {
// Response.Write("QQ或密码错误");
// }
//read.Dispose();
//read.Close();
//con.Dispose();
//con.Close();
//string sql = string.Format("select count(*) from UserInfor where QQ='{0}'and Pwd='{1}'", qq, pwd);
//using (cmd = new SqlCommand(sql, con))
//{
// if (Convert.ToInt32(cmd.ExecuteScalar().ToString()) > 0)
// {
// Response.Write("登录成功");
// }
// else
// {
// Response.Write("QQ或密码错误");
// }
//}
OpenDB();
using (cmd = new SqlCommand("procSelUser", con))
{
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter[] para = new SqlParameter[]{
new SqlParameter("@qq",qq),
new SqlParameter("@pwd",pwd)
};
foreach (var p in para)
{
cmd.Parameters.Add(p);
};
using (read = cmd.ExecuteReader())
{
if (read.HasRows)
{
Response.Write("登录成功");
}
else
{
Response.Write("QQ或密码错误");
}
}
}
}
}
catch (Exception ex)
{
Response.Write("网站维护中...");
}
}

 

OpenDB();
//string sql = string.Format("select count(*) from UserInfor where QQ='{0}'and Pwd='{1}'", qq, pwd);
string sql = "select count(*) from UserInfor where QQ=@QQ and Pwd=@Pwd";
SqlParameter[] para = new SqlParameter[]{
new SqlParameter("@qq",qq),
new SqlParameter("@pwd",pwd)
};
using (cmd = new SqlCommand(sql, con))
{

foreach (var p in para)
{
cmd.Parameters.Add(p);
};
if (Convert.ToInt32(cmd.ExecuteScalar().ToString()) > 0)
{
Response.Write("登录成功");
}
else
{
Response.Write("QQ或密码错误");
}
}

 

 

protected void btnAdd_Click(object sender, EventArgs e)
{
try
{
string realname = txtARealName.Text.Trim();
string tel = txtAPhoneNum.Text.Trim();
string niclname = txtANickName.Text.Trim();
string qq = txtAQQ.Text.Trim();
string pwd = txtAPwd.Text.Trim();
string createdtime = txtACreatedTime.Text.Trim();
if (string.IsNullOrEmpty(tel) || string.IsNullOrEmpty(qq) || string.IsNullOrEmpty(pwd))
{
Response.Write("QQ或电话号码或密码不能为空");
}
else
{
OpenDB();
string sql = string.Format("insert into UserInfor (RealName,PhoneNum,QQ,Pwd,CreatedTime,NickName) values('{0}','{1}','{2}','{3}','{4}','{5}')", realname, tel, qq, pwd, createdtime, niclname);
using (cmd = new SqlCommand(sql, con))
{
if (cmd.ExecuteNonQuery() > 0)
{
Response.Write("注册成功");
}
else
{
Response.Write("注册失败");
}
}

}
}
catch(Exception ex)
{
Response.Write("网站维护中...");
}
}

protected void txtDDelete_Click(object sender, EventArgs e)
{
try
{
string userid = txtDUserId.Text.Trim();

if (string.IsNullOrEmpty(userid))
{
Response.Write("用户ID不能为空");
}
else
{
OpenDB();
string sql = string.Format("delete UserInfor where UserId={0}", Convert.ToInt32(userid));
using (cmd = new SqlCommand(sql, con))
{
if (cmd.ExecuteNonQuery() > 0)
{
Response.Write("删除成功");
}
else
{
Response.Write("删除失败");
}
}

}
}
catch (Exception ex)
{
Response.Write("网站维护中...");
}
}

protected void txtUpdate_Click(object sender, EventArgs e)
{
try
{
string userid = txtUUserId.Text.Trim();
string qq = txtUQQ.Text.Trim();
OpenDB();
string sql = string.Format("update UserInfor set QQ='{0}' where UserId='{1}'",qq,Convert.ToInt32(userid));
using (cmd = new SqlCommand(sql, con))
{
if (cmd.ExecuteNonQuery() > 0)
{
Response.Write("修改成功");
}
else
{
Response.Write("修改失败");
}
}

}
catch (Exception ex)
{
Response.Write("网站维护中...");
}
}

}
}