using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration;
using _17DateStr.DataSet1TableAdapters;
/* DataSet
* 每次读取的数据都创建连接，执行Command得到SqlDataReader太麻烦，让我们封装一个方法
*
* SqlDataReader对于小数据量的数据来说带来的只有麻烦，优点可以忽略不计
*
* SqlDataReader是连接相关的，SqlDataReader中的查庖结果并不是放到程序中的
* 而是放在数据库服务器中，SqlDataReader只是相当于放了一个指针(游标),只能读取当前游标指向的行
* 一旦连接开就不能再读取，这样做的好处就是无论查询结果有多少条，对程序占用的内存都几乎没有影响
* ADO.Net中提供了数据集的机制，将查询结果填充到本地内存中，这样连接断开，服务器断开都不影响数据的读取
* DataSet dataset = new DataSet();
* SqlDataAdapter adapter = new SqlDataAdapter(cmd); adapter.Fill(dataset);
*
* SqlDataAdapter是DataSet和数据库之间的沟通的桥梁,数据集DataSet包含若干表DataTable,DataTable包含若干行DataRow
* foreach(DataRow row in dataset.Tables[0].Rows){
* row["name"];
* }
*
*
* SQLHelper
* 封装一个SQLHelper类方便使用，提供ExecuteData Table(string sql, params SqlParameter[] parameters)
* ExecuteNonQuery(string sql, params SqlParameter[] parameters)
* ExecuteScalar(string sql, params SqlParameter[] parameters)
* 等方法，网上有自由软件提供的最全的SQLHelper类，是Enterprise Libarary 中的一部分
* 用Sqlhelper重写登陆程序
* 用SQLHelper重写省市选择程序, 电话地址归属地查询
* new SqlParameter("e",0)的陷阱
* sqlconnection在程序中一直保持它open可以吗?对于数据库来说，连接是非常宝贵的资源，一定要用完了就close display
*
*
* DataSet的更新
* 可以更新row["Name"] = "yzk"; 删除行datatable.Rows.Remove(),新增行datatable.NewRow(); 这一切都是修改的内存中的DataSet,没有修改数库
* 可以调用SqlDataAdpater的update方法将对DataSet的修改提交到数据库，Update方法有很多重载方法,可以提交整个DataSet,DataTable,或者若干DataRow
* 但是需要为sqlDataAdapter提供DeleteCommand, UpdateCommand InsertCommand它才知道如何将对DataSet的修改提交到数据库
* 由于这几个Command要求格式非常苛刻，因此开发人员自己写非常困难，可以用SqlCommandBuilder自动生成这几个command，用法很简单
* new SqlCommandBuilder(adapter) 查看生成的Command(没有直接赋值给SqlDataAdapter)
* 看SqlCommandBuilder的, sqlCommandBuilder要求表必须有主键
* (*)通过DataRow的RowState可以获得行的状态(删除,修改,新增等);调用DataSet的GetChanges()方法得到变化的结果集,降低传递的资源占用
*
*/
namespace _17DateStr
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
SqlHelp.ExecuteNonQuery("Insert into T_Persons(name,age) values(@name,@age)", new SqlParameter("name","tom"),new SqlParameter("age",11));
MessageBox.Show("插入成功!");
}
private void button2_Click(object sender, EventArgs e)
{
object i = SqlHelp.ExecuteScalar("select count(*) FROM T_Persons");
MessageBox.Show("记录总数为:"+Convert.ToString(i));
}
private void button3_Click(object sender, EventArgs e)
{
SqlDataReader reader = SqlHelp.ExecuteReader("Select * FROM T_Persons");
while (reader.Read())
{
string name = reader.GetString(reader.GetOrdinal("name"));
MessageBox.Show("用户名为:" + name);
}
}
private void button4_Click(object sender, EventArgs e)
{
/*string connstr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
using (SqlConnection conn = new SqlConnection(connstr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "select * FROM T_Persons";
DataSet dataset = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(dataset);
DataTable table = dataset.Tables[0];
for (int i = 0; i < table.Rows.Count; i++)
{
DataRow row = table.Rows[i];
string name = Convert.ToString(row["name"]);
MessageBox.Show(name);
}
}
}*/
DataSet dataset = new DataSet();
string connstr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
using (SqlConnection conn = new SqlConnection(connstr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "select * FROM T_Persons";
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(dataset);
}
}
DataTable table = dataset.Tables[0];
for (int i = 0; i < table.Rows.Count; i++)
{
DataRow row = table.Rows[i];
string name = Convert.ToString(row["name"]);
MessageBox.Show(name);
}
}
private void button5_Click(object sender, EventArgs e)
{
DataTable table = SqlHelp.ExecuteDataTable("select * FROM T_Persons");
foreach (DataRow row in table.Rows)
{
string name = Convert.ToString(row["name"]);
MessageBox.Show(name);
}
}
private void tb_login_Click(object sender, EventArgs e)
{
string name = tb_username.Text;
string password = tb_password.Text;
if (name != "" && password != "")
{
DataTable table = SqlHelp.ExecuteDataTable("select * FROM T_User where name=@name", new SqlParameter("name", name));
if (table.Rows.Count >= 1)
{
DataRow row = table.Rows[0];
int errorTimes =Convert.ToInt32(row["errortimes"]);
if(errorTimes >= 3)
{
MessageBox.Show("用户被锁定,暂时不能登陆!");
return;
}else{
string p = Convert.ToString(row["password"]);
if(p == password){
MessageBox.Show("登陆成功!");
SqlHelp.ExecuteNonQuery("update T_User set errortimes=0 where name=@name", new SqlParameter("name",name));
return;
}else{
MessageBox.Show("登陆失败!");
SqlHelp.ExecuteNonQuery("update T_User set errortimes = errortimes+1 where name = @name",new SqlParameter("name",name));
return;
}
}
}
else {
MessageBox.Show("用户名不存在!");
}
}
else {
MessageBox.Show("用户名和密码不能为空");
}
}
private void Form1_Load(object sender, EventArgs e)
{
DataTable table = SqlHelp.ExecuteDataTable("select * FROM promary");
cb_promary.DisplayMember = "name";
foreach (DataRow row in table.Rows)
{
Promary pr = new Promary();
pr.id = Convert.ToInt32(row["proID"]);
pr.name = Convert.ToString(row["proName"]);
cb_promary.Items.Add(pr);
}
}
private void cb_promary_SelectedIndexChanged(object sender, EventArgs e)
{
Promary pr = (Promary)cb_promary.SelectedItem;
int id = pr.id;
string name = pr.name;
DataTable table = SqlHelp.ExecuteDataTable("select * FROM city WHERE proID=@proID",new SqlParameter("proID",id));
cb_city.Items.Clear();
cb_city.DisplayMember = "name";
foreach (DataRow row in table.Rows)
{
Promary p = new Promary();
p.id = Convert.ToInt32(row["cityID"]);
p.name = Convert.ToString(row["cityName"]);
cb_city.Items.Add(p);
}
}
private void button6_Click(object sender, EventArgs e)
{
DataTable table = SqlHelp.ExecuteDataTable("select * FROM T_User WHERE id = @id",new SqlParameter("id",(object)0));
}
private void button7_Click(object sender, EventArgs e)
{
String connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "select * FROM T_Persons";
DataSet dataset = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(dataset);
DataTable table = dataset.Tables[0];
DataRow row = table.Rows[0];
row["name"] = "jason"; //修改值
foreach (DataRow r in table.Rows)
{
string name = Convert.ToString(r["name"]);
MessageBox.Show("name值为:"+name);
}
//table.Rows.RemoveAt(1); //删除值
//table.NewRow(); //新建一个Row
//这里的sqlCommandBuilder主要是生成更新语句
SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
//这里出现了小错误，没有为表设置主键，所以提示错误
//"update T_Persons set name=@name, password=@password"
adapter.Update(dataset);//对DataSet的修改都在内存中，没有提交到数据库
}
}
}
private void button8_Click(object sender, EventArgs e)
{
T_UserTableAdapter adapter = new T_UserTableAdapter();
_17DateStr.DataSet1.T_UserDataTable data = adapter.GetData();
for (int i = 0; i < data.Count; i++)
{
_17DateStr.DataSet1.T_UserRow userRow = data[i];
MessageBox.Show(userRow.name);
}
}
}
public class Promary
{
public int id { get; set; }
public string name { get; set; }
}
}