ADO.NET(上)



usingsystem.Data.SqlClient;

 

一:调用SQL

(1):ExecuteNonquery() //执行增删改

string  conStr="server=(local);database=MyDB;uid = administrator;pwd=123456";

SqlConnection  conn =new SqlConnection(conStr);

string  sql ="insert  into usersTableValue('1001','aaa','123456')";//插入

sql ="delete usersTable where loginID='1001'";//删除

sql ="update usersTable set userName='bbb' where loginID='1001'";//更新

SqlCommand   cmd = new SqlCommand();

cmd.Connection=conn;

cmd.CommandText= sql;

con.Open();

int count=cmd.ExecuteNonquery();

conn.Close();

Console.WriteLine(count);

(2):ExecuteScalar() //执行查询，返回标量值

string  conStr="server=(local);database=MyDB;uid = administrator;pwd=123456";

SqlConnection  conn =new SqlConnection(conStr);

string  sql ="select count(*) from usersTable";

SqlCommand   cmd = new SqlCommand();

cmd.Connection=conn;

cmd.CommandText= sql;

conn.Open();

object  obj =cmd.ExecuteScalar();

int c =(int)obj;

Conn.Close();

Console.WriteLine(c);

(3):ExecuteReader() //执行查询，返回数据阅读器

string  conStr="server=(local);database=MyDB;uid = administrator;pwd=123456";

SqlConnection  conn =new SqlConnection(conStr);

string  sql ="select * from usersTable ";

SqlCommand   cmd = new SqlCommand();

cmd.Connection=conn;

cmd.CommandText= sql;

conn.Open();

SqlDataReader  sdr = cmd.ExecuteReader();

while(sdr.Read())

{

    int loginId = dr.GetInt(0);

    string userName = (string)dr[1];

    string pwd = (string)dr["password"];

   Console.WriteLine("{0}:{1},{2}",loginId,userName,pwd);

}

Conn.Close();

 

二：调用存储过程

createproc  Account_Proc(@userNameNVarchar(50),@userid NVarchar(50) output)

as

      select @userCount = loginId  from usersTable where  userName =@userName

go

(1):ExecuteNonquery()

string  conStr="server=(local);database=MyDB;uid = administrator;pwd=123456";

SqlConnection  conn =new SqlConnection(conStr);

SqlCommand   cmd = new SqlCommand("Account_Proc");

cmd.Connection=conn;

cmd.CommandType= System.Data.CommandType.StoredProcedure;

cmd.Parameters.Add("@userName",SqlDbType.NVarchar,50).Value= "aaa";

cmd.Parameters.Add("@userid",SqlDbType.Int).Direction=  ParameterDirection.Output;

conn.Open();

cmd.ExecuteNonquery();

conn.Close();

objectobj = cmd.Parameters["@userid"].Value;

if(obj!=DBNull.Value)

{

    string uid= (string)obj;

    Console.WriteLine(uid);

}

(2):ExecuteReader()

 

三：调用事务

string  conStr="server=(local);database=MyDB;uid = administrator;pwd=123456";

SqlConnection  conn =new SqlConnection(conStr);

string  sql1 ="update  usersTable set Account =Account-1000 whereloginId ="aaa";

string  sql2 ="update  usersTable set Account = Account+1000   whereloginId ="bbb";

stringsql3 ="insert into usersLogvalue("aaa","bbb",100)"

SqlCommand   cmd1 = new SqlCommand();

cmd1.CommandText= sql1;

SqlCommand   cmd2 = new SqlCommand();

cmd2.CommandText= sql2;

SqlCommand   cmd3 = new SqlCommand();

cmd3.CommandText= sql3;

cmd1.Connection= conn;

cmd2.Connection= conn;

cmd3.Connection= conn;

SqlTransaction  trans =null;

try

{

    conn.Open();

    trans = conn.BeginTransaction();

    cmd1.Transaction = trans;

    cmd2.Transaction = trans;

    cmd3.Transaction = trans;

    int a =cmd1.ExecuteNonquery();

    int b =cmd2.ExecuteNonquery();

    int c =cmd3.ExecuteNonquery();

   if(a+b+c==3)

   {

      trans.Commit();

      Console.WriteLine("转账成功");

   }

   else

   {

      trans.RollBack();

     Console.WriteLine("转账失败");

   }

}

catch(SqlExceptionex)

{

  if(trans==null)

  {

      trans.RollBack();

     Console.WriteLine(ex.Message);

  }

}

finally

{

   conn.Close();

}

 

四：预编译参数

string  username="aaa";

string  password ="123";

string  conStr="server=(local);database=MyDB;uid = administrator;pwd=123456";

SqlConnection  conn =new SqlConnection(conStr);

string  sql ="select count(*) from usersTablewhere username =@username and [password]=@password ";

SqlCommand   cmd = new SqlCommand();

cmd.Connection=conn;

cmd.CommandText= sql;

cmd.Parameters.Add(@username,SqlDbType.NVarchar,50).Value= username;

cmd.Parameters.Add(@password,SqlDbType.NVarchar,50).Value= password;

conn.Open();

SqlDataReader  sdr =cmd.ExecuteReader();

bool ok =sdr.HasRows;

if(ok)

{

     Message.Show("登陆成功");

}

else

{

    Message.Show("登陆失败");

}