ado.net 访问数据库

方法一

public List<User_Info> GetUserByLocation(string Phone)
        {
            //string conString = @"Data Source=WIN-OPGK9FBG0MOXHYANG;Initial Catalog=xhyang;Integrated Security=True";
            string sql = @"select * from User_Info where Phone=@phone";
            SqlParameter[] parms = new SqlParameter[]
            {
                new SqlParameter("@phone",Phone),

            };
            List<User_Info> linq = new List<User_Info>();
            

            using (SqlConnection con = new SqlConnection(conString))
            {
                using (SqlCommand command = new SqlCommand(sql, con))
                {
                    if (command.Connection.State == System.Data.ConnectionState.Closed)
                    {
                        command.Connection.Open();
                    }

                    command.CommandType = System.Data.CommandType.Text;
                    //command.CommandText = sql;
                    command.Parameters.AddRange(parms);
                    DataTable dt = new DataTable();
                    SqlDataAdapter sda = new SqlDataAdapter((SqlCommand)command);
                    sda.Fill(dt);
                    command.Connection.Close();
                    sda.Dispose();
                    foreach (DataRow dr in dt.Rows)
                    {
                        User_Info user = new User_Info();
                        user.UserInfoId = int.Parse(dr["UserInfoID"].ToString());
                        user.UserName = dr["UserName"].ToString();
                        user.Phone = dr["Phone"].ToString();
                        linq.Add(user);
                    }
                }
            }
            return linq;
        }

方法二

2.01

public void UpUserName(string UserName, string NewUserName,string Phone)
        {
            
            DataTable dt = new DataTable();
            System.Data.SqlClient.SqlConnection cnn = new System.Data.SqlClient.SqlConnection(conString);
            System.Data.SqlClient.SqlCommand cm = new System.Data.SqlClient.SqlCommand();
            cm.Connection = cnn;
            cnn.Open();
            System.Data.SqlClient.SqlTransaction trans = cnn.BeginTransaction();
            try
            {
                cm.Transaction = trans;
                cm.CommandText = "update User_Info set UserName ='user01' where UserName='user1'";
                cm.ExecuteNonQuery();
                //下面的是需要执行的错误的sql语句
                cm.CommandText = "update User_Info set UserNamesss ='user02' where UserName='user 2'";
                cm.ExecuteNonQuery();
                trans.Commit();
            }
            catch
            {
                trans.Rollback();
            }
            finally
            {
                cnn.Close();
                trans.Dispose();
                cnn.Dispose();
            }  
        }

2.02

public void UpUserName(string UserName, string NewUserName)
        {

            DataTable dt = new DataTable();
            System.Data.SqlClient.SqlConnection cnn = new System.Data.SqlClient.SqlConnection(conString);
            System.Data.SqlClient.SqlCommand cm = new System.Data.SqlClient.SqlCommand();
            cm.Connection = cnn;
            cnn.Open();
            System.Data.SqlClient.SqlTransaction trans = cnn.BeginTransaction();
            try
            {
                cm.Transaction = trans;
                for (int i = 0; i < 2; i++)
                {
                    string sql = "update User_Info set UserName =  @am" + i + " where UserName = @pr" + i;
                    if (i > 1)
                    {
                        sql = "update User_Info set UserNamess =  @am" + i + " where UserName = @pr" + i;
                    }
                    cm.Transaction = trans;
                    cm.CommandText = sql;
                    //cm.Parameters.Add("@am", SqlDbType.Int);
                    cm.Parameters.Add("@am" + i, SqlDbType.VarChar);
                    cm.Parameters["@am" + i].Value = NewUserName;
                    cm.Parameters.Add("@pr" + i, SqlDbType.VarChar);

                    cm.Parameters["@pr" + i].Value = UserName;
                    cm.ExecuteNonQuery();
                }
                //foreach (DataRow dr in dt.Rows)
                //{
                //    cm.Transaction = trans;
                //    cm.CommandText = "update User_Info set UserName =  @am where UserName = @pr";
                //    //cm.Parameters.Add("@am", SqlDbType.Int);
                //    cm.Parameters.Add("@am", SqlDbType.VarChar);
                //    cm.Parameters["@am"].Value = NewUserName;
                //    cm.Parameters.Add("@pr", SqlDbType.VarChar);
                //    cm.Parameters["@pr"].Value = UserName;
                //    cm.ExecuteNonQuery();
                //}
                trans.Commit();
            }
            catch
            {
                trans.Rollback();
            }
            finally
            {
                cnn.Close();
                trans.Dispose();
                cnn.Dispose();
            }
        }