【经典】C#常用数据库操作（增删改查）

OStack程序员社区-中国程序员成长平台 › 门户 › 编程› c#›C#教程

原作者: [db:作者] 来自: [db:来源] 收藏邀请

最近整理了一下常用的数据库操作，希望以后有用。

代码


 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";