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

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

代码

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

 

 

 

转载于:https://www.cnblogs.com/huazaizai/archive/2010/07/10/1774924.html