1.ExcuteNonQuery

  执行非查询语句,返回受影响的行数。

            // 1.ExcuteNonQuery

             string sqlconn = "Data Source=wss;Initial Catalog=TextDB;User ID=sa;Password=w778764;Integrated Security=true";
SqlConnection conn = new SqlConnection(sqlconn);
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "insert into TB_USER(userID,password,LastTime,code) values ('fungame','131313',2005-05-23,56)";
int num = cmd.ExecuteNonQuery();
conn.Close();
conn.Dispose();
Console.Write("(共有{0}行受影响",num.ToString());
Console.ReadKey();

2.ExcuteScalar

  执行查询语句,返回一行一列的数据。

             .ExcuteScaler
string sqlconn= "data source = wss;uid = sa;pwd = w778764;Integrated Security = true;Initial Catalog = TextDB;";
string sql = "select * from TB_USER ";
using (SqlConnection conn = new SqlConnection (sqlconn))
{
using (SqlCommand cmd = new SqlCommand(sql,conn))
{
conn.Open();
string str = cmd.ExecuteScalar().ToString();
Console.WriteLine(str);
conn.Close();
}
}
Console.ReadKey();

3.参数化查询(防止SQL注入<1' or '1' = '1>)

             //3.防止SQL注入,参数化查询
Console.WriteLine("请输入用户名:");
string uid =Console.ReadLine();
Console.WriteLine("请输入密码");
string pwd = Console.ReadLine(); string sqlconn = "data source = wss;uid = @uid;pwd = @pwd;Integrated Security = true;Initial Catalog = TextDB;";
string sqldtr = "select count(*) from TB_USER;";
SqlParameter spuid = new SqlParameter(@uid, uid);//构建参数化对象
SqlParameter sppwd = new SqlParameter(@pwd, pwd); using (SqlConnection conn = new SqlConnection(sqlconn))
{
using (SqlCommand cmd = new SqlCommand(sqldtr,conn))
{ cmd.Parameters.Add(spuid);//将参数对象添加入执行列表
cmd.Parameters.Add(sppwd);
conn.Open();
int num =(int) cmd.ExecuteScalar();
conn.Close();
Console.Write("n={0}", num);
}
}

4.sqldatareader

  执行查询语句,将数据暂存到sqldatareader对象中,可以通过索引将数据逐行取出。

             .sqldatareader使用
string sqlstr = "data source = wss;uid =@uid;pwd =@pwd;Initial Catalog = TextDB;Integrated Security = true;";
string sql = "select * from TB_USER;";
string uid = "";
string pwd = "";
SqlParameter suid = new SqlParameter(@uid, uid);
SqlParameter spwd = new SqlParameter(@pwd, pwd);
using (SqlConnection conn = new SqlConnection (sqlstr))
{
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.Parameters.Add(suid);
cmd.Parameters.Add(spwd);
conn.Open();
SqlDataReader sqlread = cmd.ExecuteReader();
using (sqlread)
{
while(sqlread.Read())
{
for (int i = ; i < sqlread.FieldCount; i++)
{
string data = sqlread[i].ToString();
Console.Write(data+"\t");
}
Console.WriteLine();
}
}
conn.Close();
}
}

5.SqlDataAdapter

  通过 DataAdapter 使用数据源中的数据生成和填充 DataSet 中的每个 DataTable,可以通过索引将单个的datatable数据绑定到数据容器中显示,如:datagridview。

  该实例中是建立了winform程序,设计视图中添加一个gridview和按钮控件,在按钮单击事件中添加如下代码。tips:girdview属性中的Anchor(锚;抛锚停泊)可以设定控件的四个方向是否跟随窗口大小改动而改动。

private void button1_Click(object sender, EventArgs e)
{
string sqlstr = "data source = wss;uid =@uid;pwd =@pwd;Initial Catalog = TextDB;Integrated Security = true;";
string sql = "select * from TB_USER;";
DataSet ds = new DataSet();
using (SqlDataAdapter sda = new SqlDataAdapter(sql,sqlstr))
{
sda.Fill(ds);
}
dataGridView1.DataSource = ds.Tables[];
}

如果这些内容帮助到你了,希望您给予支持 o(^▽^)o (支付宝)

ADO.NET基础学习-----四种模型,防止SQL注入-LMLPHP(支付宝)

04-16 03:21