最近整理了一下常用的数据库操作，希望以后有用。
代码
1 using System;
2 using System.Collections.Generic;
3 using System.Linq;
4 using System.Text;
5 using System.Data.SqlClient;
6 using System.ComponentModel;
7 using System.Windows.Forms;
8
9 namespace 课设服务器.CLASS
10 {
11 class ClassSqlData : Component
12 {
13 string connString = @"Data Source=WH\SQLEXPRESS;Initial Catalog=MyChat;Integrated Security=True;Pooling=False";
14
15 public SqlDataReader ExSQLReader(string SQLStr)//执行SQL语句，返回一个SqlDataReader
16 {
17 try
18 {
19 SqlConnection connection = new SqlConnection(connString);
20 SqlCommand command = new SqlCommand(SQLStr, connection);
21 connection.Open();
22 SqlDataReader dr;
23 dr = command.ExecuteReader();
24 return dr;
25 }
26 catch { return null; }
27 }
28
29 public int ExSQLScalar(string SQLStr)//执行SQL语句，返回结果集中的第一行第一列
30 {
31 try
32 {
33 SqlConnection connection = new SqlConnection(connString);
34 SqlCommand command = new SqlCommand(SQLStr, connection);
35 connection.Open();
36 int num = (int)command.ExecuteScalar();
37 return num;
38 }
39 catch { return 0; }
40 }
41
42 public int ExSQLNonQuery(string SQLStr)//执行SQL语句，返回收影响的行数
43 {
44 try
45 {
46 SqlConnection connection = new SqlConnection(connString);
47 SqlCommand command = new SqlCommand(SQLStr, connection);
48 connection.Open();
49 int num = command.ExecuteNonQuery();
50 command.Dispose();
51 connection.Close();
52 connection.Dispose();
53 return num;
54 }
55 catch (Exception e)
56 {
57 MessageBox.Show(e.ToString());
58 return 0;
59 }
60 }
61 }
62 }
63
例如：注册账号（增）
代码
1 #region 注册
2 public void Registering()
3 { string SQLStr2 = String.Format("INSERT INTO UserTab(UserName,PassWord,UserAge,UserSex,NickName,RealName,UserStar,UserBlood,UserId) VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}')",
4 userinfo.UserName, userinfo.PassWord, userinfo.UserAge, userinfo.UserSex, userinfo.NickName, userinfo.RealName, userinfo.UserStar, userinfo.UserBlood, userinfo.UserId);
5 try
6 {
7 ClassSqlData SqlData = new ClassSqlData();
8 int num=SqlData.ExSQLNonQuery(SQLStr2);
9 if (num > 0)
10 {
11 MessageBox.Show("用户注册信息添加到数据库");
12 }
13 else
14 {
15 MessageBox.Show(注册信息失败);
16 }
17 }
18 catch (Exception e)
19 {
20 MessageBox.Show("异常处理：\n"+e.ToString());
21
22 }
23 finally { }
登陆账号：（查）
代码
1 public void Validation()//验证
2 {
3 //MessageBox.Show("验证登陆");
4 string SQLStr = String.Format("select count(*) From [UserTab] where UserName='{0}' and PassWord='{1}'", username, password);
5 try
6 {
7 ClassSqlData SqlData = new ClassSqlData();
8 int num = SqlData.ExSQLScalar(SQLStr);
9 if(num>0)
10 {
11 MessageBox.Show("服务器：账号密码正确");
12 }
13 else
14 {
15 MessageBox.Show("服务器：账号密码错误");
16 }
17 SqlData.Dispose();
18 }
19 catch (Exception e)
20 {
21 MessageBox.Show(e.ToString());
22 }
23 finally
24 {
25
26 }
查找账号： （查）
代码
#region CheckUserInfo
public void CheckUserInfo()
{
string SQLStr = String.Format(" select * From [UserTab] where UserName='{0}'", username);
try
{
ClassSqlData SqlData = new ClassSqlData();
SqlDataReader DataReader = SqlData.ExSQLReader(SQLStr);
UserInfo userinfo = new UserInfo();
while (DataReader.Read())
{
userinfo.UserName = DataReader.GetValue(0).ToString();
userinfo.PassWord = DataReader.GetValue(1).ToString();
userinfo.UserAge = Convert.ToInt32(DataReader.GetValue(2).ToString());
userinfo.UserSex = DataReader.GetValue(3).ToString();
userinfo.NickName = DataReader.GetValue(4).ToString();
userinfo.RealName = DataReader.GetValue(5).ToString();
userinfo.UserStar = DataReader.GetValue(6).ToString();
userinfo.UserBlood = DataReader.GetValue(7).ToString();
userinfo.UserId = Convert.ToInt32(DataReader.GetValue(8).ToString());
MessageBox.Show("服务器查找成功");
}
SqlData.Dispose();
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
}
finally
{
}
改，删等整理下再发布 呵呵
转载几个常用的供大家使用。
代码
1 SqlDataReader数据操作
2 SqlConnection conn=new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Documents and Settings\Administrator.DASHI-88954B745\My Documents\Visual Studio 2008\WebSites\WebSite1\App_Data\Database.mdf;Integrated Security=True;User Instance=True");
3 SqlCommand cmd = new SqlCommand("select * from [User]",conn);
4 conn.Open();
5 SqlDataReader dr = cmd.ExecuteReader();
6 string str="";
7 if (dr.HasRows)//判断是否有数据
8 {
9 int i = dr.FieldCount;//获取当前行中列数
10 while (dr.Read())//循环读取，没有下一行会跳出
11 {
12 for (int j = 0; j < i; j++)
13 {
14 str += dr.GetValue(j);
15 }
16 str += "<br/>";
17 }
18 Response.Write(str);
19 GridView1.DataSource = dr;
20 GridView1.DataBind();
21 conn.Close();
22 }
连接数据库：
1 B/S模式连接数据库：
2 string sql="Source=服务器Ip;database=数据库名称;UID=sa;Password=sasa;";
3 C/S模式连接数据库：
4 string sql="Data Source=服务器Ip;Initial Catalog=数据库名称;User ID=sa;password=sa";