C#练习DataReader(转载)
本文转自http://www.cnblogs.com/Mysterious/p/3422901.html
SQL代码:
create database ThreeDb go USE ThreeDb; GO CREATE TABLE classify --分类表 ( id int primary key identity(1,1), name nvarchar(20) not null ) GO CREATE TABLE product --产品表 ( id int primary key identity(1,1), name nvarchar(20) not null, price decimal, number int default 0, c_id int FOREIGN KEY references classify(id) ) GO CREATE TABLE users ( id int primary key identity(1,1), name nvarchar(20) not null, pwd nvarchar(20) not null ) GO --添加分类测试数据 insert into classify(name) values('图书'); insert into classify(name) values('家电'); insert into classify(name) values('服饰'); --添加users的测试数据 insert into users(name,pwd) values('admin','admin'); --添加商品测试数据 insert into product(name,price,number,c_id) values('arduino基础版',168,50,1); insert into product(name,price,number,c_id) values('arduino提高版',268,50,1); insert into product(name,price,number,c_id) values('arduino至尊版',468,50,1); insert into product(name,price,number,c_id) values('比基尼',68,50,3); insert into product(name,price,number,c_id) values('虎皮裙',168,50,3); insert into product(name,price,number,c_id) values('长靴',368,50,3); insert into product(name,price,number,c_id) values('电冰箱',3268,50,2); insert into product(name,price,number,c_id) values('烘干机',2268,50,2); GO select count(*) from users where name='admin' and pwd='admin' go select count(*) from users where name='xxxdadad' or 1=1 --' and pwd='admin' go select * from classify; go select * from product order by c_id; go --无返回值 --用途:删除一条记录 CREATE PROCEDURE UP_Product_Delete @id int AS DELETE [product] WHERE id=@id GO --用途:修改一条记录 CREATE PROCEDURE UP_Product_Update @id int, @name nvarchar(20), @price decimal(18,0), @number int, @c_id int AS UPDATE [product] SET [name]=@name,[price]=@price,[number]=@number,[c_id]=@c_id WHERE id=@id GO --有返回值 --用途:增加一条记录 CREATE PROCEDURE UP_Product_ADD @id int output,--这是返回数据 @name nvarchar(20), @price decimal(18,0), @number int, @c_id int AS INSERT INTO [product]([name],[price],[number],[c_id]) VALUES(@name,@price,@number,@c_id) SET @id= @@IDENTITY GO --用途:是否已经存在 CREATE PROCEDURE UP_Product_Exist @id int AS DECLARE @TempID int SELECT @TempID = count(1) FROM [product] WHERE id=@id IF @TempID = 0 RETURN 0 ELSE RETURN 1 GO --返回结果集 --用途:得到实体对象的详细信息 CREATE PROCEDURE UP_Product_GetModel @id int AS SELECT id,name,price,number FROM [product] WHERE id = @id GO --用途:查询记录信息 CREATE PROCEDURE UP_Product_GetList AS SELECT id,name,price,number,c_id FROM [Product] GO --ALTER 更新 --drop 删除
winform代码
Product.cs
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data.SqlClient; using System.Configuration; using System.Data; namespace ADO.NET.SQL { public class Product { string connstr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString; public Product() { } public Product(int _id,string _n,decimal _p,int _num,int _c_id) { ID = _id; Name = _n; Price = _p; Number = _num; C_id = _c_id; } public int ID { get; set; } public string Name { get; set; } public decimal Price { get; set; } public int Number { get; set; } public int C_id { get; set; } /// <summary> /// 增加一种商品 /// </summary> /// <param name="_n">名字</param> /// <param name="_p">价格</param> /// <param name="_num">库存</param> /// <param name="_c_id">所属分类</param> /// <returns>true、false</returns> public bool Add(string _n, decimal _p, int _num, int _c_id) { //using (SqlConnection conn = new SqlConnection(connstr)) //{ // string sql = "insert into product(name,price,number,c_id) values('"+_n+"',"+ _p +","+ _num +","+ _c_id + ")"; // SqlCommand cmd = new SqlCommand(sql, conn); // conn.Open(); // int i = cmd.ExecuteNonQuery(); // if (i > 0) // return true; // else // return false; //} using (SqlConnection conn = new SqlConnection(connstr)) { string sql = "UP_Product_ADD"; SqlCommand cmd = new SqlCommand(sql, conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@name", _n); cmd.Parameters.AddWithValue("@price", _p); cmd.Parameters.AddWithValue("@number", _num); cmd.Parameters.AddWithValue("@c_id", _c_id); SqlParameter pid = new SqlParameter("@id",0); pid.Direction = ParameterDirection.Output; cmd.Parameters.Add(pid); conn.Open(); int i = cmd.ExecuteNonQuery(); //System.Windows.Forms.MessageBox.Show(cmd.Parameters["@id"].Value.ToString()); if (i > 0) return true; else return false; } } public void Exists(int _id) { try { using (SqlConnection conn = new SqlConnection(connstr)) { string sql = "UP_Product_Exist"; SqlCommand cmd = new SqlCommand(sql, conn); conn.Open(); cmd.CommandType = CommandType.StoredProcedure; SqlParameter pid = new SqlParameter("@id", _id); pid.Direction = ParameterDirection.Input; cmd.Parameters.Add(pid); SqlParameter rv = new SqlParameter("@returnvalue", SqlDbType.Int, 4); rv.Direction = ParameterDirection.ReturnValue; cmd.Parameters.Add(rv); int i = cmd.ExecuteNonQuery(); string str1 = cmd.Parameters["@returnvalue"].Value.ToString(); if (str1 == "0") { System.Windows.Forms.MessageBox.Show("未查到相同的商品ID"); } else { System.Windows.Forms.MessageBox.Show("该商品ID已经存在"); } //if (i > 0) // return true; //else // return false; } } catch (Exception exp) { System.Windows.Forms.MessageBox.Show(exp.Message); } } /// <summary> /// 更新一种商品 /// </summary> /// <param name="_id">id</param> /// <param name="_n">名字</param> /// <param name="_p">价格</param> /// <param name="_num">库存</param> /// <param name="_c_id">所属分类</param> /// <returns>true、false</returns> public bool Update(int _id, string _n, decimal _p, int _num, int _c_id) { //using (SqlConnection conn = new SqlConnection(connstr)) //{ // string sql = "UPDATE product SET name='" + _n + "',price = " + _p + ",number=" + _num + ",c_id=" + _c_id + "WHERE id=" + _id; // SqlCommand cmd = new SqlCommand(sql, conn); // conn.Open(); // int i = cmd.ExecuteNonQuery(); // if (i > 0) // return true; // else // return false; //} using (SqlConnection conn = new SqlConnection(connstr)) { string sql = "UP_Product_Update"; SqlCommand cmd = new SqlCommand(sql, conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@id", _id); cmd.Parameters.AddWithValue("@name", _n); cmd.Parameters.AddWithValue("@price", _p); cmd.Parameters.AddWithValue("@number", _num); cmd.Parameters.AddWithValue("@c_id", _c_id); conn.Open(); int i = cmd.ExecuteNonQuery(); if (i > 0) return true; else return false; } } /// <summary> /// 移除一个 /// </summary> /// <param name="_id">id</param> /// <returns>true、false</returns> public bool Remove(int _id) { //使用SQL语句 //using (SqlConnection conn = new SqlConnection(connstr)) //{ // string sql = "delete from product WHERE id=" + _id; // SqlCommand cmd = new SqlCommand(sql, conn); // conn.Open(); // int i = cmd.ExecuteNonQuery(); // if (i > 0) // return true; // else // return false; //} //使用SQL方法 using (SqlConnection conn = new SqlConnection(connstr)) { string sql = "UP_Product_Delete"; SqlCommand cmd = new SqlCommand(sql, conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@id", _id); conn.Open(); int i = cmd.ExecuteNonQuery(); if (i > 0) return true; else return false; } } /// <summary> /// 获取商品列表 /// </summary> /// <returns>泛型</returns> public Dictionary<int, Product> GetAll() { //Dictionary<int, Product> plist = new Dictionary<int, Product>(); //using (SqlConnection conn = new SqlConnection(connstr)) //{ // string sql = "select * from product"; // SqlCommand cmd = new SqlCommand(sql, conn); // conn.Open(); // SqlDataReader sdr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection); // while (sdr.Read()) // { // Product p = new Product( // Convert.ToInt32(sdr["id"]), // sdr["name"].ToString(), // Convert.ToDecimal(sdr["price"]), // Convert.ToInt32(sdr["number"]), // Convert.ToInt32(sdr["c_id"]) // ); // plist.Add(Convert.ToInt32(sdr["id"]), p); // } // sdr.Close(); //} //return plist; Dictionary<int, Product> plist = new Dictionary<int, Product>(); using (SqlConnection conn = new SqlConnection(connstr)) { string sql = "UP_Product_GetList"; SqlCommand cmd = new SqlCommand(sql, conn); cmd.CommandType = CommandType.StoredProcedure; conn.Open(); SqlDataReader sdr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection); while (sdr.Read()) { Product p = new Product( Convert.ToInt32(sdr["id"]), sdr["name"].ToString(), Convert.ToDecimal(sdr["price"]), Convert.ToInt32(sdr["number"]), Convert.ToInt32(sdr["c_id"]) ); plist.Add(Convert.ToInt32(sdr["id"]), p); } sdr.Close(); } return plist; } } }
Classify.cs
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data.SqlClient; using System.Configuration; namespace ADO.NET.SQL { public class Classify { string connstr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString; public int ID { get; set; } public string Name { get; set; } /// <summary> /// 获取所有分类 /// </summary> /// <returns>分类</returns> public SqlDataReader GetAll() { SqlConnection conn = new SqlConnection(connstr); SqlCommand cmd = new SqlCommand("select * from classify", conn); conn.Open(); SqlDataReader sdr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection); //不推荐 return sdr; } /// <summary> /// 通过id获取名字 /// </summary> /// <param name="cid"></param> /// <returns></returns> public string GetCName(int cid) { using (SqlConnection conn = new SqlConnection(connstr)) { SqlCommand cmd = new SqlCommand("select name from classify where id=" + cid.ToString(), conn); conn.Open(); string str1 = cmd.ExecuteScalar().ToString(); return str1; } } /// <summary> /// 通过名字获取id /// </summary> /// <param name="name"></param> /// <returns></returns> public int GetCid(string name) { using (SqlConnection conn = new SqlConnection(connstr)) { SqlCommand cmd = new SqlCommand("select id from classify where name=" + name, conn); conn.Open(); return Convert.ToInt32(cmd.ExecuteScalar()); } } } }
Form1.cs
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; namespace ADO.NET.SQL { public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void Form1_Load(object sender, EventArgs e) { Bind(); } private void Bind() { listView1.Items.Clear(); Classify c = new Classify(); Product p = new Product(); ListViewItem lvi; foreach (Product pr in p.GetAll().Values) { lvi = new ListViewItem(pr.ID.ToString()); lvi.SubItems.Add(pr.Name); lvi.SubItems.Add(pr.Price.ToString()); lvi.SubItems.Add(pr.Number.ToString()); lvi.SubItems.Add(c.GetCName(pr.C_id)); listView1.Items.Add(lvi); } SqlDataReader sdr = c.GetAll(); while (sdr.Read()) { comboBox1.Items.Add(sdr[0] + "-->" + sdr[1]); } sdr.Close(); } private void listView1_SelectedIndexChanged(object sender, EventArgs e) { if (listView1.SelectedItems.Count != 0) { lbl_id.Text = listView1.SelectedItems[0].Text; tb_name.Text = listView1.SelectedItems[0].SubItems[1].Text; tb_price.Text = listView1.SelectedItems[0].SubItems[2].Text; tb_number.Text = listView1.SelectedItems[0].SubItems[3].Text; string cname = listView1.SelectedItems[0].SubItems[4].Text; for (int i = 0; i < comboBox1.Items.Count; i++) { int index = comboBox1.Items[i].ToString().IndexOf("-->") + 3;//得到箭头结束的位置 if (comboBox1.Items[i].ToString().Substring(index) == cname) { comboBox1.SelectedIndex = i; } } } } /// <summary> /// 修改 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void button2_Click(object sender, EventArgs e) { try { int id = int.Parse(lbl_id.Text); int index = comboBox1.Text.IndexOf("-->"); int cid = int.Parse(comboBox1.Text.Substring(0, index)); Product p = new Product(); if ( p.Update ( id, tb_name.Text, decimal.Parse(tb_price.Text), int.Parse(tb_number.Text), cid ) ) { Bind(); } else { MessageBox.Show("没修改成功哦!"); } } catch (Exception exp) { MessageBox.Show(exp.Message); } } /// <summary> /// 添加 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void button3_Click(object sender, EventArgs e) { try { Product p = new Product(); int index = comboBox1.Text.IndexOf("-->"); int cid = int.Parse(comboBox1.Text.Substring(0, index)); if ( p.Add ( tb_name.Text, decimal.Parse(tb_price.Text), int.Parse(tb_number.Text), cid ) ) { Bind(); } else { MessageBox.Show("没添加成功哦!"); } } catch(Exception exp) { MessageBox.Show(exp.Message); } } /// <summary> /// 删除商品 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void button1_Click(object sender, EventArgs e) { try { int id = int.Parse(lbl_id.Text); Product p = new Product(); if ( p.Remove(id) ) { Bind(); } else { MessageBox.Show("没删除成功哦!"); } } catch (Exception exp) { MessageBox.Show(exp.Message); } } private void button6_Click(object sender, EventArgs e) { try { Product p = new Product(); p.Exists(int.Parse(tb_Exist.Text)); } catch (Exception exp) { MessageBox.Show(exp.Message); } } } }
app.config
<?xml version="1.0" encoding="utf-8" ?> <configuration> <connectionStrings> <add name="constr" connectionString="server=QT-201303030913;database=ThreeDb;uid=sa;pwd=*******"/> </connectionStrings> </configuration>