ado.net 访问数据库

方法一

 1 public List<User_Info> GetUserByLocation(string Phone)
 2         {
 3             //string conString = @"Data Source=WIN-OPGK9FBG0MOXHYANG;Initial Catalog=xhyang;Integrated Security=True";
 4             string sql = @"select * from User_Info where Phone=@phone";
 5             SqlParameter[] parms = new SqlParameter[]
 6             {
 7                 new SqlParameter("@phone",Phone),
 8 
 9             };
10             List<User_Info> linq = new List<User_Info>();
11             
12 
13             using (SqlConnection con = new SqlConnection(conString))
14             {
15                 using (SqlCommand command = new SqlCommand(sql, con))
16                 {
17                     if (command.Connection.State == System.Data.ConnectionState.Closed)
18                     {
19                         command.Connection.Open();
20                     }
21 
22                     command.CommandType = System.Data.CommandType.Text;
23                     //command.CommandText = sql;
24                     command.Parameters.AddRange(parms);
25                     DataTable dt = new DataTable();
26                     SqlDataAdapter sda = new SqlDataAdapter((SqlCommand)command);
27                     sda.Fill(dt);
28                     command.Connection.Close();
29                     sda.Dispose();
30                     foreach (DataRow dr in dt.Rows)
31                     {
32                         User_Info user = new User_Info();
33                         user.UserInfoId = int.Parse(dr["UserInfoID"].ToString());
34                         user.UserName = dr["UserName"].ToString();
35                         user.Phone = dr["Phone"].ToString();
36                         linq.Add(user);
37                     }
38                 }
39             }
40             return linq;
41         }

方法二

2.01

 1 public void UpUserName(string UserName, string NewUserName,string Phone)
 2         {
 3             
 4             DataTable dt = new DataTable();
 5             System.Data.SqlClient.SqlConnection cnn = new System.Data.SqlClient.SqlConnection(conString);
 6             System.Data.SqlClient.SqlCommand cm = new System.Data.SqlClient.SqlCommand();
 7             cm.Connection = cnn;
 8             cnn.Open();
 9             System.Data.SqlClient.SqlTransaction trans = cnn.BeginTransaction();
10             try
11             {
12                 cm.Transaction = trans;
13                 cm.CommandText = "update User_Info set UserName ='user01' where UserName='user1'";
14                 cm.ExecuteNonQuery();
15                 //下面的是需要执行的错误的sql语句
16                 cm.CommandText = "update User_Info set UserNamesss ='user02' where UserName='user 2'";
17                 cm.ExecuteNonQuery();
18                 trans.Commit();
19             }
20             catch
21             {
22                 trans.Rollback();
23             }
24             finally
25             {
26                 cnn.Close();
27                 trans.Dispose();
28                 cnn.Dispose();
29             }  
30         }

2.02

 1 public void UpUserName(string UserName, string NewUserName)
 2         {
 3 
 4             DataTable dt = new DataTable();
 5             System.Data.SqlClient.SqlConnection cnn = new System.Data.SqlClient.SqlConnection(conString);
 6             System.Data.SqlClient.SqlCommand cm = new System.Data.SqlClient.SqlCommand();
 7             cm.Connection = cnn;
 8             cnn.Open();
 9             System.Data.SqlClient.SqlTransaction trans = cnn.BeginTransaction();
10             try
11             {
12                 cm.Transaction = trans;
13                 for (int i = 0; i < 2; i++)
14                 {
15                     string sql = "update User_Info set UserName =  @am" + i + " where UserName = @pr" + i;
16                     if (i > 1)
17                     {
18                         sql = "update User_Info set UserNamess =  @am" + i + " where UserName = @pr" + i;
19                     }
20                     cm.Transaction = trans;
21                     cm.CommandText = sql;
22                     //cm.Parameters.Add("@am", SqlDbType.Int);
23                     cm.Parameters.Add("@am" + i, SqlDbType.VarChar);
24                     cm.Parameters["@am" + i].Value = NewUserName;
25                     cm.Parameters.Add("@pr" + i, SqlDbType.VarChar);
26 
27                     cm.Parameters["@pr" + i].Value = UserName;
28                     cm.ExecuteNonQuery();
29                 }
30                 //foreach (DataRow dr in dt.Rows)
31                 //{
32                 //    cm.Transaction = trans;
33                 //    cm.CommandText = "update User_Info set UserName =  @am where UserName = @pr";
34                 //    //cm.Parameters.Add("@am", SqlDbType.Int);
35                 //    cm.Parameters.Add("@am", SqlDbType.VarChar);
36                 //    cm.Parameters["@am"].Value = NewUserName;
37                 //    cm.Parameters.Add("@pr", SqlDbType.VarChar);
38                 //    cm.Parameters["@pr"].Value = UserName;
39                 //    cm.ExecuteNonQuery();
40                 //}
41                 trans.Commit();
42             }
43             catch
44             {
45                 trans.Rollback();
46             }
47             finally
48             {
49                 cnn.Close();
50                 trans.Dispose();
51                 cnn.Dispose();
52             }
53         }