1.造DBConnection
做一个链接字符串的常量
2.造实体类
把数据库的表封装类,把表的字段都封状成成员变量和属性
3.造数据访问类
a.声明三个成员:SqlConnection,SqlCommand,SqlDataReader
b.在构造函数中,把SqlConnection和SqlCommand实例化。
c.做一系列的增、删、改、查的方法。
4.Main函数
(1)。
a.调用数据访问类,获得数据。
b.在界面上使用for显示出来。
(2)。
a.从界面上获得输入的数据,存在变量中。
b.调用数据访问类,把变量传进相应的增删改的方法中去,实现数据库相应操作。
数据库:
create table stock
(
ids varchar(10) primary key, --股票代号
name varchar(50) ,--股票名称
start decimal(18,2),--开盘价
stop decimal(18,2),--收盘价
trans bigint--成交量
)
go
insert into stock values('s001','济南轻骑',8.5,8.7,59874)
insert into stock values('s002','长江股份',7.2,7.0,35659)
insert into stock values('s003','浦发银行',6.5,7.1,32120)
insert into stock values('s004','山东铝业',3.5,3.1,36520)
insert into stock values('s005','铁龙股份',5.6,5.9,55460)
insert into stock values('s006','金牛股份',6.3,5.4,98630)
insert into stock values('s007','辽渔集团',12.1,11.4,36541)
insert into stock values('s008','中华电力',9.6,8.7,25698)
select * from stock
主函数:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace lian_fengzhuang
{
class Program
{
static void Main(string[] args)
{
Showmain();
Console.ReadLine();
}
public static void Showmain()
{
while (true)
{
Console.WriteLine("1.显示表中所有数据");
Console.WriteLine("2.添加数据");
Console.WriteLine("3.删除相应编号数据");
Console.WriteLine("4.修改相应编号数据");
Console.WriteLine("5.退出");
Console.Write("请输入您的选择:");
string shu = Console.ReadLine();
if (shu == "1")
{
new Caozuo().CzShow();
}
else if (shu == "2")
{
new Caozuo().CzInsert();
}
else if (shu == "3")
{
new Caozuo().CzDelete();
}
else if (shu == "4")
{
new Caozuo().CzUpdate();
}
else if (shu == "5")
{
break;
}
else
{
Console.WriteLine("请认真查看,重新输入!");
Showmain();
}
}
}//循环显示要求
}
}
主函数类下的调用:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace lian_fengzhuang
{
class Caozuo
{
public void CzInsert()
{
Console.WriteLine("请输入要插入的数据:");
Console.Write("股票代号:");
string ids = Console.ReadLine();
string ke = Czduancun(ids);
if (ke == "no")
{
Console.Write("股票名称:");
string name = Console.ReadLine();
Console.Write("开盘价:");
decimal start = Convert.ToDecimal(Console.ReadLine());
Console.Write("收盘价:");
decimal stop = Convert.ToDecimal(Console.ReadLine());
Console.Write("成交量:");
int trans = Convert.ToInt32(Console.ReadLine());
new StockDA().Insert(ids, name, start, stop, trans);
}
else { Console.WriteLine("您输入的股票代码已存在!"); }
}//插入数据操作
public void CzDelete()
{
Console.WriteLine("请输入要删除数据的编号:");
Console.Write("股票代号:");
string ids = Console.ReadLine();
string ke = Czduancun(ids);
if (ke == "you")
{
new StockDA().Delete(ids);
}
else { Console.WriteLine("您输入的股票代号不存在!"); }
}//删除数据操作
public void CzUpdate()
{
Console.WriteLine("请输入要修改的数据:");
Console.Write("股票代号:");
string ids = Console.ReadLine();
string ke = Czduancun(ids);
if (ke == "you")
{
Console.Write("股票名称:");
string name = Console.ReadLine();
Console.Write("开盘价:");
decimal start = Convert.ToDecimal(Console.ReadLine());
Console.Write("收盘价:");
decimal stop = Convert.ToDecimal(Console.ReadLine());
Console.Write("成交量:");
int trans = Convert.ToInt32(Console.ReadLine());
new StockDA().Update(ids, name, start, stop, trans);
}
else { Console.WriteLine("您输入的股票代号不存在!"); }
}//修改数据操作
public void CzShow()
{
List<Stock> show = new StockDA().Select();
for (int i = 0; i < show.Count; i++)
{
Stock st = show[i];
Console.Write(st.Ids + "\t");
Console.Write(st.Name + "\t");
Console.Write(st.Start + "\t");
Console.Write(st.Stop + "\t");
Console.Write(st.Trans + "\n");
}
}//显示数据操作
public string Czduancun(string ids)
{
string ok = "";
Stock st=new StockDA().Select(ids);
if (st == null)
{
ok = "no";
}
else { ok = "you"; }
return ok;
}//判断是否存在相应数据
}
}
实例化:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
namespace lian_fengzhuang
{
class StockDA
{
private SqlConnection _Con;
private SqlCommand _Cmd;
private SqlDataReader _Dr;
public StockDA()
{
_Con=new SqlConnection(DBConnection.LIANSQL);
_Cmd = _Con.CreateCommand();
}//构造函数
public void Delete(string ids)
{
_Cmd.CommandText = "delete from stock where ids=@ids";
_Cmd.Parameters.Clear();
_Cmd.Parameters.AddWithValue("@ids",ids);
try
{
_Con.Open();
_Cmd.ExecuteNonQuery();
}
finally
{
_Con.Close();
}
}//根据相应的id删除数据
public void Insert(string ids, string name, decimal start, decimal stop, int trans)
{
_Cmd.CommandText = "insert into stock values (@ids,@name,@start,@stop,@trans)";
_Cmd.Parameters.Clear();
_Cmd.Parameters.AddWithValue("@ids",ids);
_Cmd.Parameters.AddWithValue("@name", name);
_Cmd.Parameters.AddWithValue("@start", start);
_Cmd.Parameters.AddWithValue("@stop", stop);
_Cmd.Parameters.AddWithValue("@trans", trans);
try
{
_Con.Open();
_Cmd.ExecuteNonQuery();
}
finally
{
_Con.Close();
}
}
//插入数据
public void Update(string ids, string name, decimal start, decimal stop, int trans)
{
_Cmd.CommandText = "update stock set name=@name,start=@start,stop=@stop,trans=@trans where ids=@ids";
_Cmd.Parameters.Clear();
_Cmd.Parameters.AddWithValue("@ids", ids);
_Cmd.Parameters.AddWithValue("@name", name);
_Cmd.Parameters.AddWithValue("@start", start);
_Cmd.Parameters.AddWithValue("@stop", stop);
_Cmd.Parameters.AddWithValue("@trans", trans);
try
{
_Con.Open();
_Cmd.ExecuteNonQuery();
}
finally
{
_Con.Close();
}
}
//根据相应的id,修改相应的数据
public List<Stock> Select()
{
List<Stock> list=new List<Stock>();
_Cmd.CommandText = "select * from stock";
try
{
_Con.Open();
_Dr = _Cmd.ExecuteReader();
while (_Dr.Read())
{
Stock st = new Stock();
st.Ids = _Dr["Ids"].ToString();
st.Name = _Dr["name"].ToString();
st.Start = (decimal)_Dr["start"];
st.Stop=(decimal)_Dr["stop"];
st.Trans=Convert.ToInt32( _Dr["trans"]);
list.Add(st);
}
}
finally
{
_Con.Close();
}
return list;
}//查询表中所有数据
public Stock Select(string ids)
{
_Cmd.CommandText = "select * from stock where ids=@ids";
_Cmd.Parameters.Clear();
_Cmd.Parameters.AddWithValue("@ids",ids);
try
{
_Con.Open();
_Dr=_Cmd.ExecuteReader();
if (_Dr.Read())
{
Stock st = new Stock();
st.Ids = _Dr["ids"].ToString();
st.Name = _Dr["name"].ToString();
st.Start = (decimal)_Dr["start"];
st.Stop = (decimal)_Dr["stop"];
st.Trans = (int)_Dr["trans"];
return st;
}
else
{ return null; }
}
finally
{
_Con.Close();
}
}//根据ids查询相应信息
}
}
工具类:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace lian_fengzhuang
{
class Stock
{
private string _Ids;
public string Ids
{
get { return _Ids; }
set { _Ids = value; }
}
private string _Name;
public string Name
{
get { return _Name; }
set { _Name = value; }
}
private decimal _Start;
public decimal Start
{
get { return _Start; }
set { _Start = value; }
}
private decimal _Stop;
public decimal Stop
{
get { return _Stop; }
set { _Stop = value; }
}
private int _Trans;
public int Trans
{
get { return _Trans; }
set { _Trans = value; }
}
}
}
链接字符串:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace lian_fengzhuang
{
class DBConnection
{
public const string LIANSQL = "server=.;database=lian;uid=sa;pwd=123";
}
}