一。数据库的增删改 “查”
1.数据库相关
sql:
–创建表
create table A
(
id int Primary key not null,
name varchar(20),
sex char(2)
)
–sa
–
–视图 VIEW
create view v_emp
as
select top 1 drcode,drname from pdrug (nolock)
select * from emp where empid=‘1000’
union
select * from pdruglast
select * from pdrug
union all
select * from pdruglast
–事务
–存储过程 proc
alter proc p_aa
@id varchar(20)
as
select * from emp where empid=@id
exec p_aa ‘1000’
–查询 --非聚集索引
select * from pdrug where len(name) like ‘’ and id=’’
–曾
insert into emp select * from emp_bak where empid=‘1000’
–删
select * from emp where empid=‘1000’
–delete emp where empid=‘1000’
–改
update emp set name=’’ where empid=‘1000’
获取新数据插入的id序号
insert into Fruit output inserted.fruit_id values(‘西红柿’,‘大十字啊’,3,4);
2查
insert into Employee(emp_name,emp_gender,emp_age,emp_mobile,emp_address)
values(‘顾康’,‘男’,‘21’,‘111111’,‘杭州市’)
SELECT * FROM Employee
–,为了性能,往往会在表后面加一个nolock,或者是with(nolock),其目的就是查询是不锁定表,从而达到提高查询速度的目的。
select * from Fruit(nolock)
UPDATE Fruit set Fruit_name=‘荔枝’ where fruit_id=5
UPDATE Fruit set Fruit_aliasname=‘龙眼’ where fruit_id=5
DELETE FROM Employee WHERE emp_name=‘why’
INSERT INTO Fruit(fruit_name,fruit_aliasname,fruit_purchaseprice,fruit_retailprice)
VALUES(‘大西瓜’,‘西瓜’,‘2.00’,‘4.00’)
insert into Saleslog(fruit_id,s_quantity,fruit_purchaseprice,fruit_retailprice,s_amoney,emp_id,s_date)
VALUES(‘5’,‘2.00’,‘4.00’,‘8.00’,‘4.00’,‘5’,GETDATE())
SELECT * FROM Fruit_name
select * from Fruit inner join Saleslog on Fruit.fruit_id = Saleslog.s_id
–交叉连接
select * from Fruit cross join Saleslog
select * from Saleslog(nolock)
insert into Saleslog(fruit_id,s_quantity,fruit_purchaseprice,fruit_retailprice,s_amoney,emp_id,s_date)
VALUES(‘7’,‘2.00’,‘4.00’,‘8.00’,‘4.00’,‘5’,CONVERT(DATETIME, ‘1980-12-17’, 120))
练习
创建表:水果买卖的流程
创建Employee,Fruit,Saleslog表,然后写入数据,然后增删改查,做关联,左关联,内关联
select s.[s_id],s.[fruit_id],f.[fruit_name],s.[s_quantity],s.[fruit_purchaseprice],s.[fruit_retailprice],
s.[s_amoney],s.[emp_id],isnull(e.[emp_name],‘查无此人’) as ‘销售员’,s.[s_date]
from
SalesLog s left join employee e on s.[emp_id] = e.[emp_id]
left join Fruit f on s.[fruit_id] = f.[fruit_id]
select s.[s_id],f.[fruit_name],s.[s_quantity],s.[fruit_purchaseprice],s.[fruit_retailprice],
s.[s_amoney],e.[emp_name],s.[s_date]
from
SalesLog s,employee e,Fruit f
where s.[emp_id] = e.[emp_id]
and s.[fruit_id] = f.[fruit_id]
select * from saleslog inner join
employee on saleslog.emp_id = employee.emp_id
select * from employee
select isnull(e.[emp_name],‘查无此人’) as ‘销售员’,‘销售额’=sum(s.[s_amoney])
from
SalesLog s left join employee e on s.[emp_id] = e.[emp_id]
left join Fruit f on s.[fruit_id] = f.[fruit_id]
group by s.[emp_id],e.[emp_name]
select Saleslog.s_amoney,Fruit.fruit_name,Saleslog.s_date from Saleslog left join Fruit on Saleslog.fruit_id=Fruit.fruit_id where Saleslog.s_date>'2020-07-21’and Saleslog.s_date<‘2020-07-23’
select isnull(e.[emp_name],‘查无此人’) as ‘销售员’,‘销售额’=sum(s.[s_amoney])
from
SalesLog s left join employee e on s.[emp_id] = e.[emp_id]
left join Fruit f on s.[fruit_id] = f.[fruit_id]
group by s.[emp_id],e.[emp_name]
select isnull(s.[emp_id],‘查无此人’) as ‘销售员’,‘销售额’=sum(s.[s_amoney])
from
SalesLog s
group by s.[emp_id]
select isnull(s.[fruit_id],‘查无此人’) as ‘水果编号’,‘销售额’=sum(s.[s_amoney])
from
SalesLog s
group by s.[fruit_id]
select * from Saleslog where fruit_id=‘17’
–引用有索引
select isnull(s.[fruit_id],‘查无此人’) as ‘水果编号’,‘销售额’=sum(s.[s_amoney]),‘销售单数’=count(s.[emp_id])
from
SalesLog s
group by s.[fruit_id]
select isnull(s.[fruit_id],‘查无此人’) as ‘水果编号’,‘销售额’=sum(s.[s_amoney]),‘销售单数’=count(s.[s_amoney])
from
SalesLog s
group by s.[fruit_id]
二。winfrom实现增删功能
1连接数据库
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
```
<!--数据库连接字符串-->
<add key="ConnectionString" value="Data Source=.;Initial Catalog=Fruitshop;Integrated Security=True;" />
```
</appSettings>
<connectionStrings>
<!--<add name="contant" connectionString="Data Source=192.168.1.49;Initial Catalog=FruitShop;User Id=sa;Password=enjoyor;"/>-->
</connectionStrings>
</configuration>
//本地数据库和远程数据库
string connStr = “Data Source=.;Initial Catalog=Class;Integrated Security=True;”;
Data Source=192.168.1.14;Initial Catalog=FruitShop;User Id=sa;Password=123445;"
##### 2集合基础应用
```c#
private void Form1_Load(object sender,EventArgs e){
//将TClass表中的数据读取到一个List<T>中
LoadData();
}
private void LoadData()
{
List<TClass> list = new List<TClass>();
using (SqlConnection conn = new SqlConnection(connStr))
{
string sql = "select * from Person";
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
conn.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
while(reader.Read()){
TClass model = new TClass();
model.TClassId = reader.GetInt32(0);
model.TClassName = reader.GetString(1);
model.TClassAge = reader.GetInt32(2);
model.TClassHeight = reader.GetInt32(3);
list.Add(model);//把model对象加到list集合中
}
}
}
}
}
//TClass实例
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace WindowsFormsApplication2
{
public class TClass
{
public int TClassId{get;set;}
public string TClassName{get;set;}
public int TClassAge{get;set;}
public int TClassHeight{get;set;}
public string 属性 { get { return "测试"; } }
}
}
3三层架构
.NET 在winform三层架构:
1.表现层 =》业务逻辑层和模型
2.业务逻辑层(Bussiness) =》数据访问层和模型
3.数据访问层(DBUtility) =》添加模型的引用
4.模型实例模型(Model)
三,杂的知识点(属性方法的使用)
//隐藏显示
this.Hide();
水果商店 dt = new 水果商店();
dt.Show();
//判断是否为空
if (comboBox1.Text.Trim() == String.Empty || textBox2.Text.Trim() == String.Empty)
{
return;
}
// dataGridView点击控件拿出来进行值的填充
private void dataGridView1_RowEnter(object sender, DataGridViewCellEventArgs e)
{
if (dataGridView1.CurrentRow == null) return;
DataGridViewRow currentRow = this.dataGridView1.Rows[e.RowIndex];
textBox1.Text = currentRow.Cells[1].Value.ToString();
label6.Text = currentRow.Cells[3].Value.ToString();
label13.Text = currentRow.Cells["fruit_id"].Value.ToString();
label18.Text = currentRow.Cells["fruit_id"].Value.ToString();
label14.Text = currentRow.Cells["fruit_purchaseprice"].Value.ToString();
textBox1.ReadOnly = true;
}
//Dataset数据集,判断查询返回值值是否为空
string id=textBox3.Text;
string sql = @"SELECT * FROM Fruit WHERE fruit_name=@id";
SqlParameter[] paras =
{
new SqlParameter("@id",id),
};
DataTable table = new DataTable();
DataSet ds = DBUtility.DbHelperSQL.Query(sql, paras);
if (ds.Tables.Count == 1 && ds.Tables[0].Rows.Count == 0)
{
MessageBox.Show("抱歉,该水果还没进货--敬请期待!!!!");
return;
}
else
{
table = ds.Tables[0];
}
dataGridView1.DataSource = table;
//清除dataGridView该行的值
private void button2_Click_1(object sender, EventArgs e)
{
int row = dataGridView2.CurrentRow.Index;
dataGridView2.Rows.RemoveAt(row);
}
------------------把获取的值填充到dataGridView中-----------
private void button5_Click(object sender, EventArgs e)
{
if (comboBox1.Text.Trim() == String.Empty || label4.Text.Trim() == String.Empty)
{
MessageBox.Show("斤数和员工编号不能为空!!!");
return;
}
int index = this.dataGridView2.Rows.Add();
this.dataGridView2.Rows[index].Cells[0].Value = comboBox1.Text;//员工编号
this.dataGridView2.Rows[index].Cells[1].Value = label13.Text;//水果id
this.dataGridView2.Rows[index].Cells[2].Value = label14.Text;//进价
this.dataGridView2.Rows[index].Cells[3].Value = label6.Text;//售价
this.dataGridView2.Rows[index].Cells[4].Value = label4.Text; //总额
this.dataGridView2.Rows[index].Cells[5].Value = textBox2.Text.Trim();//数量
this.dataGridView2.Rows[index].Cells[6].Value = textBox1.Text.Trim();//水果名
-------------------进行datagridview数据遍历取值进行累加---------
double sum = 0;
for (int i = 0; i < dataGridView2.Rows.Count; i++)
{
sum += Convert.ToDouble(dataGridView2.Rows[i].Cells[4].Value);
}
label16.Text = sum.ToString();
}
##### 1.判断DataGridview是否为空,然后进行取值
```csharp
private void button1_Click(object sender, EventArgs e)
{
bool t = false;
//缺个逻辑直接购买,判断是否为空;空不执行;
----------------遍历dataGridView的列,进行取值写数据库---------------
foreach (DataGridViewRow data in dataGridView2.Rows)
{
if (data.Cells["Column1"].Value == null)
{
break;
}
else {
string emp_id = data.Cells[0].Value.ToString();
string fruit_id = data.Cells[1].Value.ToString();
string fruit_pur = data.Cells[2].Value.ToString();
string ret = data.Cells[3].Value.ToString();
string s_amoney = data.Cells[4].Value.ToString();
string s_quan = data.Cells[5].Value.ToString();
DateTime dt = DateTime.Now;
Model.Saleslog sale = new Model.Saleslog();
sale.emp_id = Convert.ToInt32(emp_id);
sale.fruit_id = Convert.ToInt16(fruit_id);
sale.fruit_purchaseprice = Convert.ToDecimal(fruit_pur);
sale.fruit_retailprice = Convert.ToDecimal(ret);
sale.s_amoney = Convert.ToDecimal(s_amoney);
sale.s_quantity = Convert.ToDecimal(s_quan);
sale.s_date = Convert.ToDateTime(dt.ToString());
bool flag = new Bussiness.busi_Saleslog().Add(sale);
t = flag;
}
}
if (t)
{
MessageBox.Show("购买成功");
this.Hide();
水果商店 ye = new 水果商店();
ye.Show();
}
else
{
MessageBox.Show("购买失败");
}
Emp1_Load();
}
2.引用正则表达式进行数据类型判断
`
```csharp
----------------进行年月日格式判断----------
public static bool ValidateDataTime(string InputStr)
{
if (InputStr.Length > 0)
{
if (Regex.IsMatch(InputStr.Trim(), @"^((((1[6-9]|[2-9]\d)\d{2})-(0?[13578]|1[02])-(0?[1-9]|[12]\d|3[01]))|(((1[6-9]|[2-9]\d)\d{2})-(0?[13456789]|1[012])-(0?[1-9]|[12]\d|30))|(((1[6-9]|[2-9]\d)\d{2})-0?2-(0?[1-9]|1\d|2[0-8]))|(((1[6-9]|[2-9]\d)(0[48]|[2468][048]|[13579][26])|((16|[2468][048]|[3579][26])00))-0?2-29-))$"))
{
return true;
}
else
{
return false;
}
}
return false;
}
private void button1_Click(object sender, EventArgs e)
{
string text = textBox1.Text.ToString();
bool x = ValidateDataTime(text);
if (x || Regex.IsMatch(text, "^[\u4E00-\u9FA5]+$"))------------进行不是数字和汉字的判断-------------
{
if (x)
{
------把输入的数据填充进行数据库的查询--------------------------------------
string sql = @"select Saleslog.s_amoney,Fruit.fruit_name,Saleslog.s_date from Saleslog left join Fruit on Saleslog.fruit_id=Fruit.fruit_id where convert(varchar(10),s_date,120) =@date";
SqlParameter[] paras = new SqlParameter[] {
new SqlParameter("@date",text)};
DataTable table = new DataTable();
DataSet ds = DBUtility.DbHelperSQL.Query(sql, paras);
if (ds.Tables.Count == 1 && ds.Tables[0].Rows.Count == 0)
{
MessageBox.Show("该日期没有销售记录");
return;
}
else
{
table = ds.Tables[0];
}
dataGridView3.DataSource = table;
}
------把输入的数据填充进行数据库的查询--------------------------------------
else
{
string sql = @"select Employee.emp_name,Saleslog.s_amoney,Saleslog.s_date from Employee left join Saleslog on Employee.emp_id=Saleslog.emp_id where Employee.emp_name=@name";
SqlParameter[] paras = new SqlParameter[] {
new SqlParameter("@name",text)};
DataTable table = new DataTable();
DataSet ds = DBUtility.DbHelperSQL.Query(sql, paras);
if (ds.Tables.Count == 1 && ds.Tables[0].Rows.Count == 0)
{
MessageBox.Show("请核对信息,查无此人");
return;
}
else {
table = ds.Tables[0];
}
string dt = table.Rows[0]["s_amoney"].ToString();
if (dt.Length != 0)
{
dataGridView2.DataSource = table;
}
else {
table.Clear();
dataGridView2.DataSource = table;
MessageBox.Show("今天还没开张,要加油哦");
}
}
}
else
{
MessageBox.Show("格式不符合");
}
}`