常用SQL语句 

 1 1.链接数据库
 2 using System.Data;
 3 using System.Data.SqlClient;
 4 
 5     private static string str_Connect = @"Data Source=DESKTOP-ICQTSAF;Database=db_MyQPlay;User ID=sa;pwd=123456";
 6     public static SqlConnection connection = new SqlConnection(str_Connect);
 7 2.查询
 8 （1）
 9     sql = "select SCOPE_IDENTITY() from tb_User";
10     command = new SqlCommand(sql, DataOperator.connection);
11     QPlayNum = Convert.ToInt32(command.ExecuteScalar());
12     
13 （2）
14     string sql=@"Select Count(*) From tb_User Where ID="+id+" And Pwd='"+pwd+"'";
15     int num = dataOpe.ExecSQL(sql);
16 
17 （3）    
18     string sql = "Select pwd,remember,autologin from tb_User Where ID=" + id;
19 
20     DataSet ds = dataOpe.GetDataSet(sql);
21     if(ds.Tables[0].Rows.Count>0)
22     {
23         if(Convert.ToInt32(ds.Tables[0].Rows[0][1])==1)
24         {
25             ck_Remember.Checked = true;
26             tb_Pwd.Text = ds.Tables[0].Rows[0][0].ToString();
27     
28             if(Convert.ToInt32(ds.Tables[0].Rows[0][2])==1)
29             {
30                 ck_Auto.Checked = true;
31                 pic_Login_Click(sender, e);
32             }
33         }
34     }
35         
36 3.插入
37 （1）
38 string sql = string.Format("insert into tb_User (Pwd, NickName, Sex, Age, Name, Star, BloodType) " +
39             "Values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}');select @@Identity from tb_User",
40             tb_Pwd.Text.Trim(),tb_NickName.Text.Trim(),sex,tb_Age.Text.Trim(),tb_Name.Text.Trim(),cbb_Star.Text,cbb_BloodType.Text);
41             
42     SqlCommand command = new SqlCommand(sql, DataOperator.connection);
43     DataOperator.connection.Open();
44     int result = command.ExecuteNonQuery();
45     
46 4.其他
47 class DataOperator
48     {
49         private static string str_Connect = @"Data Source=DESKTOP-ICQTSAF;Database=db_MyQPlay;User ID=sa;pwd=123456";
50         public static SqlConnection connection = new SqlConnection(str_Connect);
51 
52         public int ExecSQL(string sql)  //仅返回第一行第一列元素。
53         {
54             SqlCommand command = new SqlCommand(sql, connection);
55             if(connection.State==ConnectionState.Closed)
56             {
57                 connection.Open();
58             }
59             int num = Convert.ToInt32(command.ExecuteScalar());
60             connection.Close();
61             return num;
62         }
63 
64         public int ExecSQLResult(string sql)    //返回受影响的行数
65         {
66             SqlCommand command = new SqlCommand(sql, connection);
67             if (connection.State == ConnectionState.Closed)
68             {
69                 connection.Open();
70             }
71             int result = Convert.ToInt32(command.ExecuteNonQuery());
72             connection.Close();
73             return result;
74         }
75 
76         public DataSet GetDataSet(string sql)
77         {
78             SqlDataAdapter sqlDA = new SqlDataAdapter(sql, connection);
79             DataSet ds = new DataSet();
80             sqlDA.Fill(ds);
81             return ds;
82         }     
83     }
View Code

 

posted @ 2022-10-08 16:30  冲云霄  阅读(1)  评论(0)    收藏  举报