首页 > 代码库 > 跟着杨中科循序渐进学习wpf（全）
跟着杨中科循序渐进学习wpf（全）
第一季 C#编程基础
1.用C#编写一个10+20=?的小程序：
public static voidMain(tring[] args)
{
int i1=10;
int i2=20;
int i3=i1+i2;
Console.WriteLine(i3); //也可用占位符来实现：Console.WriteLine("{0}+{1}={2}",i1,i2,i1+i2);在输出参数较多时候要用占位符
Console.ReadKey(); //注意MS-VS中快捷键ctrl+j的使用
}
2.MS-VS中显示代码行号的步骤：工具>选项>文本编辑器>c#>显示>行号。
3.转义字符
Ⅰ.c#中转义符“\”的用法：Response.Write(@"c:\...\...\abc.txt")或者是Response.Write("c:\\...\\...\\abc.txt")。@只对转义符起作用，不能够解决字符串中双引号的输出问题；后者比较麻烦，一般不用。
eg:在屏幕上打印“ab”
string s="\"an\"";
Console.Write(s);
Ⅱ.转义符和内存存储无关：
eg:string s=Console.ReadLine(); //Console.WriteLine()返回的内容永远是字符串类型
Console.WriteLine(s);
运行后在屏幕中输入a\nb打印出来的是a\nb；而不是a回车换行b。
Ⅲ：int i=Convert.ToInt32(Console.ReadLine()) //这种写法是正确的，只是没有创建一个容器来存放数据
4.换行符
换行符“\n”:Response.Write("a\nb"). //转义符只针对在代码中直接写出的字符串，对于程序中读取出来的无效，此处输出为a换行b
5.变量
变量：变量可以看做是专门放数据的容器，变量在定义的时候不可以重名，以保证数据的安全性和准确性。
（不表示不能重名eg:{int a =5;}int a =5;定义的变量只要所属方法或类不同就可以同样正常运行）。----作用域
命名规则：第一个字母必须是字母或者是下划线，其后的字符可以是任意的数字，字母或者是下划线。不能让变量名全部使用c#的关键字做变量名。
eg:int int1=5; //这样写是正确的
//c#关键字判定方式：VS中用蓝色显示的字符就都是c#关键字；变量的命名规则和函数，类是一样的
//c#中可以用中文做变量名|函数名|类名
6.变量类型
变量类型：int,char,string,bool,double(了解),byte(了解),decimal(了解),long(了解),float(了解,在数据库中一般工资的数据类型要用到这个)。
7.’a’与”a”
‘a‘是char类型；‘‘a‘‘是string类型。字符串可以看作是多个字符组成的。
eg: string s1=”abcd”; 则s1.length为4不等于5；原因在于与C语言中不一样，不包括结束”\0结束符
s.Length来获得字符串中字符的个数，字符串的不可变性，只能读不能写，即不能被赋值，一旦声明不能被改变，若要改变，需要重新声明
eg:
string s1=”hello”;
char[]chars=s1.ToCharArray();//复制s1为chars
chars[0]=’a’;
string s2=new string(chars);
Console.WrieLine(s2);
输出结果为aello;
其处理过程：字符串—字符串数组—改变数组元素值—得到新的字符串
注：
string s1=”hello”;
string s10=s1;
//s10指向s1指向的字符串，不是s10指向s1，哪怕s1以后指向了其他内存，s10指向也不会变
8.类型转换
类型转换convert:Convert.ToString(),Convert.ToInt32(),ToString().
类型转换cast
int i1=0;
int i2=i1;
若”=”右边的类型与左边的类型不一致时
或：
int MaxValue,intMinValue;
byte b=3;
int i1=b;
b=(byte)i1;//强制转换
*只有在内存存储上存在交集的类型之间才能进行cast，否则不可以比如不可以：
int I;
string s=(string)I;//反之也不可以；这种情况必须用convert转换
9.运算符表达式
●+，-，*，/(除)，%(求余)
●+可以用作字符串连接，其他不可以
●++自增；--自减
●运算符优先级：i1+i2*i3与(i1+i2)*i3;不要变态，括号是王道
●变量可以和字面量混合运算：eg:i1=i2+20
补充：一.可变参数函数（C#）
声明：
static void Vfunc(params string[]values)
{
Foreach(string s in values)
{
Console.WriteLine(s);
}
}
注：可变参数数组必须是最后一个参数
int Sun(stringname,params int[]values);
二．函数重载（函数名可以重复，但参数（类型或者个数）必须不一致，能否重载取决于参数，与返回值无关）
10.赋值运算：i+=2;//i=i+2
(*) += , -= , *= , /= ;
11.int x,y=10,0;
x+y=80; //这样写是错误的，因为左边的必须是变量！！不能是常量也不能是表达式
Console.Write(y);
12.Console.Write("{0}",i=1); //c#中赋值表达式也有值，他的值表示赋值后变量的值
Console.Write("{0}",i=1); 与Console.Write("{0}",i==1);的区别。
13.if语句：
int age=20
if(age>20)
{ Console.Write("成年人") }
else if(age>12)
{ Console.Write("儿童") }
else
{ Console.Write("婴幼儿") } //if,else关键字后面只有一行代码时候可以把｛｝省略掉。但关键字后的第二行代码不和关键字有任何关系
//顾if语句只有一行代码最好也加上大括号
14.if语句及语句的嵌套：
eg1:提示用户输入年龄，如果大于等于18，则告知用户可以查看，如果小于10岁，则告知不允许查看，如果大于等于10岁，则提示用户是否继续查看（yes,no)如果输入的是yes则提示用户可以查看，否则提示不可以查看。（测试边界条件，-1，8888888888888888888888888.微软如狼似虎的Tester）。代码如下：
namespace test
{
class Program
{
static void Main(string[] args)
{
Console.WriteLine("请输入您的年纪：");
string strage = Console.ReadLine();
int age =Convert.ToInt32(strage);
if (age>18)
{
Console.WriteLine("您可以继续查看！");
}
else if(age< 10)
{
Console.WriteLine("很抱歉，您不能继续查看！");
}
else
{
Console.WriteLine("您的年纪未满18岁，回复yes则继续查看，回复no则退出！");
if (Console.ReadLine() == "yes")
{
Console.WriteLine("您可以继续查看！");
}
else if (Console.ReadLine() == "no")
{
Console.WriteLine("乖！");
}
else
{
Console.WriteLine("您所输入的错误！");
}
}
Console.ReadKey();
}
}
}
eg2:依次提示用户输入两个整数（假设i1,i2）。如果i1,i2都是正数，则将i1的值递增一个数，然后打印i1+i2的值；如果i1,i2都是负数，则将i1的值递减10个数，然后打印i1*i2的值；如果i1,i2中任一个为0，则提示数据有错误，否则计算i1*i2的绝对值。代码如下：
namespace test
{
class Program
{
static void Main(string[] args)
{
Console.WriteLine("请输入一个整数：");
string stri1= Console.ReadLine();
int i1 =Convert.ToInt32(stri1);
Console.WriteLine("请再次输入一个整数：");
string stri2= Console.ReadLine();
int i2 =Convert.ToInt32(stri2);
if (i1 >0 && i2 > 0)
{
i1 = i1+1;
Console.WriteLine("{0}+{1}={2}",i1,i2,i1+i2);
}
else if (i1< 0 && i2 < 0)
{
i1 = i1 - 10;
Console.WriteLine("{0}*{1}={2}",i1,i2,i1*i2);
}
else if (i1== 0 || i2 == 0)
{
Console.WriteLine("数据有误，请从新输入：");
}
else
{
int i3=i1*i2;
if (i3 < 0)
{
i3 = -i3;
}
Console.WriteLine(i3);
}
Console.ReadKey();
}
}
}
15.switch case 语句
被判定的值进入满足条件的分支执行；case中的值必须是常量，不能是变量，表达式。
●类似于if...else...elseif...else,但是是离散值的判断。
●switch一般都可以用if重写，但是if不一定能用switch重写。
●不要忘了break，c#中的break不写是不行的；除了合并case的情况。
eg:代码如下：（PS:杨老师举的这个例子很幽默啊，这种精神也是我佩服的地方，寓教于乐！）
int i = 2; //修改i的值以达到测试switch...case...语句功能特点的效果
switch (i)
{
case 2: //相当于if(i==2)
Console.WriteLine("你真2！");
Console.WriteLine("你才2！");
break; //c#中必须写break，switch语句的每个分支都要有一个break,在C中虽然可以省略但会造成BUG
case 4:
Console.WriteLine("死去吧！");
break;
case 8:
Console.WriteLine("发发发！");
break;
default: //相当于if语句的else
Console.WriteLine("你输入的{0}没有意义!",i);
break;
}
Console.ReadKey();
●当几个逻辑处理单元处理内容相同的时候可以将逻辑处理单元的内容合并起来。
eg：int i = 2;
switch (i)
{
case 2: //case后面只能跟常量：如“2”，“aaa”;不能跟变量
Console.WriteLine("你真2！");
Console.WriteLine("你才2！");
break;
case8:
Console.WriteLine("发发发！");
break;
case 10:
case 20: //相当于if(i==10||i==20),这里是唯一一个case后面不用break的情况
Console.WriteLine("你输入的是整钱！");
Console.WriteLine("大爷！你真有钱！");
break;
default:
Console.WriteLine("你输入的{0}没有意义!",i);
break;
}
Console.ReadKey();
16.while循环
●while(i<100)
｛
//打印i
i++; //只要while后小括号中的表达式为true,就不断一遍一遍执行大括号中的代码，就好像是一个死循环
｝
●（*）do //先做。。。如果满足则再来一次，直至while表达式为false.括号中代码至少被执行一次。
{ //很少用的一种语法
//打印i
i++;
}
while(i<100)
eg1: 用while计算1到100之间的整数和；(其实有更好的解法)
int i1=0;
int sum=0;
while(i<=100)
{
sum+=i1;
i1++;
}
Console.WriteLine("从0到100所有数的和为{0}",sum);
eg2:要求用户输入用户名和密码，只要不是admin,888888就一直提示要求重新输入。
eg3:不断要求用户输入一个数字，然后打印这个数字的二倍，当用户输入q的时候程序退出。 
eg4:不断要求用户输入一个数字，当用户输入end的时候显示刚才输入的数字中的最大值。 
16.eg1:用while计算1到100之间的整数和。代码如下：
inti=1; //在VS控制台下运行
int result=0;
while(1<=100)
{
result=result+i;
i++;
}
Console.WriteLine(result);
Console.ReadKey();
eg2:要求用户输入用户名密码，只要不是admin和888888，就一直提示要求重新输入。代码如下：
Console.WriteLine("请输入用户名：");
string user=Console.ReadLine();
Console.WriteLine("请输入密码：");
string pwd =Console.ReadLine();
while (user!= "admin" || pwd !="888888") //注意字符串的写法，"admin"
{
Console.WriteLine("您输入的用户名或密码错误，请重新输入：");
Console.WriteLine("请重新输入用户名：");
user= Console.ReadLine();
Console.WriteLine("请重新输入密码：");
pwd= Console.ReadLine();
}
Console.WriteLine("欢迎登录！");
Console.ReadKey();
eg3:不断要求用户输入一个数字，然后打印这个数字的二倍，当
用户输入q的时候程序退出（return）。代码如下：
while(true)
{
Console.WriteLine("请输入一个数字");
string strinput = Console.ReadLine();
if (strinput == "q") //if语句的位置很重要，如果把if语句放在后面就会出现BUG，原因是"q"不能被转换为int型
{
return; //执行到return的时候就退出整个函数（Main）,while也就不会继再继续执行下去了，而控制台程序一旦Main退出程序也就退出了
}
int input = Convert.ToInt32(strinput);
Console.WriteLine("所输入的数字的两倍是：{0}", input * 2);
}
Console.ReadKey();
eg4:不断要求用户输入一个数字（假定用户输入的都是正整数），当用户输入end的时候显示刚才输入的数字中的最大值。设一个变量int max,初始值为0，用户每输入一次就把用户输入的和max比较一下，如果输入的必max大，则让max等于用户输入。代码如下：
int max = 0;
while(true)
{
Console.WriteLine("请输入一个正整数(结束输入end)：");
string input = Console.ReadLine();
if (input == "end")
{
Console.WriteLine("所输入的最大值为{0}", max); //注意if语句的位置和Console.ReadKey()；+return;的位置
Console.ReadKey();
return;
}
int num = Convert.ToInt32(input);
if (num > max)
{
max = num;
}
}
17.循环的中断
●break：立即终止整个循环。 //与return区别
●continue：立即终止当前循环步骤，进行下一次循环步骤。 //终止本次循环
eg:
int i = 0;
while (i < 15)
{
Console.WriteLine("i={0}", i);
i++;
if (i == 10)
{
break; //终止while循环，继续while后面的代码
//continue; //终止while本次循环，继续while下一次循环
//return; //终止当前执行的函数，后面的代码不会再执行
}
Console.WriteLine("自增以后i={0}",i);
}
Console.WriteLine("before ReadKey");
Console.ReadKey();
? 练习1：用while continue 实现计算 1 到 100 之间的除了能被7 整除之外所有整数的和。
? 练习2：用while break 实现要求用户输入用户名和密码，只要不是admin、888888就一直提示要求重新输入。
? 练习3：编写聊天机器人，如果问“今天天气怎么样？”则回答天气 ，如果问 ……，如果说“88”，则“再见” 。（switch-case）
.........待续
18.foreach循环
string[] names ={"tom","jerry","lily"};
foreach(string name in names) //string name声明一个变量名
{
Console.WriteLine("我的名字是：{0}",name);
}
foreach和for的区别：for可以不逐个遍历，比如每隔一个遍历一个，或者从后往前遍历。
19. 函数：
● 函数就是将一堆代码进行重用的一种机制。函数就是一段代码，这段代码可能有输入的值（参数），可能会返回值。一个函数就像一个专门做这件事的人，我们调用她来做一些事情，它可能需要我们提供一些数据给它，它执行完成后可能会有一些执行结果给我。要求的数据就叫参数，返回的执行结果就叫返回值。
● Console.ReadLine就是一个有返回结果的函数；
Console.WriteLine("HELLO")就是一个有执行参数的函数，只有告诉WriteLine被打印的数据它才知道如何打印；
int i = Convert.ToInt32("22")则是一个既有参数又有返回值的函数。
● 有了函数写代码就像拼积木，c#中的各种各样的技术其实就是通过for,if等这些基础的语法将不同的函数按照一定的逻辑组织起来。
eg:自己写函数：
1.读取输入的整数，定义成函数，多次调用：
Static int ReadInt()
{
String s = Convert.ReadLine();
return Convert.ToInt32(s);
}
写程序测试，程序调用到函数的时候是进入函数内部执行的，执行完毕再继续向下执行。
static void Main(string[] args)
{
int i = ReadInt();
i++;
Console.WriteLine(i);
}
2.函数如果没有返回值则标记返回值类型为void;
static void SayHello()
{
Console.Write("HelloWord~!");
return;
}
3.return语句:导致函数立即返回。在返回值为非void的函数中return,在返回值非void的函数中return数值。
static int Call(string name)
{
if(name=="tom")
{ return 1 ; }
if(name=="jok")
{ return 0 ; }
return -1 ;
}
Static string ReadIt()
{
string name = Console.ReadLine();
return name ;
}
20.函数参数：
eg:
1.计算两个整数中的最大值：int Max(int i1 , inti2)
static void Main(string[] args)
{
int a = Max(50,97);
Console.WriteLine(a);
Console.ReadKey();
}
Static int Max(int i1,int i2)
{
if(i1>i2)
{return i1 ; }
return i2; //此处else可以省略
}
2.计算输入数组的和：int Sum(int[] values)
static void Main(string[] args)
{
int[] values = {4,5,6,7,8};
intsum = Sum(values);
Console.WriteLine(sum);
Console.ReadLine();
}
staticint Sum(int[] values)
{
inta = 0;
for(int i=0;i<values.length;i++)
{
a= a + values[i];
}
returna;
//上面语句也可写作：
//int a = 0;
//foreach(int i in values)
//{
// a = a + i ;
//}
//return a;
//
}
3.确定函数参数的原则：自己能确定的数据自己内部解决，自己确定不了的数据通过参数传递。
4.string[]strs={"aa","435","sdf","tre"}返回给我一个字符串，然后字符串使用我指定的分隔符来进行分割，比如我指定用“|”分割，那么返回给我
“aa|435|sdf|tre”。
static void Main(string[] args)
{
string[] strs={"aa","435","sdf","tre"};
Console.WriteLine(Test(strs,"|"));
Console.ReadKey();
}
static string Test(string[] strs,string seperator)
{
string abc = "" ;
for(int i=0;i<strs.length-1;i++)
{
abc =abc + strs[i] + seperator ;
}
abc = abc + strs[strs.(length-1)] ;
return abc ;
}
21.可变参数：
1.参数数组：int sum(params int[] values) ; int sum(string name,params int[] values)
● 可变参数数组必须是最后一个
Console.Write()函数的参数即为可变参数，其参数可以无限制的增加。
eg:
static void Main(string[] args)
{
SayHello("Jim","Lily","Jone","Luky","无休止的可变参数");
Console.ReadKey();
}
static void SayHello(string name,params string[] nicknames)
{
Console.WriteLine("我的名字是：{0}",name);
foreach (string nickname in nicknames)
{
Console.WriteLine("我的昵称是：{0}",nickname) ;
}
}
2.参数默认值（.NET4.0）:void
SayHello(string name,int age=20) (*了解) //是用函数重载实现的
22.函数重载：（函数重名）
1.函数的重名：
eg: 1,3编写错误；2,4编写正确。
1.static void SayHello(string name)
{
Console.WriteLine("我是{0}",name);
}
Static void SayHello(string name)
{
Console.WriteLine("i am{0}",name);
}
2.static void SayHello(string name)
{
Console.WriteLine("我是{0}",name);
}
static void SayHello(int age)
{
Console.WriteLine("我的年纪是{0}",age);
}
3.static void SayHello(string name)
{
Console.WriteLine("我是{0},name");
}
staticint SayHello(string name)
{
return 10;
}
4.static void SayHello(string name)
{
Console.WriteLine("我是{0}",name);
}
static void SayHello(string name,string nickname)
{
Console.WriteLine("我是{0},昵称是{1}",name,nickname);
}
● 构成重载的条件：参数的类型不同或参数的个数不同才能构成重载（不严谨），与返回值无关。
23.字符串的处理：
● c#中单个字符用单引号包含就是char类型，eg:"a",单引号中方且只能放一个字符。
● 单个字符也可以表示为字符串，还可以有长度为0的字符串。
● 使用s.Length属性来获得字符串中的字符的个数。//在c语言中字符串长度要在C#基础上加1，因为要考虑‘\0‘。eg:"abc"C#中长度为3位，在C中长度为4位。
● string可以看作是char的只读数组。char c = s[1];
eg：遍历输出string中的每个元素：
static void Main(string[] args)
{
string[] abc = {"233", "adf", "sgdfg", "rer","345" };
EachString(abc);
Console.ReadKey();
}
static void EachString(string[]strs)
{
foreach(string str in strs)
{
Console.WriteLine("此为字符串数组中的元素:{0}", str);
}
}
● c#中字符串有一个重要的特性：不可变性，字符串一旦声明就不再可以改变。所以只能通过索引来读取指定位置的char，不能对指定位置的char进行修改。
● 如果要对char进行修改，那么就必须创建一个新的字符串，用s.ToCharArray()方法得到字符串的char数组，对数组进行修改后，调用new string(char[])这个构造函数（暂时不用细研究）来创建char数组的字符串。一旦字符串被创建，那么char数组的修改也不会造成字符串的变化。
eg:将字符串中的A替换为a：
static void Main(string[] args)
{
string str ="BAnAnA";
Console.WriteLine(ConvertStr(str));
Console.ReadLine();
}
static string ConvertStr(string str)
{
char[] acs =str.ToCharArray();
for(inti=0;i<acs.Length;i++)
{
if (acs[i]==‘A‘)
{
acs[i]=‘a‘;
}
}
string chars = new string(acs);
return chars ;
}
字符串→字符数组→改变部分元素值→新字符串
● 谁说字符串不可变？string s = "abc";s="123",s这不是变了吗
● 要区分变量名和变量指向的值的区别。程序中可以有很多字符串，然后由字符串变量指向他们，变量可以指向其他的字符串，但是字符串本身没有变化。字符串不可变性指的是内存中的字符串不可变，而不是变量不可变。
eg:string s1="hello";
string s10=s1;//s10指向s1指向的字符串，而不是s10指向s1,哪怕s1以后指向了其他内存，那么s10还是指向“hello”
char[] chars=s1.ToCharArray();
char[0]=‘a‘;
s1=new string(chars);
Console.WriteLine(s1);
Console.WriteLine(s10);
Console>ReadKey();
24.String类常用函数：
● ToLower():得到字符串的小写形式。
eg:String s = "Hello" ;
string s1 = s.ToLower();//ToLower方法是返回一个小写形式的返回值
//s = s.ToLower(); //并不是改变了字符串的内容，而是生成了一个新的全部变为小写的字符串，然后用s指向这个新的字符串
Console.WriteLine(s);
Console.WriteLine(s1);
Console.WriteLine(s1.ToUpper());
Console.ReadKey();
● 注意字符串是不可变的，所以这些函数都不会直接改变字符串的内容，二是把修改后的字符串的值通过函数返回值的形式返回。s.ToLower()与s=s.ToLower()
● ToUpper():得到字符串的大写形式：
Trim():去掉字符串两端的空白。//字符串中间的空白它不管
● s1.Equals(s2,StringComparison.OrdinalIgnoreCase),两个字符串进行比区分大小写的比较。//Ingore:忽略。Case:大小写返回值为bool类型（true,false）
eg:boll b ="abc".Equals("ABC",StringComparison.OrdinalIgnoreCase)
Console.WriteLine(b); //结果为True
//==是区分大小写的比较，Equals("ABC",StringComparison.OrdinalIgnoreCase)是忽略大小写的比较
● int i = "abc".CompareTo("123");//CompareTo()方法(较少用)对两个字符串进行比较，如果前者比后者大，则返回一个大于0的数，如果前者比后者小则返回一个小于0的数，如果相等则返回0；
25.字符串的分割：
● string[] Split(params char[] separator):将字符串按照指定的分隔符分割为字符串数组；
eg: string s1 = "aaa,bbb,ccc,ddd.eee.fff.ggg|hhh|iii";
string[] strs =s1.Split(‘,‘,‘.‘,‘|‘);
foreach(string item in strs)
{
Console.WriteLine(item);
}
Console.ReadKey();
● string[] Split(char[] separator,StringSplitOptions options)将字符串按照指定的char分割符分割为字符串数组（options 取RemoveEmptyEntries的时候移除结果中的空白字符串）；
eg:string s1 = "aa,bb,,cc,,12,34";
string[]strs = s1.Split(newchar[]{‘,‘},StringSplitOptions.RemoveEmptyEntries);
foreach(string item in strs)
{
Console.WriteLine(item);
}
Console.ReadKey();
● string[] split(string[] separator,StringSplitOptions options)将字符串按照指定的string分隔符分割为字符串数组；
eg:string s1 = "我是孔明我是韩非子我是孔老二";
string[]strs = s1.Split(newstring[]{"我是"},StringSplitOptions.RemoveEmptyEntries);
foreach(string item in strs)
{
Console.WriteLine(item);
}
Console.ReadKey();
eg1:从日期字符串（“2008-08-08”）中分析出年，月，日；
static void Main(string[]args)
{
string s = "2008-08-08";
Console.WriteLine("年份是：{0};月份是：{1};{2}日。",ConvertStr(s));
Console.ReadKey();
}
static string[] ConvertStr(string str)
{
string[] strs = str.Split(‘-‘);
return strs;
}
eg2:从一个记录了学生成绩的文本文档，每个学生成绩是一行，每行是用|分割的数据，用|分割的域分别是姓名，年龄，成绩，写程序取出成绩最高学生的姓名和成绩。
参考：使用string[]lines=System.IO.File.ReadAllLines(@"c:\root.ini",Encoding.Default);从文本文件读取数据，返回值为string数组，每个元素是一行。
string[] txtlinesstrs =System.IO.File.ReadAllLines(@"d:\123\123.txt", Encoding.Default);
string[] strs = {};
intsum = 0;
foreach(string linesstr in txtlinesstrs )
{
strs = linesstr.Split(‘|‘);
Console.WriteLine("各位同学的成绩分别是：{0}", strs[2]);
sum = sum + Convert.ToInt32(strs[2]);
}
Console.WriteLine("共{0}位同学；同学们的平均成绩是{1}。",txtlinesstrs.Length,sum/ (txtlinesstrs.Length));
Console.ReadKey();
26.字符串函数详解：
● 字符串替换：string Replace(string oldValue,string newValue)将字符串中的出现oldValue的地方替换为newValue.
eg:名字替换
string s = "李时珍同志是一个好同志，是一个好医生，向李时珍同志学习";
s = s.Replace("李时珍","李素丽");
Console.WriteLine(s);
Console.ReadKey();
● 取子字符串：string Subtring(int startIndex),取从位置startIndex开始一直到最后的子字符串
● string Substring(int startindex,int length),取从位置startIndex开始长度为length的子字符串，如果子字符串的长度不足length则报错。
eg:string s = "http://www.baidu.com";
string yuming = s.Substring(7);//从指定序号开始一直到最后的子字符串
string str = s.Substring(7,5);//SubString第二个参数指的是截取多长，而不是结束位置;如果length参数超过了长度就会报错
Console.WriteLine(yuming);
Console.WriteLine(str);
Console.ReadKey();
● bool Contains(strng value):判断字符串中是否含有子串value
eg: string s = "我们的社会真和谐啊";
if(s.Contains("社会")||s.Contains("和谐"))
{
Console.WriteLine("含有敏感词汇，请文明用语！");
}
Console.ReadKey();
● bool StartsWith(stringvalue):判断字符串是否以字串value开始
● bool EndsWith(stringvalue):判断字符串是否以子串value结束
eg:string s = "http://www.top191.com";
if(s.StartsWith("http://")||s.StartsWith("https://"))
{
Console.WriteLine("是网址");
}
else
{
Console.WriteLine("不是网址")；
}
//s.EndsWith();
Console.ReadKey();
● int IndexOf(string value):取子串value第一次出现的位置
eg: string str = "今天会下雨吗？"；
int i = str.IndexOf("下雨");//返回子字符串的第一个字符第一次出现的位置，如果不存在则返回-1
Console.WriteLine(i);
Console.ReadKey();
27.字符串的处理练习：
● eg1:接收用户输入的字符串，将其中的字符以与输入相反的顺序输出。"abc"→"cba"
static void Main(string[] args)
{
string str = "abc";
ConvertStr(str);
}
static void ConvertStr(string str)
{
char[] chars= str.ToCharArray();
string a ="";
for (int i =chars.Length - 1; i >= 0; i--)
{
a = a + chars[i];
}
Console.WriteLine(a);
Console.ReadKey();
}
● eg2:接收用户输入的一句英文，将其中的单词以反序输出。"hello c sharp"→"sharp c hello"
static void Main(string[] args)
{
string str = "hello c sharp";
ConvertStr(str);
}
static void ConvertStr(string str)
{
string[]strs = str.Split(‘ ‘);
string str2= "";
for (int i =strs.Length-1; i >= 0; i--)
{
str2 = str2 + strs[i]+" ";
}
Console.WriteLine(str2);
Console.ReadKey();
}
● eg3:从Email中提取出用户名和域名：abc@163.com
static void Main(string[]args)
{
Console.WriteLine("请输入一个合法的Email账户：");
string str = Console.ReadLine();
EmailSelect(str);
}
static void EmailSelect(stringstr)
{
if (str.Contains("@")&&str.Contains("."))
{
int i = str.IndexOf("@");
Console.WriteLine("用户名：{0}",str.Substring(0, i));
Console.WriteLine("域名：{0}",str.Substring(i + 1));
Console.ReadKey();
return;
}
else
{
Console.WriteLine("很抱歉，您输入的并非Email邮箱");
Console.ReadKey();
}
}
● eg4:文本文件中存储了多个文章标题，作者，标题和作者之间用若干空格隔开，每行一个，标题有的长有的短，输出的控制台的时候最多标题长度20，如果超过20，则截取长度17的字符串并且最后添加"...",加一个竖线后输出作者的名字
(1)-标准答案：
static void Main(string[] args)
{
string[] lines=System.IO.File.ReadAllLines(@"d:\123\456.txt",Encoding.Default);
foreach(string line in lines)
{
//string[]strs = line.Split(‘‘);
string[]strs = line.Split(new char[]{‘ ‘},StringSplitOptions.RemoveEmptyEntries);
string title = strs[0];//标题
string author = strs[1];//作者
//title.Substring(0,17);
if (title.Length> 17)
{
title = title.Substring(0, Math.Min(17, title.Length));//17和标题的长度两个数的最小值。Math.Min（）用来计算两个数中的最小值
title = title + "...";//一个问题：当标题没有超长的时候还会出现“。。。”
}
Console.WriteLine("{0}|{1}",title,author);
}
Console.ReadKey();
}
(2)static void Main(string[] args)
{
TxtConvert();
}
static void TxtConvert()
{
string[] txtlines =System.IO.File.ReadAllLines(@"d:\123\456.txt", Encoding.Default);
string[] strs = {};
foreach(string a in txtlines)
{
strs = a.Split(new char[] { ‘ ‘ }, StringSplitOptions.RemoveEmptyEntries);
strs[0] = strs[0].Trim();
strs[1] = strs[1].Trim();
if (strs[0].Length > 20)
{
strs[0] = strs[0].Substring(0, 17) + strs[0].Replace(strs[0].Substring(18),"...");
Console.WriteLine("输出结果为：{0}", strs[0] + "|" + strs[1]);
}
else
{
Console.WriteLine("输出结果为：{0}", strs[0] + "|" + strs[1]);
}
}
Console.ReadKey();
}
● eg5:从ini格式的文件中（每行是“件=值”格式）中读取出配置项的值
static void Main(string[]args)
{
string value =http://www.mamicode.com/GetConfigValue(@"d:/123/345.txt","端口");
Console.WriteLine(value);
Console.ReadLine();
}
static string GetConfigValue(string filename,string itemName)
{
string[] lines =System.IO.File.ReadAllLines(filename,Encoding.Default);
foreach(string line in lines)
{
string[] strs = line.Split(‘=‘);
string name = strs[0];
string value = http://www.mamicode.com/strs[1];
if(name.Trim()==itemName)
{
return value.Trim();
}
}
return"您输入的参数错误";//如果不加，那么程序就存在没有返回值的情况。且不能写在if语句后面。后面可以用异常的方法来解决。
}
● eg6:自己动手写聊天机器人，能够回答不同城市的天气，回答感情问题，问的次数过多还会饿死，提问可以模糊提问（包含指定关键词）
static void Main(string[] args)
{
Console.WriteLine("你好！我是机器人！");
while (true)
{
string str = Console.ReadLine();
if (str.Contains("天气"))
{
string city = str.Substring(2);
Console.WriteLine("{0}的天气是晴朗", city);
}
else if (str.Contains("你")&& (str.Contains("名字")|| str.Contains("姓名")))
{
Console.WriteLine("不要崇拜哥，哥只是个传说");
}
else if (str.StartsWith("88") || str.StartsWith("再见") ||str.StartsWith("bye"))
{
Console.WriteLine("萨又拿啦！");
return;
}
Console.ReadKey();
}
}
28.函数的ref,out参数
函数的参数默认是值传递的，也就是"复制一份“
eg:
Static void Main(string[] args)
{
int age = 20;
IncAge(age);
Console.WriteLine(”age={0}",age);//打印出的结果为：20
}
static void IncAge(int age)//复制了一份，IncAge内部改变的是副本
{
age++
}
//ref:
static void Main(string[] args)
{
int age = 20;
IncAge(ref age);
Console.WriteLine(”age={0}",age);//打印出的结果为：21
}
static void IncAge(ref int age)
{
age++
}
//out:
static voidMain(string[] args)
{
int age = 20;
IncAge(out age);
Console.WriteLine(”age={0}",age);//打印出的结果为：21
}
static void IncAge(out int age)
{
Console.WtriteLine(age);//提示没有初始化，外面给了初值，里面都将认为没有初始化
age=30;//out参数必须赋值。对于out来讲不需要在外面赋初值
}
ref必须先初始化，因为是引用，所以必须先"有“，才能引用，而out则是内部为外部复制，所以不需要初始化，而且初始化也没用。
ref应用场景内部对外部的值进行改变，out则是内部为外部变量复制，out一般用在函数有多个返回值的场所。
案例：两个变量交换；int.TryParse.
string str =Console.ReadLine();
int i;
if(int.TryParse(str, out i))
{
Console.WriteLine("转换成功！{0}", i);
}
else
{
Console.WriteLine("转换失败");
}
Console.ReadKey();
两个变量交换：ref应用
static void Main(string[] args)
{
int i1 = 10;
int i2 = 20;
Swap(ref i1,ref i2);
Console.WriteLine("i1={0},i2={1}", i1, i2);
Console.ReadKey();
}
static void Swap(ref int i1, ref inti2)
{
int temp =i1;
i1 = i2;
i2 = temp;
}
循序渐进学ASP.Net(二)wpf基础
1. 控件的属性及事件
TextBlock为只读文本；
一个控件的Click事件可以指派为其他控件的事件（不同的事件指向同一个方法如Button1的Click事件可以指向Button2的Click事件，则点击Button1时触发了Button2的Click事件）
Loaded——控件加载 如：Window_Loaded()
Closed——控件关闭
控件.Focus（）——获得焦点
要注意区分属性，方法，事件（回调机制），调用方法；
2. 删除控件的步骤（删除某个控件的事件）
a:删除.cs里的方法；
b:删除.Xaml中对应的事件的代码
3．Sender方法 Object Sender：触发事件的控件
Object类
eg:
a：Button btn=(Button)Sender;
btn.Content=”你点我了！”;
Object类是一切类的父类
以上代码与b：Button.Content=”你点我了！”;有区别a表示被触发事件控件的对象，例如：将另外一个控件的Click事件指向button_Click则此“另一个事件”的Content发生变化其值被赋值为“你点我了“而b的执行结果为button的Content发生变化；原因在于谁为触发事件的控件，则谁就为Sender
3. 类的继承
Class Animal
{
}
Class Dog:Animal
{
}
所有的类都是直接或间接的继承至Object类
*可用父类变量指向子类对象 即：
Dog dog=new Dog();
Animal a1=dog;//其实就是给实在的东西贴标签的过程；
变量是一个标签，对象是一个实际存在的东西
但不能将父类的变量指向子类的对象
但下列情况是允许的：
Animal a1=new Dog();
Dog d1=a1;//报错，程序只会一行一行的顺序执行，不会往上也不会往下看（牵强，意会） --不会往上看到- Animal a1=new Dog();
但可以写成 Dog d2=(Dog)a1;(有点类似于类型强制转换)
且 如 class Animal
{
Public Bark(){}
}
Class Dog:Animal
{
Publicjiao(){}
}
Animal A1=new Dog(); //死记
A1.jiao();//报错，程序只看 Animal A1=new Dog();即只会认为A1是Animal类的对象
下面的情况也是正确的
Animal D1=(Dog)a1;
D1.Bark();
4.集合数组
int [] nums=new int[3];//C#中数组的长度是一定的，若要长度可变，则使用集合
nums[0]=333;
nums[1]=444;
nums[2]=555;
foreach(int i in nums)
{
MessageBox.Show(i.ToString());
}
5.泛型
List<int> list=new List<int>(); //相当于长度不固定的数组（动态数组）、增加元素格式为list.Add();
//此处list为 List<int>的对象 此知识点对数据绑定很重要
list.Add(333);
list.Add(444);
list.Add(555);
list.Add(666);
……………………
Foreach(int i in list)
{
MessageBox.Show(i.ToString());
}
int[] nums=new int[3];//声明一个数组 长度为3
List可以看做是动态数组list.Count.ToString();//代表list的长度
list.Remove(333);//删除
list.Clear();//清除
6.XAML的文件格式
Xaml：描述界面的长相，必须学会手写xaml；严禁托控件
属性用引号；元素闭合
Xaml中两种赋值方式；Text=”XXX”和<TextBox.Text>XXX</TextBox.Text >
例：<Button Content=”30”></Button>
和 <Button.Content>30</ Button.Content >//此方法适用于复杂的属性 属性的值必须用引号，属性必须有闭合 <TextBox.Text/>表示自闭和；
7.控件的常用属性
l Visibility属性的值是枚举类型Visible可见；Collapsed不可见
text.Visibility=System.Windows.Visibility.Collapsed;//表示text不可见；
l IsEnabled:控件是否可用；bool类型
l Background：背景色；
l FontSize：字体大小
例：Button IsEnable=”False” //控件不可用
Background=””;//ctrl+j可选颜色；
private void btshan_Click(object sender, RoutedEventArgs e)
{
if (tetName.Visibility == System.Windows.Visibility.Hidden)
tetName.Visibility = System.Windows.Visibility.Visible;
else
{
tetName.Visibility = System.Windows.Visibility.Hidden;
}
}
实现闪一闪功能
8.TextBox控件（文本框）
IsReadOnly=”false”;//或者True只读
TextWrapping=”wrap”;//折行（多行）
MaxLength=”5”;长度（指能输入的长度）但若代码为Text=”…………”;可不受此长度的限制 在xaml里面 只是限制用户输入
9.PasswordBox控件
Password=”3333”;//初始密码
密码框Password为其密码而非XXX.Text
10.可空类型
C#中：
l 可空数据类型：引用类型，自定义类，String大部分类都是可空类型
l 不可空：值类型，int，boolean，decimal,DataTime等
l A:string s=””;//空字符串，但不是null，null表示没有
l B:string s1=null;//A表示有这个箱子，但箱子为空，B表示没有这个箱子，s1没有指向任何对象，不可空类型不能为null，有例外，可空的int类型
l Int? i1=null;//正确，在不可null类型前加?就可以为null—与数据库衔接点 因为数据库中 int类型可以为空
DataTime? D1=null;
同样：int?i5=3;
Int i6=i5;//报错，编译器只看声明时类型
但int i6=(int)i5;//正确，似乎可空类型包括了不可空类型，即int?包括了int； 在可空不可null类型前加“？”就可以为null
l Int ? i8=null;
Int i9=(int)i8;
编译时根据以上的规则，不会报错，但是运行时会抛出异常
11.CheckBox，复选框 IsChecked:是否选中，bool?(true,false)将用到上面的知识点
bool?与bool
if(CheckBox1.IsChecked)
{
}//报错
需要改为
if((bool)CheckBox1.IsChecked)//因为IsChecked属性为可空类型 CheckBox1.IsChecked 为bool?类型
注：实际中必须还要有下面的代码
private void checkBox1_Checked(object sender, RoutedEventArgs e)
{
}
12.RadioButton 只能有一个被选中
XX.IsChecked属性判断是否被选中；GroupName属性分组
13.DataPick控件（.Net4版本后才有）
l XX.SelectedDate获得日期，此控件的DateTime属性也是可空类型
14.DateTime
l DataTime.Now.ToString();//now包含了时分秒
15.Image控件
l Source属性 Source=”路径名”显示图片
Source="Image/beauty.jpg" 格式
16.ProgressBar 进度条控件 –数据绑定
ProgressBar Maximum="100" Minimum="0" Value=http://www.mamicode.com/"60"
IsIndeterminate="True"//该属性表示不确定的情况，即不确定进行到多少进度了
16.页面布局—控制控件位置—StackPanel
基本布局 StackPanel(默认为Orientation="Vertical"即垂直排列),
也可更改为水平排列 效果如下图
注意：在Button中只能有一个填充控件
<Button>
<Image Source="Img/beauty.jpg"></Image>
<TextBlock></TextBlock> --错误 1 属性“Content”已设置多次。 E:\asp.net movie\documents\day3_wpf\StackPanel1\StackPanel1\MainWindow.xaml 23 17 StackPanel1
</Button>
但是可以嵌套使用 Stackpanel布局解决此问题
<Button>
<StackPanel>
<Image Source="Img/beauty.jpg"></Image>
<TextBlock Text="你好啊！"></TextBlock>
</StackPanel>
</Button>
补充：TextBlock的VerticalAlignment属性控制显示文本的位置
<TextBlock VerticalAlignment="Center" Text="你好啊！"></TextBlock> --TextBlock垂直居中
17.页面布局—Grid
<Grid.ColumnDefinitions> --三列
<ColumnDefinition></ColumnDefinition>
<ColumnDefinition></ColumnDefinition>
<ColumnDefinition></ColumnDefinition>
</Grid.ColumnDefinitions>
<Grid.RowDefinitions> --四行
<RowDefinition></RowDefinition>
<RowDefinition></RowDefinition>
<RowDefinition></RowDefinition>
<RowDefinition></RowDefinition>
</Grid.RowDefinitions>
<Button Grid.Row="1" Grid.Column="1"></Button> --指定Button为第一行第一列（附加属性）
<Image Grid.Row="0" Grid.Column="1" Grid.ColumnSpan="2"></Image> -- Grid.ColumnSpan="2"占两行 同理也有Grid.RowSpan="2"
默认情况为三行四列平分窗口 用width和hight指定宽度和高度 指定某一行的高度 或者某一列的宽度
手写xaml完成登录界面布局
<Grid>
<Grid.ColumnDefinitions>
<ColumnDefinition></ColumnDefinition>
<ColumnDefinition></ColumnDefinition>
</Grid.ColumnDefinitions>
<Grid.RowDefinitions>
<RowDefinition></RowDefinition>
<RowDefinition></RowDefinition>
<RowDefinition></RowDefinition>
</Grid.RowDefinitions>
<TextBlock Text="用户名：" Margin="10" VerticalAlignment="Center" HorizontalAlignment="Center"></TextBlock>
<TextBlock Grid.Column="0" Grid.Row="1" Text="密码：" Margin="10" VerticalAlignment="Center" HorizontalAlignment="Center"></TextBlock>
<Button Grid.Column="0" Grid.Row="2" Content="登录" Margin="10" VerticalAlignment="Center" HorizontalAlignment="Center"></Button>
<TextBox Grid.Column="1" Grid.Row="0" Margin="20" ></TextBox>
<PasswordBox Grid.Column="1" Grid.Row="1" Margin="20"></PasswordBox>
<Button Content="取消" Grid.Column="1" Grid.Row="2" Margin="10" VerticalAlignment="Center" HorizontalAlignment="Center"></Button>
</Grid>
Margin="10"—表示控件距离四周的距离 VerticalAlignment="Center" HorizontalAlignment="Center" 分别表示垂直居中和水平居中
Grid布局完成美女连连看(棋盘) 生成一个10*10的Grid 生成10*10图片 并随机布局图片
a.动态往界面加控件
private void Window_Loaded(object sender, RoutedEventArgs e) --重点一
{
Button btn = new Button();//新建一个Button
btn.Content = "hahaha!";//Button的Content属性设置为“hahaha！”
sp1.Children.Add(btn); //将btn加入到sp1的孩子
}
b.窗口加载时 动态创建10行10列
for(int i=0;i<10;i++) //界面的行列定义
{
ColumnDefinition colDef = new ColumnDefinition(); --重点二
gridGame.ColumnDefinitions.Add(colDef);
RowDefinition rowDef = new RowDefinition();
gridGame.RowDefinitions.Add(rowDef);
}
for (int i = 0; i < 10; i++) //生成100个Button
{
for (int j = 0; j < 10; j++)
{
Button btn = new Button();
btn.Content = i + "," + j;
Grid.SetRow(btn,i);
Grid.SetColumn(btn,j);
gridGame.Children.Add(btn);
}
}
Grid.SetRow(btn,i);//修改btn的行坐标为i；
c.替换Button为Image
for (int i = 0; i < 10; i++) //生成100个Button
{
for (int j = 0; j < 10; j++)
{
//Button btn = new Button();
//btn.Content = i + "," + j;
//Grid.SetRow(btn,i);
//Grid.SetColumn(btn,j);
//gridGame.Children.Add(btn);
Image img = new Image();
img.Source = new BitmapImage(new Uri("1.jpg",UriKind.Relative));//img.Source=("1.jpg");错误
//因为不能像xaml会自动做类型转换 --先记下来（为什么后面会解释）
Grid.SetRow(img,i);
Grid.SetColumn(img,j);
gridGame.Children.Add(img);
}
}
最后结果为
Random random = new Random();//随机数生成器
//int i1=random.Next(0,99);//生成一个大于等于0小于99的随机整数
for (int i = 0; i < 10; i++) //生成100个Button
{
for (int j = 0; j < 10; j++)
{
//Button btn = new Button();
//btn.Content = i + "," + j;
//Grid.SetRow(btn,i);
//Grid.SetColumn(btn,j);
//gridGame.Children.Add(btn);
int imgName = random.Next(1,10);//生成一个大于等于1小于10的随机整数作为文件名
Image img = new Image(); --重点三
img.Source = new BitmapImage(new Uri("Images/"+imgName+".jpg",UriKind.Relative));//img.Source=("1.jpg");错误
//因为不能像xaml会自动做类型转换
Grid.SetRow(img,i);
Grid.SetColumn(img,j);
gridGame.Children.Add(img);
}
}
18.布局的嵌套
<Grid>
<Grid.ColumnDefinitions>
<ColumnDefinition></ColumnDefinition>
<ColumnDefinition></ColumnDefinition>
</Grid.ColumnDefinitions>
<Grid.RowDefinitions>
<RowDefinition></RowDefinition>
<RowDefinition></RowDefinition>
</Grid.RowDefinitions>
<Button Grid.Column="1" Grid.Row="1"></Button>
<StackPanel> --Grid里面可以嵌套StackPanel
<Button Content="1"></Button>
<Button Content="2"></Button>
<Button Content="3"></Button>
</StackPanel>
<Grid Grid.Column="1"> -- Grid里面也可以嵌套Grid
<Grid.ColumnDefinitions>
<ColumnDefinition></ColumnDefinition>
<ColumnDefinition></ColumnDefinition>
</Grid.ColumnDefinitions>
<Grid.RowDefinitions>
<RowDefinition></RowDefinition>
<RowDefinition></RowDefinition>
</Grid.RowDefinitions>
</Grid>
</Grid>
Grid里面嵌套StackPanel
Grid也可以嵌套Grid
练习：
1. 做计算器的界面
<Grid>
<Grid.RowDefinitions>
<RowDefinition></RowDefinition>
<RowDefinition></RowDefinition>
<RowDefinition></RowDefinition>
<RowDefinition></RowDefinition>
<RowDefinition></RowDefinition>
<RowDefinition></RowDefinition>
<RowDefinition></RowDefinition>
<RowDefinition></RowDefinition>
</Grid.RowDefinitions>
<Grid.ColumnDefinitions>
<ColumnDefinition></ColumnDefinition>
<ColumnDefinition></ColumnDefinition>
<ColumnDefinition></ColumnDefinition>
<ColumnDefinition></ColumnDefinition>
<ColumnDefinition></ColumnDefinition>
</Grid.ColumnDefinitions>
<Button Grid.Column="0" Grid.Row="2" Content="MC" Margin="5"></Button>
<Button Grid.Column="1" Grid.Row="2" Content="MR" Margin="5"></Button>
<Button Grid.Column="2" Grid.Row="2" Content="MS" Margin="5"></Button>
<Button Grid.Column="3" Grid.Row="2" Content="M+" Margin="5"></Button>
<Button Grid.Column="4" Grid.Row="2" Content="M-" Margin="5"></Button>
<Button Grid.Column="0" Grid.Row="3" Content="←" Margin="5"></Button>
<Button Grid.Column="1" Grid.Row="3" Content="CE" Margin="5"></Button>
<Button Grid.Column="2" Grid.Row="3" Content="C" Margin="5"></Button>
<Button Grid.Column="3" Grid.Row="3" Content="±" Margin="5"></Button>
<Button Grid.Column="4" Grid.Row="3" Content="√" Margin="5"></Button>
<Button Grid.Column="0" Grid.Row="4" Content="7" Margin="5"></Button>
<Button Grid.Column="1" Grid.Row="4" Content="8" Margin="5"></Button>
<Button Grid.Column="2" Grid.Row="4" Content="9" Margin="5"></Button>
<Button Grid.Column="3" Grid.Row="4" Content="/" Margin="5"></Button>
<Button Grid.Column="4" Grid.Row="4" Content="%" Margin="5"></Button>
<Button Grid.Column="0" Grid.Row="5" Content="4" Margin="5"></Button>
<Button Grid.Column="1" Grid.Row="5" Content="5" Margin="5"></Button>
<Button Grid.Column="2" Grid.Row="5" Content="6" Margin="5"></Button>
<Button Grid.Column="3" Grid.Row="5" Content="*" Margin="5"></Button>
<Button Grid.Column="4" Grid.Row="5" Content="1/x" Margin="5"></Button>
<Button Grid.Column="0" Grid.Row="6" Content="1" Margin="5"></Button>
<Button Grid.Column="1" Grid.Row="6" Content="2" Margin="5"></Button>
<Button Grid.Column="2" Grid.Row="6" Content="3" Margin="5"></Button>
<Button Grid.Column="3" Grid.Row="6" Content="-" Margin="5"></Button>
<Button Grid.Column="4" Grid.Row="6" Grid.RowSpan="2" Content="+" Margin="5"></Button>
<Button Grid.Column="0" Grid.Row="7" Grid.ColumnSpan="2" Content="0" Margin="5"></Button>
<Button Grid.Column="2" Grid.Row="7" Content="." Margin="5"></Button>
<Button Grid.Column="3" Grid.Row="7" Content="+" Margin="5"></Button>
<TextBox Grid.Row="1" Grid.ColumnSpan="5" Margin="2"></TextBox>
<TextBlock Text="View" VerticalAlignment="Center" HorizontalAlignment="Center"></TextBlock>
<TextBlock Text="Edit" Grid.Column="1" VerticalAlignment="Center" HorizontalAlignment="Center"></TextBlock>
<TextBlock Text="Help" Grid.Column="2" VerticalAlignment="Center" HorizontalAlignment="Center"></TextBlock>
</Grid>
2. 做腾讯微博界面
<Grid>
<Grid.RowDefinitions>
<RowDefinition></RowDefinition>
<RowDefinition></RowDefinition>
<RowDefinition></RowDefinition>
<RowDefinition></RowDefinition>
<RowDefinition></RowDefinition>
</Grid.RowDefinitions>
<Grid.ColumnDefinitions>
<ColumnDefinition></ColumnDefinition>
<ColumnDefinition></ColumnDefinition>
<ColumnDefinition></ColumnDefinition>
</Grid.ColumnDefinitions>
<Button Height="60" Width="60" HorizontalAlignment="Right">
<Image Source="Images/img1.jpg"></Image>
</Button>
<TextBlock Grid.Column="1" Grid.Row="0" Text="个人资料" FontSize="24" VerticalAlignment="Center"></TextBlock>
<Image Grid.Column="1" Grid.Row="1" Source="Images/2.jpg" Grid.RowSpan="3"></Image>
<Grid Grid.Column="2" Grid.Row="2">
<Grid.ColumnDefinitions>
<ColumnDefinition></ColumnDefinition>
<ColumnDefinition></ColumnDefinition>
</Grid.ColumnDefinitions>
<Image Source="Images/shouting.jpg"></Image>
<Button Grid.Column="1" Content="取消收听" Background="Cyan" Margin="15"></Button>
</Grid>
<Button Grid.Column="2" Grid.Row="3" Margin="15" Content="特别关心" Background="Cyan"></Button>
<StackPanel Grid.Column="1" Grid.Row="4">
<TextBlock Text="陈圆圆" HorizontalAlignment="Center"></TextBlock>
<TextBlock Text="@chengyuanyuan" HorizontalAlignment="Center"></TextBlock>
<TextBlock Text="个人介绍" HorizontalAlignment="Center"></TextBlock>
<TextBlock Text="还没想写什么" HorizontalAlignment="Center"></TextBlock>
</StackPanel>
</Grid>
19.菜单menu
<StackPanel> --常规布局
<Menu Height="23" HorizontalAlignment="Left" Name="menu1" VerticalAlignment="Top" Width="200" >
<MenuItem Header="文件">
<MenuItem Header="打开"></MenuItem>
<MenuItem Header="退出"></MenuItem>
</MenuItem>
<MenuItem Header="编辑">
<MenuItem Header="复制"></MenuItem>
<MenuItem Header="粘贴"></MenuItem>
</MenuItem>
</Menu>
<Grid>
</Grid>
</StackPanel>
a.DockPanel布局（将其子控件靠上下左右操作）
<Grid>
<DockPanel>
<Menu DockPanel.Dock="Top">
<MenuItem Header="文件"></MenuItem>
<MenuItem Header="编辑"></MenuItem>
</Menu>
<TextBox DockPanel.Dock="Bottom"></TextBox>
</DockPanel>
</Grid>
菜单被点击，即处理其Click事件
20.工具栏ToolBar
<Grid>
<DockPanel>
<ToolBar DockPanel.Dock="Top">
<Button Content="保存"></Button>
<Button Content="新建"></Button>
<Button Content="自动保存"></Button>
<CheckBox Content="打开"></CheckBox>
</ToolBar>
<TextBox DockPanel.Dock="Bottom"></TextBox>
</DockPanel>
</Grid>
所有的控件都可以在ToolBar上使用，但会改变控件的造型 如上图的
<DockPanel>
<Menu DockPanel.Dock="Top">
<MenuItem Header="文件"></MenuItem>
<MenuItem Header="编辑"></MenuItem>
</Menu>
<ToolBar DockPanel.Dock="Top" Height="35">
<Button Content="保存"></Button>
<Button>
<Image Source="Images/open.jpg" ></Image>
</Button>
<Button Content="自动保存"></Button>
<CheckBox Content="打开"></CheckBox>
</ToolBar>
<TextBox DockPanel.Dock="Bottom"></TextBox>
</DockPanel>
21.多窗口基础
--添加窗口
窗口的弹出
a.设计好窗口
b.创建新窗口类的一个实例
private void Button_Click(object sender, RoutedEventArgs e)
{
AboutWindow aboutWin = new AboutWindow();
aboutWin.ShowDialog();
}
c.对实例调用ShowDialog()方法即可调出新窗口
--设置谁为主窗口
在app.xaml文件中更改StartupUri=“”
<Application x:Class="ToolBar.App"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
StartupUri="MainWindow.xaml">
--窗口的属性
<Window x:Class="ToolBar.AboutWindow"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
Title="关于" Height="300" Width="300" Name="Aboutwindow"
ResizeMode="NoResize" WindowStartupLocation="CenterScreen">
ResizeMode="NoResize" --设置窗口不可改变其大小
WindowStartupLocation="CenterScreen" --设置窗口打开时位于屏幕正中央
WindowState="Maximized" --让窗口最大化
--两个窗口之间传数据（窗口其实就是一个类
a.取出TxtName的数据string name = txtName.Text;
b.在弹出窗口设置一个属性public string UserName { get; set; }
c.得到该数据 aboutWin.UserName = name;
d.在新窗口加载的时候，设置TextBlock的Text属性为该值
private void Aboutwindow_Loaded(object sender, RoutedEventArgs e)
{
tbName.Text = UserName;
}
22.多窗口交互的例子
弹出窗口代码:
public partial class NewWindow : Window
{
public string Message { get; set; }
public NewWindow()
{
InitializeComponent();
}
private void Button_Click(object sender, RoutedEventArgs e)
{
DialogResult = true;
Message=txtMess.Text;
}
private void Button_Click_1(object sender, RoutedEventArgs e)
{
DialogResult = false;
}
}
主窗口代码:
public partial class MainWindow : Window
{
public MainWindow()
{
InitializeComponent();
}
private void Button_Click(object sender, RoutedEventArgs e)
{
NewWindow newwindow = new NewWindow();
bool? b= newwindow.ShowDialog(); -- ShowDialog()为可空类型,如果一个窗口为ShowDialog()该函数调出来的,则一旦对DialogResult赋值,窗口会退出
if (b == true)
{
MessageBox.Show("确定 "+newwindow.Message);
}
else if(b==false)
{
MessageBox.Show("取消");
}
else if (b == null)
{
MessageBox.Show("没设置");
}
}
}
联系:显示”是、否、不确定“的对话框
MessageBox.Show("fff","提示",MessageBoxButton.YesNoCancel);//该重载方法第一个参数表示弹出对话框的显示类容，第二个参数为标题，第三个参数为什么类型的按钮
Show方法的返回值为MessageBoxResult
MessageBoxResult r1= MessageBox.Show("确定还是取消还是不确定，做出你的选择！","提示",MessageBoxButton.YesNoCancel);
if (r1==MessageBoxResult.Yes)
{
MessageBox.Show("Ok啦");
}
else if (r1 == MessageBoxResult.No)
{
MessageBox.Show("取消！");
}
else if (r1 == MessageBoxResult.Cancel)
{
MessageBox.Show("不确定啦");
}
练习：做一个类似
这样的对话框，调用者可以得知按得是哪个按钮
主窗口.cs
private void button1_Click(object sender, RoutedEventArgs e)
{
MyWindow myWin = new MyWindow();
myWin.ShowDialog();
if (Answer == true)
{
MessageBox.Show("点击了确定按钮！");
}
else if (Answer == false)
{
MessageBox.Show("点击了取消按钮！");
}
else if (Answer == null)
{
MessageBox.Show("点击了不确定按钮！");
}
}
自己定义的窗口.cs
private void Button_Click(object sender, RoutedEventArgs e)
{
MainWindow mainWin = new MainWindow();
mainWin.Answer = true;
Close();
}
private void Button_Click_1(object sender, RoutedEventArgs e)
{
MainWindow mainWin = new MainWindow();
mainWin.Answer = false;
Close();
}
private void Button_Click_2(object sender, RoutedEventArgs e)
{
MainWindow mainWin = new MainWindow();
mainWin.Answer = null;
Close();
}
运行结果：无论点击哪个按钮均弹出“点击了不确定按钮！“
遗留问题：
问题1 多窗口传递值时，可以将主窗口的值传给其他窗口，但是其他窗口的值传不回主窗口
问题2 自己动手写对话框，点击了不同按钮，不能识别
学习至今知识有限:似乎这两个问题是一个知识点,即,从弹出窗口传回的值主窗口控件没有即时刷新机制,解决方法,主窗口在弹出窗口前先退出
(Close())当其他窗口有值传回给主窗口时再调出（Load()）主窗口，如此实现主窗口控件属性的刷新
证明实例如下：
主窗口设置为该形式，点击“调出新窗口”时则弹出窗口，控件2为一个TextBlock控件，其值与弹出窗口4的值一致，即实现弹出窗口与主窗口的交互
并且又主窗口的属性值可以确定，弹出窗口点了哪个哪件。该功能由主窗口的public bool? Answer { set; get; }方法实现
public string Message { set; get; }
主窗口.cs代码如下：
namespace severalWindows
{
/// <summary>
/// MainWindow.xaml 的交互逻辑
/// </summary>
public partial class MainWindow : Window
{
public bool? Answer { set; get; }
public string Message { set; get; }
//public int Temp { set; get; }
public MainWindow()
{
InitializeComponent();
}
private void button1_Click(object sender, RoutedEventArgs e)
{
NewWindow newWin = new NewWindow();
Close();
newWin.ShowDialog();
}
private void Window_Loaded(object sender, RoutedEventArgs e)
{
txtMain.Text = Message;
if (Answer == true)
{
txtbMain.Text = "你点击了弹出窗口的-确定-按钮！";
}
else if (Answer == false)
{
txtbMain.Text = "你点击了弹出窗口的-取消-按钮！";
}
else if (Answer == null)
{
txtbMain.Text = "你点击了弹出窗口的-重试-按钮！";
}
}
}
}
弹出窗口的.cs代码如下：
namespace severalWindows
{
/// <summary>
/// NewWindow.xaml 的交互逻辑
/// </summary>
public partial class NewWindow : Window
{
public NewWindow()
{
InitializeComponent();
}
private void btOk_Click(object sender, RoutedEventArgs e)
{
MainWindow mainWin = new MainWindow();
mainWin.Message=txtNew.Text;
mainWin.Answer = true;
Close();
mainWin.ShowDialog();
}
private void btCancel_Click(object sender, RoutedEventArgs e)
{
MainWindow mainWin = new MainWindow();
mainWin.Message = txtNew.Text;
mainWin.Answer = false;
Close();
mainWin.ShowDialog();
}
private void btRetry_Click(object sender, RoutedEventArgs e)
{
MainWindow mainWin = new MainWindow();
mainWin.Message = txtNew.Text;
mainWin.Answer = null;
Close();
mainWin.ShowDialog();
}
}
}
23.打开、保存文件对话框
private void button1_Click(object sender, RoutedEventArgs e)
{
OpenFileDialog ofd = new OpenFileDialog();
//文件类型过滤器
ofd.Filter="文本文件|*.txt|PNG图片|*.png|视频|*.avi|所有文件|*.*";
if (ofd.ShowDialog() == true)
{
string fileName = ofd.FileName;
MessageBox.Show("打开文件成功！"+fileName);
}
else
{
MessageBox.Show("取消了！");
}
}
private void button2_Click(object sender, RoutedEventArgs e)
{
SaveFileDialog sfd = new SaveFileDialog();
sfd.Filter = "文本文件|*.txt|PNG图片|*.png|视频|*.avi|所有文件|*.*";
if(sfd.ShowDialog()==true)
{
//会自动加扩展名，提示是否覆盖
MessageBox.Show(sfd.FileName);
}
}
打开图片并显示：
private void btImg_Click(object sender, RoutedEventArgs e)
{
OpenFileDialog ofd = new OpenFileDialog();
//文件类型过滤器
ofd.Filter = "图片文件|*.jpg";
if (ofd.ShowDialog() == true)
{
string fileName = ofd.FileName;
image1.Source = new BitmapImage(new Uri(fileName));
MessageBox.Show("打开文件成功！" + fileName);
}
else
{
MessageBox.Show("取消了！");
}
}
第五季:ado.net基础
1. 数据绑定
<Slider Maximum="100" Minimum="0" Height="23" HorizontalAlignment="Left" Margin="78,101,0,0" Name="slider1" VerticalAlignment="Top" Width="100" />
<TextBox Text="{Binding Value,ElementName=slider1}" Height="23" HorizontalAlignment="Left" Margin="80,151,0,0" Name="textBox1" VerticalAlignment="Top" Width="120" />
Slider绑定到TextBox的值
1.在项目中新建Person类----定义类，定义属性
指定其Public属性为
class Person
{
public string Name { get; set; }
public int Age { get; set; }
}
2. 在主窗口中创建一个Person类的对象p1 并指定其属性值 再将textName和textAge的数据上下文（DataContent）值指定为p1 ----new一个实例，给要绑定的控件设定DataContent
private Person p1 = new Person();
public MainWindow()
{
InitializeComponent();
}
private void Window_Loaded(object sender, RoutedEventArgs e)
{
p1.Name = "Alice Qin";
p1.Age = 10;
textAge.DataContext = p1;
textName.DataContext = p1;
}
3.在主窗口中名字为：textName和textAge的两个控件分别绑定到p1的Name和Age（p1已经有了Name和Age属性） -----xaml中要进行数据绑定的属性
<TextBox Text="{Binding Name}" Grid.Column="0" Grid.Row="0" Margin="10" Name="textName"></TextBox>
<TextBox Text="{Binding Age}" Grid.Column="0" Grid.Row="1" Margin="10" Name="textAge"></TextBox>
TextBox控件还有ToolTip属性（鼠标放到TextBox上时显示的值）提示工具也可以绑定 几乎所有的属性都能绑定
<TextBox Text="{Binding Name}" Grid.Column="0" ToolTip="{Binding Age}" Grid.Row="0" Margin="10" Name="textName"></TextBox>
为什么要定义一个类型（Person），是为了方便尽量不操作控件。
如果直接点击Age++按钮，控件的值不会改变但是如果在界面textAge的值改变后点击显示Age按钮，则会显示出p1的Age已经改变了，因为普通对象
没有“通知我的属性改变了“这么一种机制，所以改变对象的属性界面不会变，但是界面改变是有TextChanged之类的事件的，所以改变界面可以同步修改到界面。
解决此问题：Person类需要一种通知属性改变的一种机制，解决方案代码如下（实现双向绑定）： ---如果要求后台对象值发生改变界面跟着变则需要实现InotifyPropertyChanged接口，并且属性值变化后
触发事件
public int Age
{
get
{
return age;
}
set
{
this.age = value;
if(PropertyChanged!=null)
{
PropertyChanged(this,new PropertyChangedEventArgs("Age"));
}
}
}
如果后台对象的值不会变，则没有必要实现InotifyPropertyChanged该接口
class Person:INotifyPropertyChanged //解析
{
private string name;
public string Name
{
get
{
return name;
}
set
{
this.name = value;
if (PropertyChanged != null)
{
PropertyChanged(this, new PropertyChangedEventArgs("Name"));
}
}
}
private int age;
public int Age
{
get
{
return age;
}
set
{
this.age = value;
if(PropertyChanged!=null)
{
PropertyChanged(this,new PropertyChangedEventArgs("Age"));
}
}
}
public event PropertyChangedEventHandler PropertyChanged;
//实现接口INotifyPropertyChanged 只有一个事件PropertyChanged
//INotifyPropertyChanged是.net内置的接口，数据绑定会检测Datacontext是否实现了INotifyPropertyChanged
//如果实现了，就会监听PropertyChanged得知属性的变化
}
现增加Person的属性值
private void Window_Loaded(object sender, RoutedEventArgs e)
{
p1.Name = "Alice Qin";
p1.Age = 10;
p1.Height = 190;
p1.Gander = true;
textAge.DataContext = p1;
textName.DataContext = p1;
textHeight.DataContext = p1;
cbGander.DataContext = p1;
}
Person增加Height和Gander属性,并绑定到一个TextBox和一个CheckBox上
Person中定义：
public int Height
{
set;
get;
}
public bool Gander
{
get;
set;
}
Xaml中定义：
<TextBox Margin="123,12,18,96" Name="textHeight" Text="{Binding Height}" Grid.Row="1" />
<TextBlock Height="23" HorizontalAlignment="Left" Margin="42,36,0,0" Name="textBlock3" Text="身高" VerticalAlignment="Top" Grid.Row="1" />
<TextBlock Height="23" HorizontalAlignment="Left" Margin="42,0,0,30" Name="textBlock4" Text="性别" VerticalAlignment="Bottom" Grid.Row="1" Width="39" />
<CheckBox IsChecked="{Binding Gander}" Content="Gander" Height="16" HorizontalAlignment="Left" Margin="123,110,0,0" Name="cbGander" VerticalAlignment="Top" Grid.Row="1" />
疑问：又没有一种机制：可以实现窗口中（Grid）控件的数据上下文都为p1（p1为Person的一个对象）
则只需要替换为：
//textAge.DataContext = p1;
//textName.DataContext = p1;
//textHeight.DataContext = p1;
//cbGander.DataContext = p1;
grid1.DataContext = p1;
尽管如此：也可以指定部分属性的数据上下文为为其他对象
如：textAge.DataContext = p2;
（p2.Name = "Alice Qin";
P2.Age = 10;
P2.Height = 190;
P2.Gander = true;）
数据绑定答疑：Binding 可以绑定类的属性：如Person.Age.……
2. ListBox
<ListBox Height="100" HorizontalAlignment="Left" Margin="57,74,0,0" Name="listBox1" VerticalAlignment="Top" Width="120" >
<ListBox.Items>
<ListBoxItem Content="aaa"></ListBoxItem>
<ListBoxItem Content="bbb"></ListBoxItem>
<ListBoxItem Content="ccc"></ListBoxItem>
<ListBoxItem Content="ddd"></ListBoxItem>
</ListBox.Items>
</ListBox>
也可以通过数据绑定给ListBoxItem的Content属性赋值：
List<Person> list = new List<Person>();
Person p1 = new Person();
p1.Name = "杨中科";
p1.Age = 18;
list.Add(p1);
//或采用下面的写法： list.Add(new Person { Name="苏坤",Age=16});
但此时：显示的内容如下
需要指定ListBox的 DisplayMemberPath
DisplayMemberPath="Name"
<ListBox DisplayMemberPath="Name" SelectedValuePath="Age" Height="100" HorizontalAlignment="Left" Margin="72,162,0,0" Name="lbPerson" VerticalAlignment="Top" Width="120" />
private void button1_Click(object sender, RoutedEventArgs e)
{
//SelectedItem获得的是选中行的对应的对象。如果没有选中行就是null
object selectedItem = lbPerson.SelectedItem;
//SelectedValue获得选中行对应的对象的“SelectedValuePath标志的属性”的属性值
object selectedValue = http://www.mamicode.com/lbPerson.SelectedValue;
}
ListBox显示的集合是ItemSource属性，不是DataContext lbPersons.ItemsSource=list;
默认是把每一项的对象ToString()显示，可以设定DisplayMemberPath让他显示某个属性的值
3. 多个ListBox联动
在MainWindow中创建两个ListBox控件命名为lbPro和lbCity其中lbPro定义泛型List<string>存放省
private void Window_Loaded(object sender, RoutedEventArgs e)
{
List<string> listProv = new List<string>();
listProv.Add("广东");
listProv.Add("四川");
listProv.Add("重庆");
lbPro.ItemsSource = listProv;
}
省联动市代码如下:
private void lbPro_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
string prov = (string)lbPro.SelectedItem; // lbPro.SelectedItem为object类型，现指定为：string类型即可
if(prov=="广东")
{
List<string> listCity = new List<string>();
listCity.Add("佛山");
listCity.Add("无影脚");
listCity.Add("叶问");
lbCity.ItemsSource = listCity;
}
else if (prov == "四川")
{
List<string> listCity = new List<string>();
listCity.Add("宜宾");
listCity.Add("泸州");
listCity.Add("雅安");
lbCity.ItemsSource = listCity;
}
else if (prov == "重庆")
{
List<string> listCity = new List<string>();
listCity.Add("万州");
listCity.Add("江北");
listCity.Add("九龙坡");
lbCity.ItemsSource = listCity;
}
}
4. ListBox案例：显示学生成绩
学生成绩、年龄、成绩三级联动
定义学生类：
class Students
{
public string Name { get; set; }
public int Age { get; set; }
public int Score { get; set; }
}
窗口布局：
<ListBox DisplayMemberPath="Name" Height="145" HorizontalAlignment="Left" Margin="32,38,0,0" Name="lbName" VerticalAlignment="Top" Width="120" SelectionChanged="lbName_SelectionChanged" />
<ListBox DisplayMemberPath="Age" Height="145" HorizontalAlignment="Left" Margin="175,38,0,0" Name="lbAge" VerticalAlignment="Top" Width="120" />
<ListBox DisplayMemberPath="Score" Height="145" HorizontalAlignment="Left" Margin="324,38,0,0" Name="lbScore" VerticalAlignment="Top" Width="120" />
在ListBox的SelectionChanged方法中写入联动代码：
private void lbName_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
Students stu = (Students)lbName.SelectedItem;
if (stu.Name == "小明")
{
List<Students> list = new List<Students>();
list.Add(new Students { Name = "小明", Age = 12, Score = 80 });
lbAge.ItemsSource = list;
lbScore.ItemsSource = list;
}
else if (stu.Name == "小华")
{
List<Students> list = new List<Students>();
list.Add(new Students { Name = "小华", Age = 22, Score = 85 });
lbAge.ItemsSource = list;
lbScore.ItemsSource = list;
}
else if (stu.Name == "小花")
{
List<Students> list = new List<Students>();
list.Add(new Students { Name = "小花", Age = 32, Score = 90 });
lbAge.ItemsSource = list;
lbScore.ItemsSource = list;
}
else if (stu.Name == "小方")
{
List<Students> list = new List<Students>();
list.Add(new Students { Name = "小方", Age = 42, Score = 95 });
lbAge.ItemsSource = list;
lbScore.ItemsSource = list;
}
}
5. DataGrid控件
指定ItemsSource属性即可
List<Students> list = new List<Students>();
list.Add(new Students { Name = "小明",Age=12,Score=80 });
list.Add(new Students { Name = "小华", Age = 22, Score = 85 });
list.Add(new Students { Name = "小花", Age = 32, Score = 90 });
list.Add(new Students { Name = "小方", Age = 42, Score = 95 });
dg1.ItemsSource = list;
<DataGrid Name="dg2" AutoGenerateColumns="False"></DataGrid> ---禁用自动生成列
<DataGrid Name="dg2" AutoGenerateColumns="False" CanUserAddRows="False"> --禁用自动生成行
并且行列可以自己定义
<Grid>
<DataGrid Name="dg2" AutoGenerateColumns="False" CanUserAddRows="False">
<DataGrid.Columns> --自定义列
<DataGridTextColumn Binding="{Binding Name}" Header="姓名"></DataGridTextColumn>
<DataGridCheckBoxColumn IsReadOnly="True" Binding="{Binding Gander}" Header="性别"></DataGridCheckBoxColumn> -- IsReadOnly="True"设置为只读，不能修改，也可以直接对整个表格设置为只读
</DataGrid.Columns>
</DataGrid>
private void Window_Loaded(object sender, RoutedEventArgs e)
{
List<Students> list = new List<Students>();
list.Add(new Students { Name = "小明", Age = 12, Score = 80,Gander=true,ClassName="一班"});
list.Add(new Students { Name = "小华", Age = 22, Score = 85, Gander = true, ClassName = "二班" });
list.Add(new Students { Name = "小花", Age = 32, Score = 90, Gander = false, ClassName = "三班" });
list.Add(new Students { Name = "小方", Age = 42, Score = 95, Gander = true, ClassName = "四班" });
dg2.ItemsSource = list;
}
<DataGrid.Columns>
<DataGridTextColumn Binding="{Binding Name}" Header="姓名"></DataGridTextColumn>
<DataGridCheckBoxColumn IsReadOnly="True" Binding="{Binding Gander}" Header="性别"></DataGridCheckBoxColumn>
<DataGridComboBoxColumn x:Name="colClassName" SelectedItemBinding="{Binding ClassName}"></DataGridComboBoxColumn>
</DataGrid.Columns>
private void Window_Loaded(object sender, RoutedEventArgs e)
{
List<Students> list = new List<Students>();
list.Add(new Students { Name = "小明", Age = 12, Score = 80,Gander=true,ClassName="一班"});
list.Add(new Students { Name = "小华", Age = 22, Score = 85, Gander = true, ClassName = "二班" });
list.Add(new Students { Name = "小花", Age = 32, Score = 90, Gander = false, ClassName = "三班" });
list.Add(new Students { Name = "小方", Age = 42, Score = 95, Gander = true, ClassName = "四班" });
dg2.ItemsSource = list;
List<string> classNames = new List<string>();
classNames.Add("一班");
classNames.Add("二班");
classNames.Add("三班");
classNames.Add("四班");
colClassName.ItemsSource = classNames;
}
6. 数据库的基本概念
PS:向vs2010(已有sql server 2008实例)精简sql server2008中添加sql server management studio方法
1.升级现有express(单击开始→Microsoft SQL Server 2008→配置工具→SQL Server 安装中心)
2单击“版本升级”，出现一下画面
3一路默认到以下界面
4选择“具有高级服务的Express版本”确认之后
5现在安装SQLManagementStudio_x86_CHS它就可以成功添加SQL Server Management Studio了。
登录方法为:windows身份验证 计算机名\实例名(默认为SQLEXPRESS)
主键：数据行的唯一标识 分为逻辑主键和业务主键 业务主键是使用有业务意义的字段做主键（有实际意义） 逻辑主键（目前主流使用）是只没有任何业务意义的主键（比如流水号）
设置主键的方法：
标识规范选成：是
nvarchar表示字符串nvarchar(MAX)表示最大 numeric(18, 2)表示小数点后取两位，最长为整数部分加小数部分最长为18位
1. 新增列，给列起名字，名字一半都用英文
2. 选择列类型，bigint，非常大的整数，可以设定属性中的“标识规范“=是来自动增长，然后主键上点击右键设置主键，
nvarchar（30）表示长度最多为30的字符串，nvarchar(MAX)产度不限的字符串，如果字段可以为空，则勾选“允许null值”
3.保存，给表命名。T_开头
若遇到
工具→设计器→
阻止保存要求重新创建表的更改前的勾去掉
7.SQL语句
INSERT INTO T_PERSON(ID,NAME,AGE) VALUES(1,’JIM’,20) SQL语句不区分大小写（仅字段和表名）删除（delete），更新（update set ） where过滤
insert into T_Students (Name,Age,Habit) values (‘小明‘,19,‘打篮球‘)
select * from T_Students
delete from T_Students where Name=‘小明‘
delete from T_Students where Age>=90
update T_Students set Age=Age+1
select Name as 姓名 Age as 年龄 from T_Students
as取列的别名
8.数据汇总
select MAX(Age) from T_Students
select MIN(Age) from T_Students
select AVG(Age) from T_Students
select MAX(Age) as 最大年龄,MIN(Age) as 最小年龄,AVG(Age) as 平均年龄 from T_Students
select COUNT(*) from T_Students where Age>100
select MIN(Age) from T_Students where Age>100
9.order by和like
a.
通配符:%表示若干字符 _表示一个字符
select Name from T_Students where Name like ‘小%‘ and Name like ‘%花‘ 以小开头，以花结尾
select Name from T_Students where Name like ‘%小%‘ Name中含有小
like语句缺陷（大项目慎用）大数据库查询较慢，全文检索课解决此问题
b.
order by
select * from T_Students order by Age desc
desc:降序
asc：升序
若遇Age相同则其他检索条件可并列筛选
select * from T_Students order by Age desc,Height desc 表示若Age相同则Height也按降序排列
order by 语句可以和where一起使用 但是 order by语句必须放到where语句之后—先过滤再排序
10.Ado.Net基础
.Net中用来向数据库提交执行SQL语句的一堆类
SQL server的登录方式
a. Windows身份验证，但是一般项目中都是单独的数据库服务器，程序在另外一台电脑上连接到SQLServer，要求输入密码，否则不安全
b. SQLServer验证：使用用户名、密码验证（推荐使用）。启用方法：以Windows身份验证登进去，然后右键根节点→安全性；
c. 启用sa账户：SQLServer的最高权限管理员账户，启用方法：根节点→安全性→sa→常规中修改密码，默认是强制复杂密码，可以取消“强制实施密码策略”。“状态”的“登录”勾选为“启用”
注意：基于安全考虑，不要启用sa账户，而是针对数据库建立数据库的专用受限账户（项目中）。
本机sa账户首次登录报错：
代码如下：
using (SqlConnection conn = new SqlConnection(
"Data Source=.; Initial Catalog=MyTest;User ID=sa;Password=123456"))
{
//若是连接本机数据库则"Data Source=.;若为其他服务器，则"Data Source=IP;
conn.Open();//打开连接
}
报错信息如下：
A network-related or instance-specific error occurred while establishing a connection to SQL Server.
The server was not found or was not accessible.
Verify that the instance name is correct and that SQL Server is configured to allow remote connections.
(provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
解决：（连接字符创错误）
//IDisposible
//using(类型 d = new ....)
//{.....................}
//SqlConnection为建立和数据库连接的对象
using (SqlConnection conn = new SqlConnection(
@"Data Source=.\SQLEXPRESS; Initial Catalog=MyTest;User ID=sa;Password=123456"))
{
conn.Open();
//若是连接本机数据库则"Data Source=.\SQLEXPRESS;若为其他服务器，则"Data Source=IP;
}
Using—及时释放资源
cmd.ExecuteNonQuery();//用于执行update,……等非查询语句
using (SqlConnection conn = new SqlConnection( // SqlConnection为建立和数据库连接的对象
@"Data Source=.\SQLEXPRESS; Initial Catalog=MyTest;User ID=sa;Password=123456"))
{
conn.Open();
//通过连接创建一个向数据库发命令（Command）的对象SqlCommand
//若是连接本机数据库则"Data Source=.\SQLEXPRESS;若为其他服务器，则"Data Source=IP;
using (SqlCommand cmd = conn.CreateCommand())
{
//CommandText为要执行的SQL语句
cmd.CommandText = "Insert into T_Students(Name,Age) values(‘元芳‘,2100)";//要执行的SQL语句
cmd.ExecuteNonQuery();//执行ExecuteNonQuery();非查询
}
}
MessageBox.Show("执行完成！");
cmd.ExecuteScalar();用于执行有且只有一行一列返回值的SQL语句
using(SqlConnection conn = new SqlConnection(
@"Data Source=.\SQLEXPRESS; Initial Catalog=MyTest;User ID=sa;Password=123456"))
{
conn.Open();
using(SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "select count(*) from T_Students where Age<100";
int i= (int)cmd.ExecuteScalar();
MessageBox.Show(i+"条数据");
}
}
MessageBox.Show("执行完成！");
怎么获得自增字段的主键值
select max(Id) from T_Students ----错误，及时性会造成严重错误
正确写法：
insert into T_Students (Name,Age) output inserted.Id values (‘小菜‘,155) ---刚插入的Id输出inserted.Id
ExecuteScalar() --返回值为long类型
在SQL中 sql server .net\c#
Int int
Bigint long
Nvarchar string
Bit (0\1) bool
using(SqlConnection conn = new SqlConnection(
@"Data Source=.\SQLEXPRESS; Initial Catalog=MyTest;User ID=sa;Password=123456"))
{
conn.Open();
using(SqlCommand cmd = conn.CreateCommand())
{
//获得自增字段的值
cmd.CommandText = "insert into T_Students(Name,Age) output inserted.Id values (‘小李‘,156)";
long i = (long)cmd.ExecuteScalar();
MessageBox.Show(i.ToString());
}
}
11.ExecuteReader执行查询---执行多条查询结果
using (SqlConnection conn = new SqlConnection(
@"Data Source=.\SQLEXPRESS; Initial Catalog=MyTest;User ID=sa;Password=123456"))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "select * from T_Students where Age<100";
using (SqlDataReader reader = cmd.ExecuteReader())
//ExecuteReader实现了Idisposable接口，会自动释放资源,reader的返回值为bool类型
{
while (reader.Read())//reader.Read()为过滤数据完之前（指针没有移到最后）返回值永远为true
{
String name = reader.GetString(1);//GetString(i)参数i为数据库中要查询数据是在第几列
//MessageBox.Show(name);
int age = reader.GetInt32(2);
MessageBox.Show(age.ToString()+"-"+name);
}
}
}
}
12.参数化查询避免SQL注入漏洞攻击
private void btnSearch_Click(object sender, RoutedEventArgs e)
{
using (SqlConnection conn = new SqlConnection(
@"Data Source=.\SQLEXPRESS; Initial Catalog=MyTest;User ID=sa;Password=123456"))
{
conn.Open();
using(SqlCommand cmd=conn.CreateCommand())
{
cmd.CommandText = "select age from T_Students where Name=‘杨中科‘";
using(SqlDataReader reader=cmd.ExecuteReader())
{
while(reader.Read())
{
//GetInt32获得的是int类型
//GetInt64获得的是long类型（数据库中是bigint）
int age = reader.GetInt32(0);
// cmd.CommandText = "select age from T_Students where Name=‘杨中科‘";参数为查询结果的第几列
MessageBox.Show(age.ToString());
}
}
}
}
实现查询Name=‘杨中科‘的Age等于多少
现要实现输入Name查询Age
cmd.CommandText = "select age from T_Students where Name=‘"+textName.Text+"‘";----字符串拼接即可
但会出现注入漏洞攻击
private void btnSearch_Click(object sender, RoutedEventArgs e)
{
using (SqlConnection conn = new SqlConnection(
@"Data Source=.\SQLEXPRESS; Initial Catalog=MyTest;User ID=sa;Password=123456"))
{
conn.Open();
using(SqlCommand cmd=conn.CreateCommand())
{
//输入1‘or‘1‘=‘1 会造成SQL注入漏洞 通过参数查询解决此问题
cmd.CommandText = "select age from T_Students where Name=‘"+textName.Text+"‘";
using(SqlDataReader reader=cmd.ExecuteReader())
{
while(reader.Read())
{
//GetInt32获得的是int类型
//GetInt64获得的是long类型（数据库中是bigint）
int age = reader.GetInt32(0);
// cmd.CommandText = "select age from T_Students where Name=‘杨中科‘";参数为查询结果的第几列
MessageBox.Show(age.ToString());
}
}
}
}
}
解决方法:
cmd.CommandText = "select age from T_Students where Name=@Name";
cmd.Parameters.AddWithValue("@Name",textName.Text);
或者(推荐)
cmd.CommandText = "select age from T_Students where Name=@Name";
cmd.Parameters.Add(new SqlParameter("@Name",textName.Text));
参数不能用来替换表名,select之类的关键字等
13.DataSet离线数据集
数据集的好处是降低数据库服务器的压力,编程也简单.应用实例: 重点!!!
private void button1_Click(object sender, RoutedEventArgs e)
{
using(SqlConnection conn=new SqlConnection(
@"Data Source=.\SQLEXPRESS; Initial Catalog=MyTest;User ID=sa;Password=123456"))
{
conn.Open();
using(SqlCommand cmd=conn.CreateCommand())
{
cmd.CommandText = "select * from T_Students where Age<@age";
cmd.Parameters.Add(new SqlParameter("@age",60));
//SqlDataAdapter 是一个帮我们把SqlCommand查询结果填充到DataSet中的类
//DataSet相当于本地的一个复杂集合（类似于List<>）
//哪怕是与数据库连接断开了DataSet中也还有数据
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet dataset = new DataSet();
adapter.Fill(dataset);//Fill则是执行并且把SqlCommand查询结果填充到DataSet中
DataTable table=dataset.Tables[0];
DataRowCollection rows = table.Rows;
for (int i = 0; i < rows.Count;i++ )
{
DataRow row=rows[i];
int age=(int)row["Age"];
string name=(string)row["Name"];
MessageBox.Show(name+","+age);
}
}
}
}
14.连接字符串放到配置文件中
a.添加→新建项→应用程序配置文件(项目根目录添加应用程序配置文件，名字叫做App.config)
App.config:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<connectionStrings>
<add name="dbConnStr" connectionString="Data Source=.\SQLEXPRESS; Initial Catalog=MyTest;User ID=sa;Password=123456" /> --App.config加节点，给add起一个name
</connectionStrings>
</configuration>
b.添加引用 --添加System.configuration的引用（理解为开发包）
c.程序中添加 ---就能使用System.configuration里面的ConfigurationManager类
string connStr = ConfigurationManager.ConnectionStrings["dbConnStr"].ConnectionString;
MessageBox.Show(connStr);
程序中所有用到连接字符串的地方可以替换
string connStr = ConfigurationManager.ConnectionStrings["dbConnStr"].ConnectionString;
//MessageBox.Show(connStr);
using(SqlConnection conn=new SqlConnection(
connStr))
{
conn.Open();
using(SqlCommand cmd=conn.CreateCommand())
{
程序开发完成，拷给用户使用时，只需要拷贝Debug或者Release下的.exe和.exe.config文件给用户即可，若用户的数据库服务器地址变更只需要
更改.exe.config文件即可（asp.net里面只有web.config）
15．自己动手写SqlHelper
a.添加类SqlHelper
书写方法ExecuteNonQuery和ExecuteScalar代码如下：
class SqlHelper
{
private static string connStr = ConfigurationManager.ConnectionStrings["dbConnStr"].ConnectionString;
public static int ExecuteNonQuery(string sql)
{
using(SqlConnection conn= new SqlConnection(connStr))
{
conn.Open();
using(SqlCommand cmd=conn.CreateCommand())
{
cmd.CommandText = sql;
return cmd.ExecuteNonQuery();
}
}
}
private static object ExecuteScalar(string sql)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
using(SqlCommand cmd=conn.CreateCommand())
{
cmd.CommandText = sql;
return cmd.ExecuteScalar();
}
}
}
//只用来执行查询结果比较小的sql DataSet返回值有且只有一个Table[0]（表格）
public static DataSet ExecuteDataSet(string sql)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
using(SqlCommand cmd=conn.CreateCommand())
{
cmd.CommandText = sql;
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet dataset = new DataSet();
adapter.Fill(dataset);
return dataset;
}
}
}
}
使用情况如下：
private void button2_Click(object sender, RoutedEventArgs e)
{
SqlHelper.ExecuteNonQuery("insert into T_Students (Name,Age) values (‘小黄‘,50)");
MessageBox.Show("执行成功");
}
private void button2_Click(object sender, RoutedEventArgs e)
{
//SqlHelper.ExecuteNonQuery("insert into T_Students (Name,Age) values (‘小黄‘,50)");
DataSet ds = SqlHelper.ExecuteDataSet("select * from T_Students where Age>20 ");
foreach(DataRow row in ds.Tables[0].Rows)
{
string name=(string)row["Name"];
MessageBox.Show(name);
}
MessageBox.Show("执行成功");
}
16． SqlHelper的改进（1）
改进1：
因为DataSet只用来执行查询结果比较小的sql DataSet返回值有且只有一个Table[0]（表格）故在使用时可直接这样书写代码使用
public static DataTable ExecuteDataTable(string sql)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
using(SqlCommand cmd=conn.CreateCommand())
{
cmd.CommandText = sql;
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet dataset = new DataSet();
adapter.Fill(dataset);
return dataset.Tables[0];
}
}
}
使用代码如下：
private void button2_Click(object sender, RoutedEventArgs e)
{
//SqlHelper.ExecuteNonQuery("insert into T_Students (Name,Age) values (‘小黄‘,50)");
DataTable table = SqlHelper.ExecuteDataTable("select * from T_Students where Age>20 ");
foreach(DataRow row in table.Rows)
{
string name=(string)row["Name"];
MessageBox.Show(name);
}
MessageBox.Show("执行成功");
}
17． SqlHelper的改进（2）----SqlHelper添加解决注入漏洞攻击（参数法）
ExecuteNonQuery：
private static string connStr = ConfigurationManager.ConnectionStrings["dbConnStr"].ConnectionString;
public static int ExecuteNonQuery(string sql,SqlParameter[] parameters)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
//foreach (SqlParameter param in parameters)
//{
// cmd.Parameters.Add(param);
//}或者下面的形式
cmd.Parameters.AddRange(parameters);
return cmd.ExecuteNonQuery();
}
}
}
private static object ExecuteScalar(string sql,SqlParameter[] parameters)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
cmd.Parameters.AddRange(parameters);
return cmd.ExecuteScalar();
}
}
}
//只用来执行查询结果比较小的sql
public static DataTable ExecuteDataTable(string sql,SqlParameter[] parameters)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
cmd.Parameters.AddRange(parameters);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet dataset = new DataSet();
adapter.Fill(dataset);
return dataset.Tables[0];
}
}
}
使用时代码如下：
private void button2_Click(object sender, RoutedEventArgs e)
{
//SqlHelper.ExecuteNonQuery("insert into T_Students (Name,Age) values (‘小黄‘,50)");
DataTable table = SqlHelper.ExecuteDataTable("select * from T_Students where Age>20 "
,new SqlParameter[0]);//传个长度为0的数组
foreach(DataRow row in table.Rows)
{
string name=(string)row["Name"];
MessageBox.Show(name);
}
MessageBox.Show("执行成功");
}
private void button2_Click(object sender, RoutedEventArgs e)
{
//SqlHelper.ExecuteNonQuery("insert into T_Students (Name,Age) values (‘小黄‘,50)");
//DataTable table = SqlHelper.ExecuteDataTable("select * from T_Students where Age>20 "
// ,new SqlParameter[0]);//若有参数，代码如下：
DataTable table = SqlHelper.ExecuteDataTable("select * from T_Students where Age>@Age and Name=@Name "
, new SqlParameter[] { new SqlParameter("@Age", 20), new SqlParameter("@Name", "小明") });
foreach(DataRow row in table.Rows)
{
string name=(string)row["Name"];
MessageBox.Show(name);
}
MessageBox.Show("执行成功");
}
18.长度可变参数复习
static void Main(string[] args)
{
int i = Sum(new int[]{3,5,10,20});
Console.WriteLine(i);
Console.ReadKey();
}
static int Sum(int[] sums)
{
int count = 0;
foreach(int i in sums)
{
count += i;
}
return count;
}
static void Main(string[] args)
{
int i = Sum("aaa",new int[]{3,5,10,20});
Console.WriteLine(i);
//调用者就像有无穷多个参数一样
int j = Sum("aaa", 3, 90, 99, 333, 666);
Console.WriteLine(j);
Console.ReadKey();
}
//程序编写者把多个参数当成打包的数组用
static int Sum(string s,params int[] sums) //params指定该参数为可变长度的参数，并且，params指定的参数必须为函数的最后一个参数
{
int count = 0;
foreach(int i in sums)
{
count += i;
}
return count;
}
则SqlHelper也可简化为：（版本3）重点!!!
private static string connStr = ConfigurationManager.ConnectionStrings["dbConnStr"].ConnectionString;
public static int ExecuteNonQuery(string sql, params SqlParameter[] parameters)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
//foreach (SqlParameter param in parameters)
//{
// cmd.Parameters.Add(param);
//}或者下面的形式
cmd.Parameters.AddRange(parameters);
return cmd.ExecuteNonQuery();
}
}
}
private static object ExecuteScalar(string sql, params SqlParameter[] parameters)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
cmd.Parameters.AddRange(parameters);
return cmd.ExecuteScalar();
}
}
}
//只用来执行查询结果比较小的sql
public static DataTable ExecuteDataTable(string sql, params SqlParameter[] parameters)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
cmd.Parameters.AddRange(parameters);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet dataset = new DataSet();
adapter.Fill(dataset);
return dataset.Tables[0];
}
}
}
用法如下：
private void button2_Click(object sender, RoutedEventArgs e)
{
//SqlHelper.ExecuteNonQuery("insert into T_Students (Name,Age) values (‘小黄‘,50)");
//DataTable table = SqlHelper.ExecuteDataTable("select * from T_Students where Age>20 "
// ,new SqlParameter[0]);//若有参数，代码如下：
//DataTable table = SqlHelper.ExecuteDataTable("select * from T_Students where Age>@Age "
// , new SqlParameter[]{new SqlParameter("@Age",20)});
SqlHelper.ExecuteNonQuery("insert into T_Students (Name,Age) values (‘我心狂野‘,50)");
DataTable table= SqlHelper.ExecuteDataTable("select * from T_Students where Age=@Age or Name=@Name"
, new SqlParameter("@Age", 50), new SqlParameter("@Name", "杨中科"));
foreach(DataRow row in table.Rows)
{
string name=(string)row["Name"];
MessageBox.Show(name);
}
MessageBox.Show("执行成功");
}
19.作业
练习1.用户界面中进行登录判断，输错三次禁止登录（半小时），用数据库记录ErrorTimes
namespace 练习1_登录
{
/// <summary>
/// LoginWindow.xaml 的交互逻辑
/// </summary>
public partial class LoginWindow : Window
{
public LoginWindow()
{
InitializeComponent();
}
private void btnLonin_Click(object sender, RoutedEventArgs e)
{
string name = txtName.Text;
string password = pwdUser.Password;
DataTable table = SqlHelper.ExecuteReader("select * from T_Users where Name=@Name",
new SqlParameter("@Name",name));
//if(name.Length<=0)
//{
// MessageBox.Show("请输入用户名！");
// return;
//}
if(table.Rows.Count<=0)
{
MessageBox.Show("请输入用户名！");
return;
}
if (password.Length <= 0)
{
MessageBox.Show("请输入密码！");
return;
}
if(table.Rows.Count>1)
{
MessageBox.Show("用户名重复");
}
DataRow row=table.Rows[0];
string dbPassword=(string)row["Password"];
int errortimes = (int)row["ErrorTimes"];
if (errortimes >= 3)
{
MessageBox.Show("密码错误次数超过3次上限，此用户将被锁定半小时！");
return;
}
if (dbPassword != pwdUser.Password)
{
MessageBox.Show("密码错误！");
SqlHelper.ExecuteNonQuery("update T_Users Set ErrorTimes=ErrorTimes+1 where Name=@Name"
,new SqlParameter("@Name",name));
}
else
{
MessageBox.Show("登录成功！");
SqlHelper.ExecuteNonQuery("update T_Users Set ErrorTimes=0 Name=@Name"
, new SqlParameter("@Name", name));
}
}
private void btnCancel_Click(object sender, RoutedEventArgs e)
{
Close();
}
private void Window_Loaded(object sender, RoutedEventArgs e)
{
SqlHelper.ExecuteNonQuery("update T_Users Set ErrorTimes=0");
}
}
}
练习2:数据导入：从文本文件导入用户信息，易错点：Parameter的重复添加。File.ReadAllLines()
private void button2_Click(object sender, RoutedEventArgs e)
{
OpenFileDialog ofd = new OpenFileDialog();//声明一个打开文件对话框类的对象ofd
ofd.Filter="文本文件|*.txt"; //设置过滤器，只能打开.txt文件
if(ofd.ShowDialog()!=true) //若打开文件ofd.ShowDialog()返回值为true否则为false
{
return;
}
string filename = ofd.FileName; //取得其打开文件的filename
//File.ReadLines是把文件一次读取到string集合中
IEnumerable<string> lines = File.ReadLines(filename);//将打开文件的所有行放到一个string数组中
foreach (string line in lines)//遍历每一行
{
string[] segs = line.Split(‘|‘);//每一行按照‘|’分割，并导入到数据库中
string name=segs[0];
string age=segs[1];
SqlHelper.ExecuteNonQuery("insert into T_UserMess (Name,Age) values (@Name,@Age)"
, new SqlParameter("@Name", name), new SqlParameter("@Age", Convert.ToInt32(age)));
}
MessageBox.Show("导入成功！成功导入"+lines.Count()+"条数据！");
}
此时能成功导入，但导入时的数据中文存在乱码：（读取导入都可能会存在乱码）数据保存和读取的格式不一致造成乱码
解决方法如下：
IEnumerable<string> lines = File.ReadLines(filename,Encoding.Default);
File.ReadLines()添加参数确定编码为操作系统默认的编码方式 File.ReadLines没有第二个参数的重载是采用的UTF8编码
练习3:数据导出：将用户信息导出到文本文件File.WriteAllLines()
private void button3_Click(object sender, RoutedEventArgs e)
{
DataTable ds = SqlHelper.ExecuteReader("select * from T_Students where Habit is not null");
string[] Message = { };
foreach(DataRow row in ds.Rows)
{
//string name = (string)row["Name"];
//int age = (int)row["Age"];
//string habit = (string)row["Habit"];
//int id = (int)row["Id"];
string[] message = { (string)row["Name"], row["Age"].ToString(), (string)row["Habit"], row["Id"].ToString() };
File.WriteAllLines
(@"E:\asp.net movie\documents\s5_ado.net\Ado.net文件操作\output.txt"
, message, Encoding.Default);
}
MessageBox.Show("执行成功！");
}
问题: string[] message = { (string)row["Name"], row["Age"].ToString(), (string)row["Habit"], row["Id"].ToString() };
会将之前的output.txt中的文件覆盖，最后导致文件中只有row的最后一个值
练习4：案例省市选择程序
ComboBox的显示值：Items.Add的参数是Object类型，也就是可以放任意数据类型的数据，可以设置DisplayMember属性设定显示的属性，
通过SelectedItem属性取得到就是选择的条目对应的对象。例子。疑问：取出来的是Object，怎么能转换为对应的类型？
变量名只是“标签”。显示值和实际值对象不一样，在ASP.Net中也有相同的东西
创建一个ProvinceItem类，将数据填充在这个对象中添加到ComboBox中
步骤1：由脚本建立表，导入数据
步骤2：设计窗口样式
ListBox省名字为lbProv；ListBox市名字为lbCity；ListBox县名字为lbCounty
步骤3：从数据库中取数据并由数据绑定的方法添加到listProv中
用于存放数据库表中字段的类定义如下:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace 复习
{
class Area
{
public int AreaId { get; set; }
public string AreaName { get; set; }
}
}
DataTable table = SqlHelper.ExecuteReader("select * from AreaFull where AreaPid=0");
List<Area> listProv = new List<Area>();//List<>用于存放多个Area的对象
foreach (DataRow datarow in table.Rows)
{
Area area = new Area();//声明一个Area的对象area
area.AreaId = (int)datarow["AreaId"];
area.AreaName = (string)datarow["AreaName"];
listProv.Add(area);//将这个Area的对象添加到List<Area>中
}
lbProv.ItemsSource = listProv;//指定lbProv.ItemsSource为listProv（数据绑定）
并且：xaml中需要添加：DisplayMemberPath="AreaName"指出lbProv的显示内容为：AreaName
步骤4：当lbProv的SelectionChanged事件被触发时，lbCity响应代码如下：
private void lbProv_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
Area areaProv = (Area)lbProv.SelectedItem;//获得选中省的对象
//获得所有AreaPid等于选中省的AreaId的值，也就是省的下属市
DataTable dtCity=SqlHelper.ExecuteReader("select * from AreaFull where AreaPid=@Pid"
,new SqlParameter("@Pid",areaProv.AreaId));
List<Area> listCity = new List<Area>();//List<>用于存放多个Area的对象
foreach (DataRow datarow in dtCity.Rows)
{
Area areaCity = new Area();//声明一个Area的对象area
areaCity.AreaId = (int)datarow["AreaId"];
areaCity.AreaName = (string)datarow["AreaName"];
listCity.Add(areaCity);//将这个Area的对象添加到List<Area>中
}
lbCity.ItemsSource = listCity;//指定lbProv.ItemsSource为listProv（数据绑定）
}
同理：lbCity_SelectionChanged事件被触发时，lbCounty响应代码如下：
private void lbCity_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
Area areaCounty = (Area)lbCity.SelectedItem;//获得选中市的对象
//获得所有AreaPid等于选中省的AreaId的值，也就是市的下属县
DataTable dtCounty = SqlHelper.ExecuteReader("select * from AreaFull where AreaPid=@Pid"
, new SqlParameter("@Pid", areaCounty.AreaId));
List<Area> listCounty = new List<Area>();//List<>用于存放多个Area的对象
foreach (DataRow datarow in dtCounty.Rows)
{
Area area = new Area();//声明一个Area的对象area
area.AreaId = (int)datarow["AreaId"];
area.AreaName = (string)datarow["AreaName"];
listCounty.Add(area);//将这个Area的对象添加到List<Area>中
}
lbCounty.ItemsSource = listCounty;//指定lbProv.ItemsSource为listProv（数据绑定）
}
遗留问题:
当省市县全部显示时,省的选择变了程序会报错
练习5：案例手机号码归属地
设计数据库表:写程序把文本文件中的数据导入数据库，开发搜索功能，到58.com上找手机号测试
步骤1: 将手机号码归属地信息文本文件导入到数据库
OpenFileDialog ofd = new OpenFileDialog();//将手机号码归属地信息文本文件导入到数据库
//（创建打开文件对话框类的一个对象ofd）
ofd.Filter="文本文件|*.txt";//设置文件过滤器
if(ofd.ShowDialog()!=true)
{
return;
}
string[] lines= File.ReadLines(ofd.FileName,Encoding.Default).ToArray();
//File.ReadLines的返回值为IEnumerable类型，现将其使用ToArray()转换为string类型的数组
for (int i = 1; i < lines.Count();i++ )
{
string line=lines[i];
string[] strs= line.Split(‘\t‘);//vs里‘\t‘为制表符，将文本文件中的每一行信息以‘\t‘指标符分割
//获得每一段的数据
string StartTelNum = strs[0];
string City = strs[1];
string TelType = strs[2];
//string TelArea = strs[2];
City = City.Trim(‘"‘);
TelType = TelType.Trim(‘"‘);
SqlHelper.ExecuteNonQuery(
"insert into T_TenNum (StartTelNum,TelType,TelArea) values (@StartTelNum,@TelType,@TelArea)"
, new SqlParameter("@StartTelNum", StartTelNum), new SqlParameter("@TelType", TelType)
, new SqlParameter("@TelArea", City));
}
导入过程:1.每插入一行都要打开连接,执行再关闭,效率低,设计只打开连接一次,执行,再关闭连接;
解决方法猜想:不用SqlHelper,手动写打开连接，创建命令，执行，关闭连接过程，这样所有的插入数据库中15w+条数据的插入过程看似在一个连接中完成
以此提高效率
string connStr=ConfigurationManager.ConnectionStrings["dbConnStr"].ConnectionString;
using(SqlConnection conn=new SqlConnection(connStr))
{
conn.Open();
using(SqlCommand cmd=conn.CreateCommand())
{
cmd.CommandText =
@"insert into T_TelNum (StartTelNum,TelType,TelArea) values
(@StartTelNum,@TelType,@TelArea)";
cmd.Parameters.Add(new SqlParameter("@StartTelNum", StartTelNum));
cmd.Parameters.Add(new SqlParameter("@TelType", TelType));
cmd.Parameters.Add(new SqlParameter("@TelArea", City));
cmd.ExecuteNonQuery();
}
}
验证过程：
string[] lines= File.ReadLines(ofd.FileName,Encoding.Default).ToArray();
//File.ReadLines的返回值为IEnumerable类型，现将其使用ToArray()转换为string类型的数组
DateTime starttime = DateTime.Now;
for (int i = 1; i < lines.Count();i++ )
{
string line=lines[i];
string[] strs= line.Split(‘\t‘);//vs里‘\t‘为制表符，将文本文件中的每一行信息以‘\t‘指标符分割
//获得每一段的数据
string StartTelNum = strs[0];
string City = strs[1];
string TelType = strs[2];
//string TelArea = strs[2];
City = City.Trim(‘"‘);
TelType = TelType.Trim(‘"‘);
//方法一：使用SqlHelper所有插入中每一条数据均需要打开，执行，关闭连接的过程：耗时
//SqlHelper.ExecuteNonQuery(
// "insert into T_TelNum (StartTelNum,TelType,TelArea) values (@StartTelNum,@TelType,@TelArea)"
// , new SqlParameter("@StartTelNum", StartTelNum), new SqlParameter("@TelType", TelType)
// , new SqlParameter("@TelArea", City));
string connStr=ConfigurationManager.ConnectionStrings["dbConnStr"].ConnectionString;
using(SqlConnection conn=new SqlConnection(connStr))
{
conn.Open();
using(SqlCommand cmd=conn.CreateCommand())
{
cmd.CommandText =
@"insert into T_TelNum (StartTelNum,TelType,TelArea) values
(@StartTelNum,@TelType,@TelArea)";
cmd.Parameters.Add(new SqlParameter("@StartTelNum", StartTelNum));
cmd.Parameters.Add(new SqlParameter("@TelType", TelType));
cmd.Parameters.Add(new SqlParameter("@TelArea", City));
cmd.ExecuteNonQuery();
}
}
double ellapse =((DateTime.Now - starttime).TotalSeconds*lines.Length)/(i+1);
//DateTime.Now - starttime为TimeSpan类型的
注意：编程中计算问题应该先乘后除，因为计算机不能精确表示小数
此时估测时间为2574秒，时间并没有减少：原因Ado.Net中数据库连接池机制：Ado.Ne会尽可能的复用连接池中的连接
解决方法：（数据库批量提交SqlBulkCopy）
OpenFileDialog ofd = new OpenFileDialog();//将手机号码归属地信息文本文件导入到数据库
//（创建打开文件对话框类的一个对象ofd）
ofd.Filter="文本文件|*.txt";//设置文件过滤器
if(ofd.ShowDialog()!=true)
{
return;
}
string[] lines= File.ReadLines(ofd.FileName,Encoding.Default).ToArray();
//File.ReadLines的返回值为IEnumerable类型，现将其使用ToArray()转换为string类型的数组
DateTime starttime = DateTime.Now;
string connStr = ConfigurationManager.ConnectionStrings["dbConnStr"].ConnectionString;
//SqlBulkCopy
DataTable table = new DataTable();
table.Columns.Add("StartTelNum");
table.Columns.Add("City");
table.Columns.Add("TelType");
for (int i = 1; i < lines.Count(); i++)
{
string line = lines[i];
string[] strs = line.Split(‘\t‘);//vs里‘\t‘为制表符，将文本文件中的每一行信息以‘\t‘指标符分割
//获得每一段的数据
string StartTelNum = strs[0];
string City = strs[1];
string TelType = strs[2];
//string TelArea = strs[2];
City = City.Trim(‘"‘);
TelType = TelType.Trim(‘"‘);
DataRow row = table.NewRow();//创建一个DataRow对象
row["StartTelNum"] = StartTelNum;//一定要在最开始创建 table.Columns添加列
row["City"] = City;
row["TelType"] = TelType;
table.Rows.Add(row);//NewRow()只是创建，没有加入
}
using (SqlBulkCopy bulkCopy=new SqlBulkCopy(connStr))
{
bulkCopy.DestinationTableName = "T_TelNum";
bulkCopy.ColumnMappings.Add("StartTelNum", "StartTelNum");//第一个参数表示这一列在本地DataTable的列名字
//第二个参数表示在数据库表中的列名字，添加DataTable中列名和数据库表中列名的映射
bulkCopy.ColumnMappings.Add("TelType", "TelType");
bulkCopy.ColumnMappings.Add("City", "TelArea");
bulkCopy.WriteToServer(table);
}
TimeSpan ts = DateTime.Now - starttime;
MessageBox.Show("数据导入成功！共耗时"+ts.TotalSeconds.ToString()+"秒。");
步骤说明：
1. 分析并取出文本文件的数据，存放于string[] lines这个数组中
2. 创建DataTable类的一个对象，数据没经过SqlBulkCopy复制进数据库之前先存放在DataTable中
3. 分析文本文件中的数据
for (int i = 1; i < lines.Count(); i++)
{
string line = lines[i];
string[] strs = line.Split(‘\t‘);//vs里‘\t‘为制表符，将文本文件中的每一行信息以‘\t‘指标符分割
//获得每一段的数据
string StartTelNum = strs[0];
string City = strs[1];
string TelType = strs[2];
//string TelArea = strs[2];
City = City.Trim(‘"‘);
TelType = TelType.Trim(‘"‘);
DataRow row = table.NewRow();//创建一个DataRow对象
row["StartTelNum"] = StartTelNum;//一定要在最开始创建 table.Columns添加列
row["City"] = City;
row["TelType"] = TelType;
table.Rows.Add(row);//NewRow()只是创建，没有加入
}
4. 将DataTable的对象table的数据复制到SqlBulkCopy的一个对象bulkCopy中，并由该对象导入到数据库
using (SqlBulkCopy bulkCopy=new SqlBulkCopy(connStr))
{
bulkCopy.DestinationTableName = "T_TelNum";
bulkCopy.ColumnMappings.Add("StartTelNum", "StartTelNum");//第一个参数表示这一列在本地DataTable的列名字
//第二个参数表示在数据库表中的列名字，添加DataTable中列名和数据库表中列名的映射
bulkCopy.ColumnMappings.Add("TelType", "TelType");
bulkCopy.ColumnMappings.Add("City", "TelArea");
bulkCopy.WriteToServer(table);
}
2.应该有个进度条显示
遗留问题：
OpenFileDialog ofd = new OpenFileDialog();//将手机号码归属地信息文本文件导入到数据库
//（创建打开文件对话框类的一个对象ofd）
ofd.Filter="文本文件|*.txt";//设置文件过滤器
if(ofd.ShowDialog()!=true)
{
return;
}
string[] lines= File.ReadLines(ofd.FileName,Encoding.Default).ToArray();
//File.ReadLines的返回值为IEnumerable类型，现将其使用ToArray()转换为string类型的数组
DateTime starttime = DateTime.Now;
string connStr = ConfigurationManager.ConnectionStrings["dbConnStr"].ConnectionString;
//SqlBulkCopy
DataTable table = new DataTable();
table.Columns.Add("StartTelNum");
table.Columns.Add("City");
table.Columns.Add("TelType");
for (int i = 1; i < lines.Count(); i++)
{
string line = lines[i];
string[] strs = line.Split(‘\t‘);//vs里‘\t‘为制表符，将文本文件中的每一行信息以‘\t‘指标符分割
//获得每一段的数据
string StartTelNum = strs[0];
string City = strs[1];
string TelType = strs[2];
//string TelArea = strs[2];
City = City.Trim(‘"‘);
TelType = TelType.Trim(‘"‘);
DataRow row = table.NewRow();//创建一个DataRow对象
row["StartTelNum"] = StartTelNum;//一定要在最开始创建 table.Columns添加列
row["City"] = City;
row["TelType"] = TelType;
table.Rows.Add(row);//NewRow()只是创建，没有加入
pgBar.Maximum = lines.Count(); ----进度条不动！
pgBar.Minimum = 0;
pgBar.Value = http://www.mamicode.com/i;
}
using (SqlBulkCopy bulkCopy=new SqlBulkCopy(connStr))
{
bulkCopy.DestinationTableName = "T_TelNum";
bulkCopy.ColumnMappings.Add("StartTelNum", "StartTelNum");//第一个参数表示这一列在本地DataTable的列名字
//第二个参数表示在数据库表中的列名字，添加DataTable中列名和数据库表中列名的映射
bulkCopy.ColumnMappings.Add("TelType", "TelType");
bulkCopy.ColumnMappings.Add("City", "TelArea");
bulkCopy.WriteToServer(table);
}
TimeSpan ts = DateTime.Now - starttime;
MessageBox.Show("数据导入成功！共耗时"+ts.TotalSeconds.ToString()+"秒。");
20.数据库重可空数据类型的处理---在正式项目中对于NULL的处理很谨慎
a.若声明了一张表T_Test1
其中三个字段均可为空通过vs中ado.net插入数据时：
当执行 insert into T_Test1 (Name,Age,Height) values (‘yzk’,20,180)时
数据库中结果为
执行insert into T_Test1 (Age,Height) values (20,180)时
而如果由数据库中直接插入时：
对比可以发现，同样没有输入Name但通过ado.net和数据库本身的操作所得到的结果却不一致
b.在数据库中执行
select * from T_Test where Name=NULL
和：select * from T_Test where Name is NULL
查询结果也不相同
Null可以理解为不知道，‘=’的使用不能说明两个不知道的东西就 一定相同，所以不能这么用
如何解决数据库和ado.net中不一致问题
需求：如果没有输入姓名：则姓名为NULL而不是为空字符串；
如果没有输入年龄：则年龄为NULL而不是为空字符串；
如果没有输入身高：则身高为NULL而不是为空字符串
if (txtName.Text.Length<=0)
{
txtName.Text = null;
}
SqlHelper.ExecuteNonQuery(@"insert into T_Test1 (Name,Age,Height)
values (@Name,@Age,@Height)", new SqlParameter("@Name",txtName.Text)
, new SqlParameter("@Age", txtAge.Text)
, new SqlParameter("@Height", txtHeight.Text));
执行结果仍然查询空字符串
若使用下面代码插入数据库
string name = txtName.Text;
string age = txtAge.Text;
string height = txtHeight.Text;
if (name.Length <= 0)
{
name = null;
}
SqlHelper.ExecuteNonQuery(@"insert into T_Test1 (Name,Age,Height)
values (@Name,@Age,@Height)"
, new SqlParameter("@Name", name)
, new SqlParameter("@Age", age)
, new SqlParameter("@Height", height));
执行时
在SqlParameter("@Name", name)中第二个参数SqlParameter("@Name", name)如果为null，则表示没有提供参数的值，会报错
解决方法DbNull：DbNull.Value用来表示数据库中的NULL
string name = txtName.Text;
string age = txtAge.Text;
string height = txtHeight.Text;
object objName;
if (name.Length <= 0)
{
objName = DBNull.Value;
}
else
{
objName = name;
}
object objAge;
if (age.Length <= 0)
{
objAge = DBNull.Value;
}
else
{
objAge = age;
}
object objHeight;
if (height.Length <= 0)
{
objHeight = DBNull.Value;
}
else
{
objHeight = height;
}
SqlHelper.ExecuteNonQuery(@"insert into T_Test1 (Name,Age,Height)
values (@Name,@Age,@Height)"
, new SqlParameter("@Name", objName)
, new SqlParameter("@Age", objAge)
, new SqlParameter("@Height", objHeight));
从数据库中读取时Null的问题
private void button2_Click(object sender, RoutedEventArgs e)
{
DataTable table = SqlHelper.ExecuteReader("select * from T_Test2 where Id=2");
DataRow row=table.Rows[0];
string name=(string)row["Name"];
int age=(int)row["Age"];
int height=(int)row["height"];
MessageBox.Show("姓名："+name+" 年龄："+age+" 身高："+height);
}
若刚好读取的行有数据为Null则会报错
解决方案：
private void button2_Click(object sender, RoutedEventArgs e)
{
DataTable table = SqlHelper.ExecuteReader("select * from T_Test2 where Id=3");
DataRow row=table.Rows[0];
//读取的值如果在数据库中的是NULL，则返回DBNull.Value
string name;
if (row["Name"] == DBNull.Value)
{
name = null;
}
else
{
name=(string)row["Name"];
}
int? age;
if (row["Age"] == DBNull.Value)
{
age = null;
}
else
{
age= (int)row["Name"];
}
int? height;
if (row["Height"] == DBNull.Value)
{
height = null;
}
else
{
height = (int)row["Height"];
}
MessageBox.Show("姓名："+name+" 年龄："+age+" 身高："+height);
}
21.三层架构
之前是直接在界面（UI）中写SQL（XXX.xaml.cs），对于大的项目这样做很难维护，而且复用性不强，
三层架构是企业开发中常用的设计模式，把数据库的访问、业务逻辑、界面分离
初学者直接学习三层架构会比较难，因此这次用精简的三层架构，只用DAL（Data Access Layer）数据访问层，把数据库访问封装到DAL中，UI调用DAL
，原则“UI中不出现SQL”
编写Model
DAL常用封装：ToModel、ListAll（对于大数据量的数据不要提供、而是提供条件搜索）、GetByld、DeleteByld、Update、AddNew
例1.
在DAL文件中写SQL语句:界面(UI)层不出现SQL语句
namespace 练习1_登录
{
class DALthreelayers
{
public static int GetCount()
{
return (int)SqlHelper.ExecuteScalar("select count(*) from T_Users");
}
public static void DelectById(int temp)
{
int max = (int)SqlHelper.ExecuteScalar("select count(*) from T_Users where Id=@Id"
,new SqlParameter("@Id",temp));
if(max<=0)
{
MessageBox.Show("数据非法，请重新输入！");
return;
}
SqlHelper.ExecuteNonQuery("delete from T_Users where Id=@Id",new SqlParameter("@Id",temp));
MessageBox.Show("已经删除了Id="+temp+"的数据！");
}
}
}
调用时，直接调用函数即可
private void button1_Click(object sender, RoutedEventArgs e)
{
MessageBox.Show(DALthreelayers.GetCount().ToString());
}
private void button2_Click(object sender, RoutedEventArgs e)
{
DALthreelayers.DelectById(Convert.ToInt32(txtId.Text));
}
如果列非常多，参数就会非常多，要把参数封装到Model类中，比如Insert方法中参数较多
a. 所谓的Model其实就是一个类，注意可空字段的定义
class Students
{
public long Id { get; set; }
public string Name { get; set; }
public int Age { get; set; }
public string Habit { get; set; }
}
b. 在StudentsDAL.cs中写方法
public static void Insert(Students student)
{
SqlHelper.ExecuteNonQuery("insert into T_Students (Name,Age,Habit) values (@Name,@Age,@Habit)"
,new SqlParameter("@Name",student.Name)
, new SqlParameter("@Age", student.Age)
, new SqlParameter("@Habit", student.Habit));
}
c. 调用方法过程如下
private void button3_Click(object sender, RoutedEventArgs e)
{
Students student = new Students();
student.Name = "令狐冲";
student.Age = 15;
student.Habit = "打麻将";
StudentsDAL.Insert(student);
}
d. 若不给可空字段赋值---会报错
解决方法：
public static void Insert(Students student)
{
object Habit;
if (student.Habit == null)
{
Habit = DBNull.Value;
}
else
{
Habit = student.Habit;
}
SqlHelper.ExecuteNonQuery("insert into T_Students (Name,Age,Habit) values (@Name,@Age,@Habit)"
,new SqlParameter("@Name",student.Name)
, new SqlParameter("@Age", student.Age)
//, new SqlParameter("@Habit", student.Habit));
, new SqlParameter("@Habit", Habit));
MessageBox.Show("执行插入成功！");
}
简化过程：
public static object ToDBValue(object value)
{
if (value =http://www.mamicode.com/= null)
{
return DBNull.Value;
}
else
{
return value;
}
}
public static void Insert(Students student)
{
//object Habit;
//if (student.Habit == null)
//{
// Habit = DBNull.Value;
//}
//else
//{
// Habit = student.Habit;
//}
//SqlHelper.ExecuteNonQuery("insert into T_Students (Name,Age,Habit) values (@Name,@Age,@Habit)"
// ,new SqlParameter("@Name",student.Name)
// , new SqlParameter("@Age", student.Age)
// //, new SqlParameter("@Habit", student.Habit));
// , new SqlParameter("@Habit", Habit));
//MessageBox.Show("执行插入成功！");
//以上代码简化方法
SqlHelper.ExecuteNonQuery("insert into T_Students (Name,Age,Habit) values (@Name,@Age,@Habit)"
, new SqlParameter("@Name", student.Name)
, new SqlParameter("@Age", student.Age)
, new SqlParameter("@Habit", ToDBValue(student.Habit)));
//, new SqlParameter("@Habit", Habit));
MessageBox.Show("执行插入成功！");
}
若有很多个字段为可空数据类型，此封装优势明显
e. 书写按Id查找的方法：
public static object FromDBValue(object value)
{
if (value =http://www.mamicode.com/= DBNull.Value)
{
return null;
}
else
{
return value;
}
}
public static Students GetById(long id)
{
DataTable table= SqlHelper.ExecuteReader("select * from T_Students where Id=@Id"
,new SqlParameter("@Id",id));
if(table.Rows.Count<=0)
{
return null;
}
else if (table.Rows.Count > 1)
{
throw new Exception("Id重复！");
}
else
{
DataRow row=table.Rows[0];
Students student = new Students();
student.Id=(long)row["Id"];
student.Name=(string)row["Name"];
//if(row["Habit"]==DBNull.Value)
//{
// student.Habit = null;
//} //同样封装一个方法
//student.Habit=(string)row["Habit"];
student.Habit = (string)FromDBValue(row["Habit"]);
return student;
}
}
调用代码：
private void button4_Click(object sender, RoutedEventArgs e)
{
int searchId = Convert.ToInt32(txtSearch.Text);
Students student= StudentsDAL.GetById(searchId);
MessageBox.Show("查找到student的姓名："+student.Name+" 年龄："+student.Age+" 爱好："+student.Habit);
}
22.三层架构与创建客户表复习
a.将ToDbNull和FromDbNull两个方法封装到SqlHelper中
public static object FromDbValue(object value)
{
if (value =http://www.mamicode.com/= DBNull.Value)
{
return null;
}
else
{
return value;
}
}
public static object ToDbValue(object value)
{
if (value =http://www.mamicode.com/= null)
{
return DBNull.Value;
}
else
{
return value;
}
}
b.Customer的Model代码如下：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace 复习
{
public class Customer
{
public long Id { get; set; }
public string Name { get; set; }
public DateTime? BirthDay { get; set; }
public string Address { get; set; }
public string TelNum { get; set; }
public int CustLevel { get; set; }
}
}
c.CustomerDAL层方法代码如下：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Windows;
namespace 复习
{
public class CustomerDAL
{
private Customer ToCustomer(DataRow row)
{
Customer cust = new Customer();
cust.Id = (long)row["Id"];
cust.Name = (string)row["Name"];
cust.BirthDay = (DateTime?)SqlHelper.FromDbValue(row["BirthDay"]);
cust.Address = (string)row["Address"];
cust.TelNum = (string)row["TelNum"];
cust.CustLevel = (int)row["CustLevel"];
//return cust;
return cust;
}
//根据Id来获取GetById、Update、GetAll、GetPageData(今后会用到)
//Insert（插入新数据）
public Customer GetById(long id)
{
DataTable dt= SqlHelper.ExecuteReader("select * from T_Customer where Id=@Id",
new SqlParameter("@Id",id));
if(dt.Rows.Count<=0)
{
return null;
}
else if (dt.Rows.Count > 1)
{
throw new Exception("严重错误，查出多条数据！");
}
else
{
DataRow row = dt.Rows[0];
return ToCustomer(row);
//Customer cust = new Customer();
//cust.Id=(long)row["Id"];
//cust.Name = (string)row["Name"];
//cust.BirthDay = (DateTime?)SqlHelper.FromDbValue(row["BirthDay"]);
//cust.Address = (string)row["Address"];
//cust.TelNum = (string)row["TelNum"];
//cust.CustLevel = (int)row["CustLevel"];
//return cust;
}
}
public void DeleteById(long id)
{
DataTable table= SqlHelper.ExecuteReader("select * from T_Customer where Id=@Id"
,new SqlParameter("@Id",id));
if(table.Rows.Count<=0)
{
MessageBox.Show("Id为" + id + "的数据行不存在！");
return;
}
SqlHelper.ExecuteNonQuery("delete from T_Customer where Id=@Id"
,new SqlParameter("@Id",id));
MessageBox.Show("Id为"+id+"已经成功删除！");
}
public void Insert(Customer customer)
{
SqlHelper.ExecuteNonQuery(@"INSERT INTO [T_Customer]
([Name]
,[BirthDay]
,[Address]
,[TelNum]
,[CustLevel])
VALUES
(@Name,@BirthDay,@Address,@TelNum,@CustLevel)"
, new SqlParameter("@Name", customer.Name)
, new SqlParameter("@BirthDay", SqlHelper.ToDbValue(customer.BirthDay))
, new SqlParameter("@Address", customer.Address)
, new SqlParameter("@TelNum", customer.TelNum)
, new SqlParameter("@CustLevel", customer.CustLevel));
}
public void Update(Customer customer)
{
SqlHelper.ExecuteNonQuery(@"UPDATE [T_Customer]
SET [Name] = @Name
,[BirthDay] = @BirthDay
,[Address] = @Address
,[TelNum] = @TelNum
,[CustLevel] = @CustLevel
WHERE Id=@Id"
, new SqlParameter("@Name", customer.Name)
, new SqlParameter("@BirthDay", SqlHelper.ToDbValue(customer.BirthDay))
, new SqlParameter("@Address", customer.Address)
, new SqlParameter("@TelNum", customer.TelNum)
, new SqlParameter("@CustLevel", customer.CustLevel));
, new SqlParameter("@Id", customer.Id));
}
public Customer[] GetAll()
{
DataTable table = SqlHelper.ExecuteReader("select * from T_Customer");
Customer[] customers=new Customer[table.Rows.Count];
for (int i = 0; i < table.Rows.Count;i++ )
{
DataRow row=table.Rows[i];
Customer customer = new Customer();
customer.Id = (long)row["Id"];
customer.Name = (string)row["Name"];
customer.BirthDay = (DateTime?)SqlHelper.FromDbValue(row["BirthDay"]);
customer.Address = (string)row["Address"];
customer.TelNum = (string)row["TelNum"];
customer.CustLevel = (int)row["CustLevel"];
customers[i] = customer;
}
return customers;
}
}
}
d.ListUI和EditUI
ListUI:
XAML文件：
<Window x:Class="复习.CustomerListUI"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
Title="CustomerListUI" Height="300" Width="516"
WindowState="Maximized" Loaded="Window_Loaded"> --最大化窗口
<Grid >
<DockPanel>
<ToolBar DockPanel.Dock="Top" Height="30">
<Button Name="btnNew">
<Image Source="Images/new.png"></Image>
</Button >
<Button Name="btnEdit">
<Image Source="Images/edit1.png"></Image>
</Button>
<Button Name="btnDelete">
<Image Source="Images/delete.png"></Image>
</Button>
</ToolBar>
<DataGrid AutoGenerateColumns="False" Name="gridCustomers" IsReadOnly="True" DockPanel.Dock="Top"> -- 去掉自动生成列 只读
<DataGrid.Columns>
<DataGridTextColumn Header="姓名" Width="100" Binding="{Binding Name}"></DataGridTextColumn>
<DataGridTextColumn Header="生日" Width="100" Binding="{Binding BirthDay}"></DataGridTextColumn>
<DataGridTextColumn Header="电话" Width="200" Binding="{Binding TelNum}"></DataGridTextColumn>
<DataGridTextColumn Header="地址" Width="300" Binding="{Binding Address}"></DataGridTextColumn>
<DataGridTextColumn Header="客户等级" Width="100" Binding="{Binding CustLevel}"></DataGridTextColumn>
</DataGrid.Columns>
</DataGrid>
</DockPanel>
</Grid>
</Window>
窗口加载事件被触发时：
private void Window_Loaded(object sender, RoutedEventArgs e)
{
CustomerDAL dal = new CustomerDAL();
gridCustomers.ItemsSource = dal.GetAll();
}
增加三个按钮的功能：
删除：
private void btnDelete_Click(object sender, RoutedEventArgs e)
{
Customer customer = (Customer)gridCustomers.SelectedItem;
if(customer==null)
{
MessageBox.Show("请选择要删除的行！");
return;
}
if (MessageBox.Show("是否真的确认删除数据？", "提醒", MessageBoxButton.YesNo) == MessageBoxResult.Yes)
{
new CustomerDAL().DeleteById(customer.Id);
//删除完成后要刷新页面，不然页面不会变化
LoadData();
}
}
public void LoadData()
{
CustomerDAL dal = new CustomerDAL();
gridCustomers.ItemsSource = dal.GetAll();
}
CustomerEditUI:
CustomerEditUI需要知道是Edit还是Update:
书写一标识函数:
public partial class CustomerEditUI : Window
{
public bool IsInsert { get; set; }
//表示是新增数据还是修改数据
public long EditingId { get; set; }
//若是编辑数据，需要知道编辑数据的Id
V1.0:编辑和新增
private void btnNew_Click(object sender, RoutedEventArgs e)
{
CustomerEditUI editUI = new CustomerEditUI();
editUI.IsInsert = true;
if (editUI.ShowDialog() == true)
//editUI.ShowDialog()为bool?类型并且只有在打开的窗口关闭后才有返回值
{
LoadData();
}
}
private void btnEdit_Click(object sender, RoutedEventArgs e)
{
Customer customer=(Customer)gridCustomers.SelectedItem;
if(customer==null)
{
MessageBox.Show("请选择要编辑的行！");
return;
}
CustomerEditUI editUI = new CustomerEditUI();
editUI.IsInsert = false;
editUI.EditingId = customer.Id;
if(editUI.ShowDialog() == true)
{
LoadData();
}
}
在CustomerEditUI窗口加载时做如下操作
private void Window_Loaded(object sender, RoutedEventArgs e)
{
if (IsInsert)//插入
{
//txtLevel.Text = "2";
Customer cust = new Customer();
cust.CustLevel = 2;
grid.DataContext = cust;
}
else//编辑修改
{
//要编辑的数据从数据库中拿出来，
//可以把Customer直接在ListUI传进来，这样还省得查一次数据库
Customer customer = new CustomerDAL().GetById(EditingId);
//但是，一个原则，窗口传值、容器中存储值尽量放简单数据类型
//填充到界面上，显示编辑前的值
txtName.Text = customer.Name;
txtTelNum.Text = customer.TelNum;
txtAddress.Text = customer.Address;
txtLevel.Text = customer.CustLevel.ToString();
dpBirthDay.SelectedDate = customer.BirthDay;
}
}
当点击了CustomerEditUI窗口的保存按钮时:
private void btnSave_Click(object sender, RoutedEventArgs e)
{
if (IsInsert)
{
Customer customer = new Customer();
customer.Address = txtAddress.Text;
customer.BirthDay = dpBirthDay.SelectedDate;
customer.CustLevel = Convert.ToInt32(txtLevel.Text);
customer.Name = txtName.Text;
customer.TelNum = txtTelNum.Text;
new CustomerDAL().Insert(customer);//插入数据库
}
else
{
//先从数据库重查询旧的值，然后把界面中的值设置到旧对象上，然后update
CustomerDAL dal = new CustomerDAL();
Customer customer = dal.GetById(EditingId);
customer.Address = txtAddress.Text;
customer.Name = txtName.Text;
customer.BirthDay = dpBirthDay.SelectedDate;
customer.CustLevel = Convert.ToInt32(txtLevel.Text);
customer.TelNum = txtTelNum.Text;
dal.Update(customer);
}
DialogResult = true;
}
当点击取消按钮时:
private void btnCancel_Click(object sender, RoutedEventArgs e)
{
DialogResult = false;
}
![clip_image081[1]](/d/file/p/2024/07/08/e17ec4045c394eec385b001c761822dd.jpg "clip_image081[1]")
通过数据绑定简化EditUI
数据绑定:
若先绑定而采用//Customer customer = new CustomerDAL().GetById(EditingId);
//txtName.Text = customer.Name;
//txtTelNum.Text = customer.TelNum;
//txtAddress.Text = customer.Address;
//txtLevel.Text = customer.CustLevel.ToString();
//dpBirthDay.SelectedDate = customer.BirthDay;
这种方法给编辑室控件的属性赋值的话,不会成功!
<TextBox Text="{Binding Name}" Height="25" HorizontalAlignment="Left" Margin="95,29,0,0" Name="txtName" VerticalAlignment="Top" Width="120" />
<TextBox Text="{Binding TelNum}" Height="25" HorizontalAlignment="Left" Margin="349,32,0,0" Name="txtTelNum" VerticalAlignment="Top" Width="120" />
<TextBox Text="{Binding CustLevel}" Height="25" HorizontalAlignment="Left" Margin="349,78,0,0" Name="txtLevel" VerticalAlignment="Top" Width="120" />
<TextBox Text="{Binding Address}" Height="25" HorizontalAlignment="Left" Margin="120,122,0,0" Name="txtAddress" VerticalAlignment="Top" Width="337" />
<DatePicker SelectedDate="{Binding BirthDay}" Height="25" HorizontalAlignment="Left" Margin="111,77,0,0" Name="dpBirthDay" VerticalAlignment="Top" Width="115" />
给Grid取名为grid
<Grid Name="grid">
<TextBlock Height="23" HorizontalAlignment="Left"
对于CustomerEditUI的窗口加载方法:
private void Window_Loaded(object sender, RoutedEventArgs e)
{
if (IsInsert)
{
//txtLevel.Text = "2";
Customer cust = new Customer();
cust.CustLevel = 2;
grid.DataContext = cust;
}
else
{
//Customer customer = new CustomerDAL().GetById(EditingId);
//txtName.Text = customer.Name;
//txtTelNum.Text = customer.TelNum;
//txtAddress.Text = customer.Address;
//txtLevel.Text = customer.CustLevel.ToString();
//dpBirthDay.SelectedDate = customer.BirthDay;
Customer customer = new CustomerDAL().GetById(EditingId);
grid.DataContext = customer;
}
}
对于数据插入Insert的简化:
private void btnSave_Click(object sender, RoutedEventArgs e)
{
if (IsInsert)
{
//Customer customer = new Customer();
//customer.Address = txtAddress.Text;
//customer.Name = txtName.Text;
//customer.BirthDay = dpBirthDay.SelectedDate;
//customer.CustLevel = Convert.ToInt32(txtLevel.Text);
//customer.TelNum = txtTelNum.Text;
Customer cust = (Customer)grid.DataContext;//grid.DataContext指向的Customer对象
//用户修改过程中，数据绑定会自动把界面的修改同步到Customer对象中
new CustomerDAL().Insert(cust);
}
else
{
//先从数据库中查询旧的值，然后把界面中的值设置到旧对象上
//Update
//CustomerDAL dal = new CustomerDAL();
//Customer customer = dal.GetById(EditingId);
//customer.Address = txtAddress.Text;
//customer.Name = txtName.Text;
//customer.TelNum = txtTelNum.Text;
//customer.CustLevel = Convert.ToInt32(txtLevel.Text);
//customer.BirthDay = dpBirthDay.SelectedDate;
Customer customer = (Customer)grid.DataContext;
CustomerDAL dal = new CustomerDAL();
dal.Update(customer);
}
DialogResult = true;
}
23.项目:
主键类型的选择自动增长的GUID
数据集中,解决主键重复的问题
在sql server中生成guid方法
select newid()
在.net中
Guid g2 = null;//Guid为值类型，不可以为空
private void btnGUIDTest_Click(object sender, RoutedEventArgs e)
{
Guid g1= Guid.NewGuid();
//Guid g2 = null;//Guid为值类型，不可以为空
}
在数据库中为uniqueidentifier类型
24.大公司团队有多少开发人员
初始项目：
项目图标，主窗口全屏，顶部菜单，窗口的Title
项目文件夹：图片放到Images下、Model和DAL建单独的项目、SqlHelper
操作员（Operator）：姓名、用户名、密码。建数据库（独立的用户名）
a.新建项目
一个解决方案下面可以有多个项目
b.Model：类库
c.DAL层
d.新建Images文件夹放图片资源（需为ICO）格式图片
项目→属性→
UI布局
Title="人力资源管理系统" Height="350" Width="525">
<Grid>
<DockPanel>
<Menu DockPanel.Dock="Top" Height="30">
<MenuItem Header="系统">
<MenuItem Header="操作员管理"></MenuItem>
</MenuItem>
</Menu>
<Image DockPanel.Dock="Bottom"></Image>
</DockPanel>
</Grid>
将SqlHelper放入DAL中
App.config放入UI层
建数据库
首先到根节点的”安全性”中创建一个用户,然后到数据库的节点下的”安全性”中也创建一个登录名,这样该用户只能访问指定数据库,安全性高
在数据库中新建表:
在Model中新建Operator类
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace HRMSys.Model
{
public class Operator
{
public Guid Id { get; set; }
public string UserName { get; set; }
public string Password { get; set; }
}
}
在DAL层中新建OperatorDAL
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using HRMSys.Model;
using HRMSys;
using System.Data.SqlClient;
namespace HRMSys.DAL
{
public class OperatorDAL
{
public void Insert(Operator op)
{
SqlHelper.ExecuteNonQuery(@"insert into T_Operator (Id,UserName,Password)
values (newid(),@UserName,@Password)"
,new SqlParameter("@UserName",op.UserName)
,new SqlParameter("@Password",op.Password));
}
}
private Operator ToOperator(DataRow row)
{
Operator op = new Operator();
op.Id = (Guid)row["Id"];
op.UserName = (string)row["UserName"];
op.Password = (string)row["Password"];
return op;
}
public Operator GetByUserName(string userName)
{
DataTable table = SqlHelper.ExecuteReader("select * from T_Operator where UserName=@UserName",
new SqlParameter("@UserName", userName));
if (table.Rows.Count <= 0)
{
return null;
}
else if (table.Rows.Count > 1)
{
throw new Exception("存在重名用户！");
}
else
{
return ToOperator(table.Rows[0]);
}
}
}
测试代码如下:
private void miOperatorMgr_Click(object sender, RoutedEventArgs e)
{
OperatorDAL dal = new OperatorDAL();
Operator op=new Operator();
op.UserName = "yzk";
op.Password = "123";
dal.Insert(op);
MessageBox.Show("插入执行成功！");
}
PS:App.config必须放到UI项目中DAL层通过ConfigurationManager可以读取到项目中app.config中的配置信息
引用关系：DAL引用Model UI项目引用DAL和Model
不要把密码明文保存：加密 无法“解密”的“加密“-散列算法
不可逆向的算法MD5、DES、SHA
MD5算法
public static string GetMD5(string sDataIn)
{
MD5CryptoServiceProvider md5 = new MD5CryptoServiceProvider();
byte[] bytValue, bytHash;
bytValue = http://www.mamicode.com/System.Text.Encoding.UTF8.GetBytes(sDataIn);
bytHash = md5.ComputeHash(bytValue);
md5.Clear();
string sTemp = "";
for (int i = 0; i < bytHash.Length; i++)
{
sTemp += bytHash[i].ToString("X").PadLeft(2, ‘0‘);
}
return sTemp.ToLower();
}
加密以明文保存的问题：容易泄露用户常用密码。2012年的互联网密码泄露风波。
MD5算法是一种散列（HASH）算法（摘要算法、指纹算法），不是一种加密算法。任何长度的任意内容都可以用MD5计算出散列值，介绍工具（MD5计算器）
MD5算法不可逆，也就是说只能得到对应内容的MD5值，无法由MD5值反推内用。对于不同的内容产生的MD5值的概率非常非常非常低
Password字段保存用户输入的密码的MD5值，这样系统管理员、黑客也不知道用户的密码是什么，也就避免了用户的其他系统密码被利用的问题（思考：密码用明文存储有哪些缺点？）
判断密码正确性的方法：计算用户输入的密码的MD5值，与数据库存储的MD5值进行比较，如果相等则认为密码正确，为什么很多网站只有密码重置，没有找回密码的功能？
应用：计算文件的MD5值来检验文件没有被篡改过。
MD5加盐
新加一个用户,密码为加盐MD5,用作测试用
private void miOperatorMgr_Click(object sender, RoutedEventArgs e)
{
string s = "123";
string md5 = CommonHelper.GetMD5(s+"love?P3@9");//加盐
Operator op = new Operator();
op.Password = md5;
op.UserName = "itcast";
OperatorDAL dal = new OperatorDAL();
dal.Insert(op);
MessageBox.Show("插入执行成功！");
}
登录时对比用户输入密码加盐后的MD5值与之前插入时加盐MD5值比较
若相等则密码正确,否则,密码错误
private void btnLonin_Click(object sender, RoutedEventArgs e)
{
string username = txtUserName.Text;
string pwd = pwdPassword.Password;
Operator op = new OperatorDAL().GetByUserName(username);
if (op == null)
{
MessageBox.Show("用户名或密码错误！");
}
else
{
string dbMd5 = op.Password;//数据库中存储的MD5
string mymmd5 = CommonHelper.GetMD5(pwd + "love?P3@9");
if (dbMd5 == mymmd5)
{
MessageBox.Show("登录成功！");
}
else
{
MessageBox.Show("用户名或密码错误！");
}
}
25.b\s与c\s项目的区别与联系
C/S是Client/Server的缩写。服务器通常采用高性能的PC、工作站或小型机，并采用大型数据库系统，如Oracle、Sybase、Informix或 SQL Server。客户端需要安装专用的客户端软件。
B/S是Brower/Server的缩写，客户机上只要安装一个浏览器（Browser），如Netscape Navigator或Internet Explorer，服务器安装Oracle、Sybase、Informix或 SQL Server等数据库。在这种结构下，用户界面完全通过WWW浏览器实现，一部分事务逻辑在前端实现，但是主要事务逻辑在服务器端实现。浏览器通过Web Server 同数据库进行数据交互。
C/S 与 B/S 区别：
1．硬件环境不同:
C/S 一般建立在专用的网络上, 小范围里的网络环境, 局域网之间再通过专门服务器提供连接和数据交换服务.
B/S 建立在广域网之上的, 不必是专门的网络硬件环境,例与电话上网, 租用设备. 信息自己管理. 有比C/S更强的适应范围, 一般只要有操作系统和浏览器就行
2．对安全要求不同
C/S 一般面向相对固定的用户群, 对信息安全的控制能力很强. 一般高度机密的信息系统采用C/S 结构适宜. 可以通过B/S发布部分可公开信息.
B/S 建立在广域网之上, 对安全的控制能力相对弱, 可能面向不可知的用户。
3．对程序架构不同
C/S 程序可以更加注重流程, 可以对权限多层次校验, 对系统运行速度可以较少考虑.
B/S 对安全以及访问速度的多重的考虑, 建立在需要更加优化的基础之上. 比C/S有更高的要求 B/S结构的程序架构是发展的趋势, 从MS的.Net系列的BizTalk 2000 Exchange 2000等, 全面支持网络的构件搭建的系统. SUN 和IBM推的JavaBean 构件技术等,使 B/S更加成熟.
4．软件重用不同
C/S 程序可以不可避免的整体性考虑, 构件的重用性不如在B/S要求下的构件的重用性好.
B/S 对的多重结构,要求构件相对独立的功能. 能够相对较好的重用.就入买来的餐桌可以再利用,而不是做在墙上的石头桌子
5．系统维护不同
C/S 程序由于整体性, 必须整体考察, 处理出现的问题以及系统升级. 升级难. 可能是再做一个全新的系统
B/S 构件组成,方面构件个别的更换,实现系统的无缝升级. 系统维护开销减到最小.用户从网上自己下载安装就可以实现升级.
6．处理问题不同
C/S 程序可以处理用户面固定, 并且在相同区域, 安全要求高需求, 与操作系统相关. 应该都是相同的系统
B/S 建立在广域网上, 面向不同的用户群, 分散地域, 这是C/S无法作到的. 与操作系统平台关系最小.
7．用户接口不同
C/S 多是建立的Window平台上,表现方法有限,对程序员普遍要求较高
B/S 建立在浏览器上, 有更加丰富和生动的表现方式与用户交流. 并且大部分难度减低,减低开发成本.
8．信息流不同
C/S 程序一般是典型的中央集权的机械式处理, 交互性相对低
B/S 信息流向可变化, B-B B-C B-G等信息、流向的变化, 更像交易中心。
管理员界面
<Window x:Class="HRMSys.UI.MD5LoginWindow"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
Title="MD5LoginWindow" Height="180" Width="348" WindowStartupLocation="CenterScreen" ResizeMode="NoResize" Loaded="Window_Loaded">
<Grid>
<TextBlock Height="23" HorizontalAlignment="Left" Margin="53,12,0,0" Name="textBlock1" Text="用户名" VerticalAlignment="Top" />
<TextBlock Height="23" HorizontalAlignment="Left" Margin="53,0,0,64" Name="textBlock2" Text="密 码" VerticalAlignment="Bottom" />
<TextBox Height="26" HorizontalAlignment="Right" Margin="0,12,80,0" Name="txtUserName" VerticalAlignment="Top" Width="120" Text="明日" />
<Button Content="登录" Height="23" HorizontalAlignment="Left" Margin="40,113,0,0" Name="btnLogin" VerticalAlignment="Top" Width="75" Click="btnLogin_Click" />
<Button Content="取消" Height="23" HorizontalAlignment="Left" Margin="181,113,0,0" Name="btnCancel" VerticalAlignment="Top" Width="75" Click="btnCancel_Click" />
<PasswordBox Height="23" HorizontalAlignment="Left" Margin="144,54,0,0" Name="pwdPossword" VerticalAlignment="Top" Width="120" Password="123456" />
</Grid>
</Window>
namespace HRMSys.UI
{
/// <summary>
/// MD5LoginWindow.xaml 的交互逻辑
/// </summary>
public partial class MD5LoginWindow : Window
{
public MD5LoginWindow()
{
InitializeComponent();
}
public static int ErrorTimes { get; set; }//设置错误标志属性
private void btnLogin_Click(object sender, RoutedEventArgs e)
{
string username = txtUserName.Text;
string password = pwdPossword.Password;
string myguid = CommonHelper.GetMD5(password+"love@ty");
Operator op = new OperatorDAL().GetByUserName(username);
if (op == null)
{
MessageBox.Show("用户名不存在！");
//return;
}
else
{
string dbMd5 = op.Password;
if (myguid == dbMd5)
{
//MessageBox.Show("登录成功！");
DialogResult = true;
}
else
{
MessageBox.Show("用户名或密码错误！");
ErrorTimes += 1;
if(ErrorTimes>=3)
{
MessageBox.Show("用户密码输错次数超过上限，该用户将被限制登录！");
DialogResult = false;
}
}
}
}
private void btnCancel_Click(object sender, RoutedEventArgs e)
{
DialogResult = false;
}
private void Window_Loaded(object sender, RoutedEventArgs e)
{
}
}
}
:
第六季: ADO.NET项目实战(接第五季最后)
1.数据的软删除
在主窗口加载时加载登陆框,若登陆成功显示主窗口,否则退出,无法登陆
private void Window_Loaded(object sender, RoutedEventArgs e)
{
MD5LoginWindow loginWin = new MD5LoginWindow();
if(loginWin.ShowDialog()!=true)
{
Application.Current.Shutdown();//程序退出
}
}
登陆界面中点击了登陆按钮判断逻辑
public static int ErrorTimes { get; set; }//设置错误标志属性
private void btnLogin_Click(object sender, RoutedEventArgs e)
{
string username = txtUserName.Text;
string password = pwdPossword.Password;
string myguid = CommonHelper.GetMD5(password+"love@ty");
Operator op = new OperatorDAL().GetByUserName(username);
if (op == null)
{
MessageBox.Show("用户名不存在！");
//return;
}
else
{
string dbMd5 = op.Password;
if (myguid == dbMd5)
{
//MessageBox.Show("登录成功！");
DialogResult = true;
}
else
{
MessageBox.Show("用户名或密码错误！");
ErrorTimes += 1;
if(ErrorTimes>=3)
{
MessageBox.Show("用户密码输错次数超过上限，该用户将被限制登录！");
DialogResult = false;
}
}
}
}
概念:什么叫软删除
一个表在引用另外一张表的时候一定要引用主键,作者表:Id,Name,IdCardNo 书籍表：Id，Name，Price，Author
数据的“软删除”，客户说的话不一定是你理解的话，把数据真正删除在某些时候会有问题：“把员工删除了难道把员工填写的工资单都删除了吗？”只是“离职”
而已，部门不是删除，而是“停止”IsDelete字段。
增加IsDelete字段
Model中增加IsDelete字段
public bool IsDelete { get; set; }
DAL层里面增加该字段
public void Insert(Operator op)//插入一个Operator
{
SqlHelper.ExecuteNonQuery(@"insert into T_Operator (Id,UserName,Password,IsDelete)
values (newid(),@UserName,@Password,0)"
, new SqlParameter("@UserName", op.UserName)
, new SqlParameter("@Password", op.Password));//bit类型在sql中表示为0、1在.net中为true、false
}
private Operator ToOperator(DataRow row)//给一行数据，分析出一个Operator
{
Operator op = new Operator();
op.Id = (Guid)row["Id"];
op.UserName = (string)row["UserName"];
op.Password = (string)row["Password"];
op.IsDelete = (bool)row["IsDelete"];
return op;
}
public Operator GetByUserName(string userName)//根据用户名，返回一个Operator类的对象
{
DataTable table = SqlHelper.ExecuteReader("select * from T_Operator where UserName=@UserName",
new SqlParameter("@UserName", userName));
if (table.Rows.Count <= 0)
{
return null;
}
else if (table.Rows.Count > 1)
{
throw new Exception("存在重名用户！");
}
else
{
return ToOperator(table.Rows[0]);
}
}
在Dal层中增加软删除方法和列出所有方法
public void DeleteById(Guid id)
{
//软删除
SqlHelper.ExecuteNonQuery("Update T_Operator set IsDelete=1 where Id=@Id"
,new SqlParameter("@Id",id));
}
public Operator[] ListAll()
{
DataTable table= SqlHelper.ExecuteReader("select * from T_Operator where IsDelete=0");
Operator[] operators=new Operator[table.Rows.Count];
for (int i = 0; i < table.Rows.Count;i++ )
{
operators[i] = ToOperator(table.Rows[i]);
}
return operators;
}
由于一个项目中可能有很多个窗口，如果窗口全部放在UI根目录下则不便于管理于是新建窗口文件夹SystemMgr
再增加两个字段
则对应的Operator的Model和DAL中对应要改
public string RealName { get; set; }
public bool IsLocked { get; set; }
Model中：
public string RealName { get; set; }
public bool IsLocked { get; set; }
public class OperatorDAL
{
public void Insert(Operator op)//插入一个Operator
{
SqlHelper.ExecuteNonQuery(@"insert into T_Operator (Id,UserName,Password,IsDelete,RealName,IsLocked)
values (newid(),@UserName,@Password,0,@RealName,0)"
, new SqlParameter("@UserName", op.UserName)
, new SqlParameter("@Password", op.Password)
, new SqlParameter("@RealName", op.RealName));
}
private Operator ToOperator(DataRow row)//给一行数据，分析出一个Operator
{
Operator op = new Operator();
op.Id = (Guid)row["Id"];
op.UserName = (string)row["UserName"];
op.Password = (string)row["Password"];
op.IsDelete = (bool)row["IsDelete"];
op.RealName = (string)row["RealName"];
return op;
}
public Operator GetByUserName(string userName)//根据用户名，返回一个Operator类的对象
{
DataTable table = SqlHelper.ExecuteReader("select * from T_Operator where UserName=@UserName",
new SqlParameter("@UserName", userName));
if (table.Rows.Count <= 0)
{
return null;
}
else if (table.Rows.Count > 1)
{
throw new Exception("存在重名用户！");
}
else
{
return ToOperator(table.Rows[0]);
}
}
public void DeleteById(Guid id)
{
//软删除
SqlHelper.ExecuteNonQuery("Update T_Operator set IsDelete=1 where Id=@Id"
,new SqlParameter("@Id",id));
}
public Operator[] ListAll()
{
DataTable table= SqlHelper.ExecuteReader("select * from T_Operator where IsDelete=0");
Operator[] operators=new Operator[table.Rows.Count];
for (int i = 0; i < table.Rows.Count;i++ )
{
operators[i] = ToOperator(table.Rows[i]);
}
return operators;
}
<DockPanel>
<ToolBar DockPanel.Dock="Top" Height="50">
<Button Name="btnNew">
<Image Source="../Images/new.png"></Image> --相对路径
</Button>
窗口加载时判断逻辑
private void Window_Loaded(object sender, RoutedEventArgs e)
{
if (IsInsert)
{
}
else
{
OperatorDAL dal = new OperatorDAL();
Operator op= dal.GetById(EditingId);
txtUserName.Text = op.UserName;
txtRealName.Text = op.RealName;
//pwdPassword.Password = op.Password;
//编辑的处理逻辑，编辑时不显示密码，若用户需要重置密码时才填密码，并且此时点保存时会重置
}
}
点击按钮时判断逻辑
public bool IsInsert { get; set; }
public Guid EditingId { get; set; }
public OperatorEditUIWindow()
{
InitializeComponent();
}
private void btnSave_Click(object sender, RoutedEventArgs e)
{
if (IsInsert)//插入时不进行密码空的判断
{
}
else
{
Operator op = new Operator();
if (pwdPassword.Password.Length <= 0)//密码为空，则保留现有的密码不动
{
}
else//如果密码不为空，则把密码重置为用户输入的密码
{
}
}
}
private void btnCanlel_Click(object sender, RoutedEventArgs e)
{
DialogResult = false;
}
DAL中新增更新方法
public void Update(Guid id,string userName,string realName)
{
//不更新密码
SqlHelper.ExecuteNonQuery(@"update T_Operator set UserName=@UserName,RealName=@RealName
where Id=@Id"
,new SqlParameter("@UserName",userName)
, new SqlParameter("@RealName", realName)
, new SqlParameter("@Id", id));
}
public void Update(Guid id, string userName, string realName,string password)
{
//更新密码
SqlHelper.ExecuteNonQuery(@"update T_Operator set UserName=@UserName,RealName=@RealName
,Password=@Password
where Id=@Id"
, new SqlParameter("@UserName", userName)
, new SqlParameter("@RealName", realName)
, new SqlParameter("@Id", id)
, new SqlParameter("@Password", password));
}
Md5密码加盐放到配置文件App.config中
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<connectionStrings>
<add name="dbConnStr"
connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=HRMSYSDB;User ID=hrmsa;Password=123456"/>
</connectionStrings>
<appSettings>
<add key="passwordSalt" value=http://www.mamicode.com/"love@ty"/>
</appSettings>
</configuration>
在CommonHelper中需要书写方法读出Md5盐的值
public static string GetPasswordSalt()
{
string salt= ConfigurationManager.AppSettings["passwordSalt"];
return salt;
}
保存逻辑更改如下
private void btnSave_Click(object sender, RoutedEventArgs e)
{
if (IsInsert)//插入时不进行密码空的判断
{
Operator op = new Operator();
op.RealName = txtRealName.Text;
op.UserName = txtRealName.Text;
op.Password = CommonHelper.GetMD5(pwdPassword.Password+CommonHelper.GetPasswordSalt());
new OperatorDAL().Insert(op);
DialogResult = true;
}
else
{
Operator op = new Operator();
if (pwdPassword.Password.Length <= 0)//密码为空，则保留现有的密码不动
{
new OperatorDAL().Update(EditingId, txtUserName.Text, txtRealName.Text);
}
else//如果密码不为空，则把密码重置为用户输入的密码
{
string pwdMd5 = CommonHelper.GetMD5(pwdPassword.Password+CommonHelper.GetPasswordSalt());
new OperatorDAL().Update(EditingId,
txtUserName.Text, txtRealName.Text,pwdMd5);
}
DialogResult = true;
}
}
private void btnCanlel_Click(object sender, RoutedEventArgs e)
{
DialogResult = false;
}
当程序开发完成,拷给用户使用时,需要.DAL.dll .Model.dll文件 .exe文件 .exe.config文件拷贝给用户
ListUI的cs文件中加
private void btnNew_Click(object sender, RoutedEventArgs e)
{
OperatorEditUIWindow editUI = new OperatorEditUIWindow();
editUI.IsInsert = true;
if(editUI.ShowDialog()==true)
{
LoadData();
}
}
private void LoadData()
{
OperatorDAL dal = new OperatorDAL();
gridOperators.ItemsSource = dal.ListAll();
}
两个方法
点击编辑时：
private void btnEdit_Click(object sender, RoutedEventArgs e)
{
Operator op =(Operator)gridOperators.SelectedItem;
if(op==null)
{
MessageBox.Show("没有选中任何行！");
return;
}
OperatorEditUIWindow editUI = new OperatorEditUIWindow();
editUI.IsInsert = false;
if (editUI.ShowDialog() == true)
{
LoadData();
}
}
private void btnDelete_Click(object sender, RoutedEventArgs e)
{
Operator op = (Operator)gridOperators.SelectedItem;
if (op == null)
{
MessageBox.Show("没有选中任何行！");
return;
}
if (MessageBox.Show("是否真的要删除" + op.UserName + "这行数据？", "警告", MessageBoxButton.YesNo) == MessageBoxResult.Yes)
{
OperatorDAL dal = new OperatorDAL();
dal.DeleteById(op.Id);//软删除
LoadData();
}
}
private void Window_Loaded(object sender, RoutedEventArgs e)
{
LoadData();
}
遗留问题：
在editUI中txtUserName.Text无法被赋值，详情见下面截图，只有通过数据绑定才能赋值
额…………什么都没改变，下次运行居然又可以了！！！
添加记住用户名密码功能：
MainWindow.xaml
namespace HRMSys.UI
{
/// <summary>
/// MainWindow.xaml 的交互逻辑
/// </summary>
public partial class MainWindow : Window
{
public MainWindow()
{
InitializeComponent();
}
private void button1_Click(object sender, RoutedEventArgs e)
{
OperatorDAL dal = new OperatorDAL();
Operator op = new Operator();
op.Id = Guid.NewGuid();
op.UserName = "聂大杰";
op.Password = "888888";
dal.Insert(op);
MessageBox.Show("执行插入成功！");
}
private void button2_Click(object sender, RoutedEventArgs e)
{
MD5LoginWindow loginWin = new MD5LoginWindow();
loginWin.ShowDialog();
}
private void button3_Click(object sender, RoutedEventArgs e)
{
Operator op = new Operator();
op.Id = Guid.NewGuid();
op.UserName = "明日";
op.Password = CommonHelper.GetMD5("123456"+"love@ty");
new OperatorDAL().Insert(op);
MessageBox.Show("插入数据库姓名为明日，密码为：123456成功！");
}
private void Window_Loaded(object sender, RoutedEventArgs e)
{
MD5LoginWindow loginWin = new MD5LoginWindow();
if (loginWin.ShowDialog() != true)
{
Application.Current.Shutdown();//程序退出
}
else
{
MessageBox.Show("登录成功，welcome！");
}
}
private void MenuItem_Click(object sender, RoutedEventArgs e)
{
OperatorListUIWindow listuiWin = new OperatorListUIWindow();
listuiWin.ShowDialog();
}
private void MenuItem_Click_1(object sender, RoutedEventArgs e)
{
TestWindow textWin = new TestWindow();
textWin.ShowDialog();
}
private void MenuItem_Click_2(object sender, RoutedEventArgs e)
{
EmployeeListUIWindow listWin = new EmployeeListUIWindow();
listWin.ShowDialog();
}
private void MenuItem_Click_3(object sender, RoutedEventArgs e)
{
TestWindow testWin = new TestWindow();
testWin.ShowDialog();
}
}
}
2.基础数据管理
部门管理：Id、Name（部门名称）、IsDelete ---练习
婚姻状况、性别、学历、婚姻状况、政治面貌、这些值比较固定，
一般不需要编辑的都记录到T_IdName表中，通过一个Category字段来表示类别，再有一个Name字段
项目中，基础数据需要遵循国家标准
性别：〔GB/T 2261.1-2003〕
0未知性别
1男性
2女性
9未说明性别
新建model Dal
namespace HRMSys.Model
{
public class IdName
{
public Guid Id { get; set; }
public string Name { get; set; }
}
}
public class IdNameDAL
{
public IdName[] GetByCategory(string category)
{
DataTable table= SqlHelper.ExecuteReader("select Id,Name,Category from T_IdName where Category=@Category"
,new SqlParameter("@Category",category));
IdName[] items = new IdName[table.Rows.Count];
for (int i = 0; i < table.Rows.Count;i++ )
{
DataRow row=table.Rows[i];
IdName item = new IdName();
item.Id=(Guid)row["Id"];
item.Name=(string)row["Name"];
items[i] = item;
}
return items;
}
}
在测试窗口中放一个ComboBox displaymemberpath指定为Name在测试窗口加载时
private void Window_Loaded(object sender, RoutedEventArgs e)
{
comboBox1.ItemsSource = new IdNameDAL().GetByCategory("学历");
}
部门的数据表建立
Model
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace HRMSys.Model
{
public class Department
{
public Guid Id { get; set; }
public string Name { get; set; }
}
}
DAL
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using HRMSys.Model;
namespace HRMSys.DAL
{
public class DepartmentDAL
{
public IEnumerable<Department> ListAll()
{
List<Department> list = new List<Department>();
DataTable dt = SqlHelper.ExecuteReader("select * from T_Department where IsStopped=0");
foreach (DataRow row in dt.Rows)
{
Department dept = ToModel(row);
list.Add(dept);
}
return list;
}
private Department ToModel(DataRow row)//给一行数据，分析出一个Department，并返回
{
Department dept = new Department();
dept.Id = (Guid)row["Id"];
dept.Name = (string)row["Name"];
return dept;
}
public Department GetById(Guid id)
{
DataTable dt = SqlHelper.ExecuteReader("select * from T_Department where Id=@Id",
new SqlParameter("@Id", id));
if (dt.Rows.Count <= 0)
{
return null;
}
else
{
return ToModel(dt.Rows[0]);
}
}
public void Update(Guid id, string name)
{
SqlHelper.ExecuteNonQuery(@"Update T_Department Set Name=@Name where Id=@Id",
new SqlParameter("@Name", name), new SqlParameter("@Id", id));
}
public void Insert(string name)
{
SqlHelper.ExecuteNonQuery(@"Insert Into T_Department(Id,Name,IsStopped) values(newid(),@Name,0)",
new SqlParameter("@Name", name));
}
public void DeleteById(Guid id)
{
SqlHelper.ExecuteNonQuery(@"Update T_Department Set IsStopped=1 where Id=@Id",
new SqlParameter("@Id", id));
}
}
}
员工管理
有些字段”看起来像数字”但是不能建为Int类型,如:身份证号码,电话号码,员工编号,导出到excel如果数字太短会显示科学计数法
外键的存在会降低Sql语言的执行效率,所以一般不建真正的外键
EmployeeModel:
public class Employee
{
public Guid Id
{
get;
set;
}
/// <summary>
/// 工号
/// </summary>
public string Number
{
get;
set;
}
public string Name
{
get;
set;
}
/// <summary>
/// 性别Id
/// </summary>
public Guid GenderId
{
get;
set;
}
//因为图片如果一次性读取到Model中会非常耗内存，因此需要的时候再去单独读取，不在Model中建字段
/// <summary>
/// 出生日期
/// </summary>
public DateTime BirthDay
{
get;
set;
}
/// <summary>
/// 入职日期
/// </summary>
public DateTime InDate
{
get;
set;
}
/// <summary>
/// 婚姻状态Id
/// </summary>
public Guid MarriageId
{
get;
set;
}
/// <summary>
/// 政治面貌Id
/// </summary>
public Guid PartyStatusId
{
get;
set;
}
/// <summary>
/// 民族
/// </summary>
public string Nationality
{
get;
set;
}
/// <summary>
/// 籍贯
/// </summary>
public string NativeAddr
{
get;
set;
}
/// <summary>
/// 教育程度Id
/// </summary>
public Guid EducationId
{
get;
set;
}
/// <summary>
/// 专业
/// </summary>
public string Major
{
get;
set;
}
/// <summary>
/// 毕业院校
/// </summary>
public string School
{
get;
set;
}
/// <summary>
/// 地址
/// </summary>
public string Address
{
get;
set;
}
/// <summary>
/// 基本工资
/// </summary>
public int BaseSalary
{
get;
set;
}
/// <summary>
/// </summary>
public string Email
{
get;
set;
}
/// <summary>
/// 有效身份证号
/// </summary>
public string IdNum
{
get;
set;
}
/// <summary>
/// 联系电话
/// </summary>
public string TelNum
{
get;
set;
}
/// <summary>
/// 紧急联系人信息
/// </summary>
public string EmergencyContact
{
get;
set;
}
/// <summary>
/// 部门Id
/// </summary>
public Guid DepartmentId
{
get;
set;
}
/// <summary>
/// 职位
/// </summary>
public string Position
{
get;
set;
}
/// <summary>
/// 合同起始时间
/// </summary>
public DateTime ContractStartDay
{
get;
set;
}
/// <summary>
/// 合同到期时间
/// </summary>
public DateTime ContractEndDay
{
get;
set;
}
/// <summary>
/// 简历
/// </summary>
public string Resume
{
get;
set;
}
/// <summary>
/// 备注
/// </summary>
public string Remarks
{
get;
set;
}
public byte[] Photo
{
get;
set;
}
}
EmployeeDAL
public class EmployeeDAL
{
public Employee ToModel(DataRow row)
{
Employee employee = new Employee();
employee.Address = (string)row["Address"];
employee.BaseSalary = (int)row["BaseSalary"];
employee.BirthDay = (DateTime)row["BirthDay"];
employee.ContractEndDay = (DateTime)row["ContractEndDay"];
employee.ContractStartDay = (DateTime)row["ContractStartDay"];
employee.DepartmentId = (Guid)row["DepartmentId"];
employee.EducationId = (Guid)row["EducationId"];
employee.Email = (string)row["Email"];
employee.EmergencyContact = (string)SqlHelper.FromDBNull(row["EmergencyContact"]);
employee.GenderId = (Guid)row["GenderId"];
employee.Id = (Guid)row["Id"];
employee.IdNum = (string)row["IdNum"];
employee.InDate = (DateTime)row["InDate"];
employee.Major = (string)row["Major"];
employee.MarriageId = (Guid)row["MarriageId"];
employee.Name = (string)row["Name"];
employee.Nationality = (string)row["Nationality"];
employee.NativeAddr = (string)row["NativeAddr"];
employee.Number = (string)row["Number"];
employee.PartyStatusId = (Guid)row["PartyStatusId"];
employee.Position = (string)row["Position"];
employee.Remarks = (string)SqlHelper.FromDBNull(row["Remarks"]);
employee.Resume = (string)SqlHelper.FromDBNull(row["Resume"]);
employee.School = (string)SqlHelper.FromDBNull(row["School"]);
employee.TelNum = (string)row["TelNum"];
//todo:如果员工非常多，那么Photo会增加内存占用
employee.Photo = (byte[])SqlHelper.FromDBNull(row["Photo"]);
return employee;
}
public Employee[] ListAll()
{
DataTable table = SqlHelper.ExecuteReader("select * from T_Employee");
Employee[] items = new Employee[table.Rows.Count];
for (int i = 0; i < table.Rows.Count; i++)
{
Employee employee = ToModel(table.Rows[i]);
items[i] = employee;
}
return items;
}
public Employee GetById(Guid id)
{
DataTable table = SqlHelper.ExecuteReader("select * from T_Employee where Id=@Id",
new SqlParameter("@Id", id));
if (table.Rows.Count <= 0)
{
return null;
}
else if (table.Rows.Count == 1)
{
return ToModel(table.Rows[0]);
}
else
{
throw new Exception();
}
}
public void Insert(Employee employee)
{
SqlHelper.ExecuteNonQuery(@"INSERT INTO [T_Employee]
([Id],[Number],[Name],[BirthDay],[InDate],[MarriageId],[PartyStatusId],[Nationality]
,[NativeAddr],[EducationId],[Major],[School],[Address],[BaseSalary],[Email]
,[IdNum],[TelNum],[EmergencyContact],[DepartmentId],[Position],[ContractStartDay]
,[ContractEndDay],[Resume],[Remarks],[IsStopped],[GenderId],Photo)
VALUES(newid(),@Number,@Name,@BirthDay,@InDate,@MarriageId,@PartyStatusId,@Nationality
,@NativeAddr,@EducationId,@Major,@School,@Address,@BaseSalary,@Email
,@IdNum,@TelNum,@EmergencyContact,@DepartmentId,@Position,@ContractStartDay
,@ContractEndDay,@Resume,@Remarks,0,@GenderId,@Photo)", new SqlParameter("@Number", employee.Number)
, new SqlParameter("@Name", employee.Name)
, new SqlParameter("@BirthDay", employee.BirthDay)
, new SqlParameter("@InDate", employee.InDate)
, new SqlParameter("@MarriageId", employee.MarriageId)
, new SqlParameter("@PartyStatusId", employee.PartyStatusId)
, new SqlParameter("@Nationality", employee.Nationality)
, new SqlParameter("@NativeAddr", employee.NativeAddr)
, new SqlParameter("@EducationId", employee.EducationId)
, new SqlParameter("@Major", SqlHelper.ToDBValue(employee.Major))
, new SqlParameter("@School", SqlHelper.ToDBValue(employee.School))
, new SqlParameter("@Address", employee.Address)
, new SqlParameter("@BaseSalary", employee.BaseSalary)
, new SqlParameter("@Email", SqlHelper.ToDBValue(employee.Email))
, new SqlParameter("@IdNum", employee.IdNum)
, new SqlParameter("@TelNum", employee.TelNum)
, new SqlParameter("@EmergencyContact", SqlHelper.ToDBValue(employee.EmergencyContact))
, new SqlParameter("@DepartmentId", employee.DepartmentId)
, new SqlParameter("@Position", employee.Position)
, new SqlParameter("@ContractStartDay", employee.ContractStartDay)
, new SqlParameter("@ContractEndDay", employee.ContractEndDay)
, new SqlParameter("@Resume", SqlHelper.ToDBValue(employee.Resume))
, new SqlParameter("@Remarks", SqlHelper.ToDBValue(employee.Remarks))
, new SqlParameter("@GenderId", employee.GenderId)
, new SqlParameter("@Photo", SqlHelper.ToDBValue(employee.Photo)));
}
public void Update(Employee employee)
{
SqlHelper.ExecuteNonQuery(@"Update T_Employee set
[Number]=@Number,[Name]=@Name,[BirthDay]=@BirthDay,[InDate]=@InDate,
[MarriageId]=@MarriageId,[PartyStatusId]=@PartyStatusId,[Nationality]=@Nationality,
[NativeAddr]=@NativeAddr,[EducationId]=@EducationId,[Major]=@Major,[School]=@School,
[Address]=@Address,[BaseSalary]=@BaseSalary,[Email]=@Email,
[IdNum]=@IdNum,[TelNum]=@TelNum,[EmergencyContact]=@EmergencyContact,
[DepartmentId]=@DepartmentId,[Position]=@Position,[ContractStartDay]=@ContractStartDay,
[ContractEndDay]=@ContractEndDay,[Resume]=@Resume,[Remarks]=@Remarks,[GenderId]=@GenderId
,Photo=@Photo
Where Id=@Id", new SqlParameter("@Number", employee.Number)
, new SqlParameter("@Name", employee.Name)
, new SqlParameter("@BirthDay", employee.BirthDay)
, new SqlParameter("@InDate", employee.InDate)
, new SqlParameter("@MarriageId", employee.MarriageId)
, new SqlParameter("@PartyStatusId", employee.PartyStatusId)
, new SqlParameter("@Nationality", employee.Nationality)
, new SqlParameter("@NativeAddr", employee.NativeAddr)
, new SqlParameter("@EducationId", employee.EducationId)
, new SqlParameter("@Major", SqlHelper.ToDBValue(employee.Major))
, new SqlParameter("@School", SqlHelper.ToDBValue(employee.School))
, new SqlParameter("@Address", employee.Address)
, new SqlParameter("@BaseSalary", employee.BaseSalary)
, new SqlParameter("@Email", SqlHelper.ToDBValue(employee.Email))
, new SqlParameter("@IdNum", employee.IdNum)
, new SqlParameter("@TelNum", employee.TelNum)
, new SqlParameter("@EmergencyContact", SqlHelper.ToDBValue(employee.EmergencyContact))
, new SqlParameter("@DepartmentId", employee.DepartmentId)
, new SqlParameter("@Position", employee.Position)
, new SqlParameter("@ContractStartDay", employee.ContractStartDay)
, new SqlParameter("@ContractEndDay", employee.ContractEndDay)
, new SqlParameter("@Resume", SqlHelper.ToDBValue(employee.Resume))
, new SqlParameter("@Remarks", SqlHelper.ToDBValue(employee.Remarks))
, new SqlParameter("@GenderId", employee.GenderId)
, new SqlParameter("@Photo", SqlHelper.ToDBValue(employee.Photo))
, new SqlParameter("@Id", employee.Id));
}
}
界面:EmployeeListWindow
<Window x:Class="HRMSys.UI.EmployeeListUIWindow"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
Title="员工管理" Height="700" Width="1000" WindowStartupLocation="CenterScreen">
<Grid>
<DockPanel>
<ToolBar DockPanel.Dock="Top" Height="30">
<Button Name="btnNewEmployee" ToolTip="新增">
<Image Source="/Images/new.png"></Image>
</Button>
</ToolBar>
<DataGrid Name="datagrid" IsReadOnly="True" AutoGenerateColumns="False">
<DataGrid.Columns>
<DataGridTextColumn Header="姓名" Width="100" Binding="{Binding Name}"></DataGridTextColumn>
<DataGridTextColumn Header="工号" Width="100" Binding="{Binding Number}"></DataGridTextColumn>
<DataGridTextColumn Header="入职时间" Width="100" Binding="{Binding InDate}"></DataGridTextColumn>
<DataGridComboBoxColumn Header="学历" Width="80" SelectedValueBinding="{Binding EducationId}" SelectedValuePath="Id"
DisplayMemberPath="Name" x:Name="columnEducationId"></DataGridComboBoxColumn>
<DataGridTextColumn Header="毕业院校" Width="100" Binding="{Binding School}"></DataGridTextColumn>
<DataGridTextColumn Header="基本工资" Binding="{Binding BaseSalary}"></DataGridTextColumn>
<DataGridComboBoxColumn Header="部门" Width="100" SelectedValueBinding="{Binding DepartmentId}" SelectedValuePath="Id"
DisplayMemberPath="Name" x:Name="columnDepartmentId"></DataGridComboBoxColumn>
<DataGridTextColumn Header="职位" Width="100" Binding="{Binding Position}"></DataGridTextColumn>
<DataGridTextColumn Header="合同签订日" Width="100" Binding="{Binding ContractStartDay}"></DataGridTextColumn>
<DataGridTextColumn Header="合同到期日" Width="100" Binding="{Binding ContractEndDay}"></DataGridTextColumn>
</DataGrid.Columns>
</DataGrid>
</DockPanel>
</Grid>
</Window>
EdtiUI
<Window x:Class="HRMSys.UI.EmployeeEditWindow"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
Title="员工添加编辑" Height="650" Width="800" WindowStartupLocation="CenterScreen" ResizeMode="NoResize">
<Grid Margin="0,0,0,55" Name="gridEmployee">
<TabControl>
<TabItem Header="基本信息">
<Grid>
<Grid.ColumnDefinitions>
<ColumnDefinition Width="18*"/>
<ColumnDefinition Width="179*"/>
</Grid.ColumnDefinitions>
<TextBlock HorizontalAlignment="Left" Margin="10,10,0,0" TextWrapping="Wrap" Text="姓名" VerticalAlignment="Top"/>
<TextBox Text="{Binding Name}" HorizontalAlignment="Left" Height="23" Margin="39,9,0,0" TextWrapping="Wrap" Name="txtName" VerticalAlignment="Top" Width="120" Grid.ColumnSpan="2"/>
<TextBlock HorizontalAlignment="Left" Margin="108,14,0,0" TextWrapping="Wrap" Text="性别" VerticalAlignment="Top" Grid.Column="1"/>
<ComboBox Text="必填" x:Name="cbGender" SelectedValue=http://www.mamicode.com/"{Binding GenderId}" DisplayMemberPath="Name" SelectedValuePath="Id" HorizontalAlignment="Left" Margin="151,9,0,0" VerticalAlignment="Top" Width="120" Grid.Column="1"/>
<TextBlock HorizontalAlignment="Left" Margin="290,11,0,0" TextWrapping="Wrap" Text="出生日期" VerticalAlignment="Top" Grid.Column="1"/>
<DatePicker x:Name="dpBirthDay" SelectedDate="{Binding BirthDay}" HorizontalAlignment="Left" Margin="355,5,0,0" VerticalAlignment="Top" Width="140" Grid.Column="1"/>
<TextBlock HorizontalAlignment="Left" Margin="10,46,0,0" TextWrapping="Wrap" Text="入职日期" VerticalAlignment="Top"/>
<DatePicker SelectedDate="{Binding InDate}" x:Name="dpInDate" HorizontalAlignment="Left" Margin="5,42,0,0" VerticalAlignment="Top" Grid.Column="1"/>
<TextBlock HorizontalAlignment="Left" Margin="108,46,0,0" TextWrapping="Wrap" Text="合同签订日" VerticalAlignment="Top" Grid.Column="1"/>
<DatePicker x:Name="dpContractBeginDate" SelectedDate="{Binding ContractStartDay}" HorizontalAlignment="Left" Margin="189,42,0,0" VerticalAlignment="Top" Grid.Column="1"/>
<TextBlock HorizontalAlignment="Left" Margin="290,46,0,0" TextWrapping="Wrap" Text="合同到期日" VerticalAlignment="Top" Grid.Column="1"/>
<DatePicker x:Name="dpContractEndDate" SelectedDate="{Binding ContractEndDay}" HorizontalAlignment="Left" Margin="355,42,0,0" VerticalAlignment="Top" Width="140" Grid.Column="1"/>
<TextBlock HorizontalAlignment="Left" Margin="10,79,0,0" TextWrapping="Wrap" Text="婚姻状况" VerticalAlignment="Top"/>
<ComboBox x:Name="cbMarriage" SelectedValue=http://www.mamicode.com/"{Binding MarriageId}" DisplayMemberPath="Name" SelectedValuePath="Id" HorizontalAlignment="Left" Margin="63,73,0,0" VerticalAlignment="Top" Width="96" Grid.ColumnSpan="2"/>
<TextBlock HorizontalAlignment="Left" Margin="108,79,0,0" TextWrapping="Wrap" Text="政治面貌" VerticalAlignment="Top" Grid.Column="1"/>
<ComboBox x:Name="cbPartyStatus" SelectedValue=http://www.mamicode.com/"{Binding PartyStatusId}" DisplayMemberPath="Name" SelectedValuePath="Id" HorizontalAlignment="Left" Margin="178,73,0,0" VerticalAlignment="Top" Width="93" Grid.Column="1"/>
<TextBlock HorizontalAlignment="Left" Margin="289,78,0,0" TextWrapping="Wrap" Text="民族" VerticalAlignment="Top" Grid.Column="1"/>
<TextBox x:Name="txtNational" HorizontalAlignment="Left" Height="23" Margin="355,79,0,0" TextWrapping="Wrap" Text="{Binding Nationality}" VerticalAlignment="Top" Width="140" Grid.Column="1"/>
<TextBlock HorizontalAlignment="Left" Margin="10,111,0,0" TextWrapping="Wrap" Text="籍贯" VerticalAlignment="Top"/>
<TextBox x:Name="txtNativeAddr" HorizontalAlignment="Left" Height="23" Margin="39,110,0,0" TextWrapping="Wrap" Text="{Binding NativeAddr}" VerticalAlignment="Top" Width="528" Grid.ColumnSpan="2"/>
<TextBlock HorizontalAlignment="Left" Margin="10,146,0,0" TextWrapping="Wrap" Text="学历" VerticalAlignment="Top"/>
<ComboBox x:Name="cbEducation" SelectedValuePath="Id" DisplayMemberPath="Name" SelectedValue=http://www.mamicode.com/"{Binding EducationId}" HorizontalAlignment="Left" Margin="39,146,0,0" VerticalAlignment="Top" Width="120" Grid.ColumnSpan="2"/>
<TextBlock HorizontalAlignment="Left" Margin="113,146,0,0" TextWrapping="Wrap" Text="专业" VerticalAlignment="Top" Grid.Column="1"/>
<TextBox x:Name="txtMajor" HorizontalAlignment="Left" Height="23" Margin="151,145,0,0" TextWrapping="Wrap" Text="{Binding Major}" VerticalAlignment="Top" Width="120" Grid.Column="1"/>
<TextBlock HorizontalAlignment="Left" Margin="289,146,0,0" TextWrapping="Wrap" Text="毕业院校" VerticalAlignment="Top" Grid.Column="1"/>
<TextBox x:Name="txtSchool" HorizontalAlignment="Left" Height="23" Margin="355,145,0,0" TextWrapping="Wrap" Text="{Binding School}" VerticalAlignment="Top" Width="140" Grid.Column="1"/>
<TextBlock HorizontalAlignment="Left" Margin="10,180,0,0" TextWrapping="Wrap" Text="联系地址" VerticalAlignment="Top"/>
<TextBox x:Name="txtAddr" HorizontalAlignment="Left" Height="23" Margin="63,179,0,0" TextWrapping="Wrap" Text="{Binding Address}" VerticalAlignment="Top" Width="504" Grid.ColumnSpan="2"/>
<TextBlock HorizontalAlignment="Left" Margin="10,215,0,0" TextWrapping="Wrap" Text="基本工资" VerticalAlignment="Top"/>
<TextBox x:Name="txtBaseSalary" HorizontalAlignment="Left" Height="23" Margin="63,214,0,0" TextWrapping="Wrap" Text="{Binding BaseSalary}" VerticalAlignment="Top" Width="96" Grid.ColumnSpan="2"/>
<TextBox x:Name="txtEmail" HorizontalAlignment="Left" Height="23" Margin="161,214,0,0" TextWrapping="Wrap" Text="{Binding Email}" VerticalAlignment="Top" Width="120" Grid.Column="1"/>
<TextBlock HorizontalAlignment="Left" Margin="108,215,0,0" TextWrapping="Wrap" Text="电子邮件" VerticalAlignment="Top" Grid.Column="1"/>
<TextBlock HorizontalAlignment="Left" Margin="290,214,0,0" TextWrapping="Wrap" Text="联系电话" VerticalAlignment="Top" Grid.Column="1"/>
<TextBox x:Name="txtTelNum" HorizontalAlignment="Left" Height="23" Margin="355,213,0,0" TextWrapping="Wrap" Text="{Binding TelNum}" VerticalAlignment="Top" Width="140" Grid.Column="1"/>
<TextBlock HorizontalAlignment="Left" Margin="10,251,0,0" TextWrapping="Wrap" Text="身份证" VerticalAlignment="Top"/>
<TextBox x:Name="txtIdNum" HorizontalAlignment="Left" Height="23" Margin="63,251,0,0" TextWrapping="Wrap" Text="{Binding IdNum}" VerticalAlignment="Top" Width="290" Grid.ColumnSpan="2"/>
<TextBlock HorizontalAlignment="Left" Margin="295,251,0,0" TextWrapping="Wrap" Text="部门" VerticalAlignment="Top" Grid.Column="1"/>
<ComboBox x:Name="cbDepatment" SelectedValue=http://www.mamicode.com/"{Binding DepartmentId}" SelectedValuePath="Id" DisplayMemberPath="Name" HorizontalAlignment="Left" Margin="355,251,0,0" VerticalAlignment="Top" Width="140" Grid.Column="1"/>
<TextBlock HorizontalAlignment="Left" Margin="10,289,0,0" TextWrapping="Wrap" Text="职位" VerticalAlignment="Top"/>
<TextBox x:Name="txtPosition" HorizontalAlignment="Left" Height="23" Margin="63,288,0,0" TextWrapping="Wrap" Text="{Binding Position}" VerticalAlignment="Top" Width="165" Grid.ColumnSpan="2"/>
<TextBlock HorizontalAlignment="Left" Margin="178,289,0,0" TextWrapping="Wrap" Text="工号" VerticalAlignment="Top" Grid.Column="1"/>
<TextBox x:Name="txtNumber" HorizontalAlignment="Left" Height="23" Margin="217,288,0,0" TextWrapping="Wrap" Text="{Binding Number}" VerticalAlignment="Top" Width="278" Grid.Column="1"/>
<TextBlock HorizontalAlignment="Left" Margin="10,328,0,0" TextWrapping="Wrap" Text="紧急联系人信息" VerticalAlignment="Top" Grid.ColumnSpan="2"/>
<TextBox x:Name="txtEmergencyContact" HorizontalAlignment="Left" TextWrapping="Wrap" Height="131" Margin="10,348,0,0" Text="{Binding EmergencyContact}" VerticalAlignment="Top" Width="343" Grid.ColumnSpan="2"/>
<TextBlock HorizontalAlignment="Left" Margin="290,328,0,0" TextWrapping="Wrap" Text="备注" VerticalAlignment="Top" Grid.Column="1"/>
<TextBox x:Name="txtRemarks" HorizontalAlignment="Left" Height="131" Margin="295,348,0,0" TextWrapping="Wrap" Text="{Binding Remarks}" VerticalAlignment="Top" Width="411" Grid.Column="1"/>
<Image x:Name="imgPhoto" HorizontalAlignment="Left" Height="215" Margin="511,14,0,0" VerticalAlignment="Top" Width="195" Grid.Column="1" Stretch="Fill" />
<Button x:Name="btnChoosePhoto" Content="选择照片" HorizontalAlignment="Left" Margin="511,251,0,0" VerticalAlignment="Top" Width="75" Grid.Column="1" Click="btnChoosePhoto_Click" />
<Button x:Name="btnCapture" Content="拍照" HorizontalAlignment="Left" Margin="610,249,0,0" VerticalAlignment="Top" Width="75" Grid.Column="1" Click="btnCapture_Click" />
</Grid>
</TabItem>
<TabItem Header="简历">
<TextBox TextWrapping="Wrap" Name="txtResume" Text="{Binding Resume}"></TextBox>
</TabItem>
</TabControl>
<Button x:Name="txtSave" Content="保存" HorizontalAlignment="Left" Margin="658,521,0,-44" VerticalAlignment="Top" Width="120" Click="txtSave_Click" Height="39"/>
</Grid>
</Window>
在EmployeeEditWindow中增加：
public Guid EditingId { get; set; }
public bool IsAddNew { get; set; }
以判断是不是为新增，如果为编辑则取得其编辑的EditingId
当双击了EmployeeListWindow的新增按钮时：
private void btnNewEmployee_Click(object sender, RoutedEventArgs e)
{
EmployeeEditWindow win = new EmployeeEditWindow();
win.IsAddNew = true;
if(win.ShowDialog()==true)
{
}
}
EditWin加载时：
private void Window_Loaded(object sender, RoutedEventArgs e)
{
if(IsAddNew)
{
Employee employee = new Employee();
employee.InDate = DateTime.Today;//给默认值
employee.ContractStartDay = DateTime.Today;
employee.ContractEndDay = DateTime.Today.AddYears(1);
employee.Nationality = "汉族";
employee.Email = "@itcast.cn";
employee.Number = "YG";
gridEmployee.DataContext = employee;
}
else
{
}
SAVE按钮按下时：
private void txtSave_Click(object sender, RoutedEventArgs e)
{
//cbGender.ItemsSource = new IdNameDAL().GetByCategory("性?别àe");
if (IsAddNew)
{
Employee employee = (Employee)gridEmployee.DataContext;
new EmployeeDAL().Insert(employee);
}
else
{
}
DialogResult = true;
}
Editwin加载时 --需要注意的地方时，实际上点击了保存按钮时，存放到Employee表中的性别、婚姻状况、政治面貌、学历等IdName中的信息是存储的外键（即IdName的Id
而非IdName表中的Name）
private void Window_Loaded(object sender, RoutedEventArgs e)
{
IdNameDAL idnamedal = new IdNameDAL();
cbGender.ItemsSource = idnamedal.GetByCategory("性别");
cbMarriage.ItemsSource = idnamedal.GetByCategory("婚姻状况");
cbPartyStatus.ItemsSource = idnamedal.GetByCategory("政治面貌");
cbEducation.ItemsSource = idnamedal.GetByCategory("学历");
cbDepatment.ItemsSource = new DepartmentDAL().ListAll();
//Employee employee = new Employee();
//employee.InDate = DateTime.Today;//给默认值
//employee.ContractStartDay = DateTime.Today;
//employee.ContractEndDay = DateTime.Today.AddYears(1);
//employee.Nationality = "汉族";
//employee.Email = "@itcast.cn";
//employee.Number = "YG";
//gridEmployee.DataContext = employee;
if(IsAddNew)
{
Employee employee = new Employee();
employee.InDate = DateTime.Today;//给默认值
employee.ContractStartDay = DateTime.Today;
employee.ContractEndDay = DateTime.Today.AddYears(1);
employee.Nationality = "汉族";
employee.Email = "@itcast.cn";
employee.Number = "YG";
gridEmployee.DataContext = employee; -----遗留问题，已添加初始化，却无法显示
//Employee employee = new Employee();
//employee.InDate = DateTime.Today;//给默认值
//employee.ContractStartDay = DateTime.Today;
//employee.ContractEndDay = DateTime.Today.AddYears(1);
//employee.Nationality = "汉族";
//employee.Email = "@itcast.cn";
//employee.Number = "YG";
//gridEmployee.DataContext = employee;
}
else
{
}
}
现实现功能描述
→
以及员工新增,还有原始的操作员管理界面能正常使用,版本V1.0
数据的非空检查
编辑页面保存前做判断
private void txtSave_Click(object sender, RoutedEventArgs e)
{
bool isOk = true;//数据检验是否通过
//cbGender.ItemsSource = new IdNameDAL().GetByCategory("性别");
// 判断非空字段不能为空
if (txtName.Text.Length <= 0)
{
isOk = false;
txtName.Background = Brushes.Red;
}
else
{
txtName.Background = null;
}
if (txtNational.Text.Length<=0)
{
isOk = false;
txtNational.Background = Brushes.Red;
}
else
{
txtNational.Background = null;
}
if (cbDepatment.SelectedIndex < 0)//cbDepatment.SelectedIndex=-1表示没有任何项被选中
{
isOk = false;
cbDepatment.Effect = new DropShadowEffect() { Color = Colors.Red };
}
else
{
cbDepatment.Effect = null;
}
if(!isOk)//如果没有通过检验非空的合法性，则不保存
{
return;
}
if (IsAddNew)
{
Employee employee = (Employee)gridEmployee.DataContext;
new EmployeeDAL().Insert(employee);
}
else
{
Employee employee = (Employee)gridEmployee.DataContext;
new EmployeeDAL().Update(employee);
}
DialogResult = true;
}
}
封装简化
private void CheckTextboxNotEmpty(ref bool isOK,params TextBox[] textboxes)
{
foreach(TextBox txtbox in textboxes)
{
if (txtbox.Text.Length <= 0)
{
isOK = false;
txtbox.Background = Brushes.Red;
}
else
{
txtbox.Background = null;
}
}
}
private void CheckComboBoxNotEmpty(ref bool isOK,params ComboBox[] cmbs)
{
foreach (ComboBox cmb in cmbs)
{
if (cmb.SelectedIndex < 0)
{
isOK = false;
cmb.Effect = new DropShadowEffect { Color = Colors.Red };
}
else
{
cmb.Effect = null;
}
}
}
bool isOK = true;//数据检验是否通过
CheckTextboxNotEmpty(ref isOK, txtName, txtNational, txtNativeAddr, txtAddr,
txtBaseSalary, txtTelNum, txtIdNum, txtPosition, txtNumber);
CheckComboBoxNotEmpty(ref isOK, cbGender, cbMarriage,
cbPartyStatus, cbEducation, cbDepatment);
if(!isOK)//如果没有通过检验非空的合法性，则不保存
{
return;
}
照片处理:
照片存储在数据库中(以二进制数据存储),降低了数据库中查询速度,一般不把照片存储在数据库中,而是存储在服务器端,数据库中只存储路径
1.Employee表中增加字段
在.net中为byte[]类型（byte数组类型—二进制的最终表示形式）
Model中
public byte[] Photo
{
get;
set;
}
界面中添加照片代码：
private void btnChoosePhoto_Click(object sender, RoutedEventArgs e)
{
OpenFileDialog ofdPhoto = new OpenFileDialog();
ofdPhoto.Filter = "jpg图片|*.jpg|png图片|*.png";
if(ofdPhoto.ShowDialog()==true)
{
Employee employee = (Employee)gridEmployee.DataContext;
string filename = ofdPhoto.FileName;
employee.Photo= File.ReadAllBytes(filename);//读取文件的二进制数组
imgPhoto.Source = new BitmapImage(new Uri(filename));
}
}
编辑时显示照片
private void Window_Loaded(object sender, RoutedEventArgs e)
{
IdNameDAL idnamedal = new IdNameDAL();
cbGender.ItemsSource = idnamedal.GetByCategory("性别");
cbMarriage.ItemsSource = idnamedal.GetByCategory("婚姻状况");
cbPartyStatus.ItemsSource = idnamedal.GetByCategory("政治面貌");
cbEducation.ItemsSource = idnamedal.GetByCategory("学历");
cbDepatment.ItemsSource = new DepartmentDAL().ListAll();
//Employee employee = new Employee();
//employee.InDate = DateTime.Today;//给默认值
//employee.ContractStartDay = DateTime.Today;
//employee.ContractEndDay = DateTime.Today.AddYears(1);
//employee.Nationality = "汉族";
//employee.Email = "@itcast.cn";
//employee.Number = "YG";
//gridEmployee.DataContext = employee;
if(IsAddNew)
{
Employee employee = new Employee();
employee.InDate = DateTime.Today;//给默认值
employee.ContractStartDay = DateTime.Today;
employee.ContractEndDay = DateTime.Today.AddYears(1);
employee.BirthDay = DateTime.Today;
employee.Nationality = "汉族";
employee.Email = "@itcast.cn";
employee.Number = "YG";
gridEmployee.DataContext = employee;
}
else
{
Employee employee = new EmployeeDAL().GetById(EditingId);
gridEmployee.DataContext = employee;
if (employee.Photo != null)
{
ShowImg(employee.Photo);
}
}
}
private void ShowImg(byte[] imgBytes)
{
MemoryStream stream = new MemoryStream(imgBytes);
BitmapImage bmpImg = new BitmapImage();
bmpImg.BeginInit();
bmpImg.StreamSource = stream;
bmpImg.EndInit();
imgPhoto.Source = bmpImg;
}
通过摄像头实现拍照功能的实现
摄像头开发组件（开源）WPFMeditKit
a. 将WPFMediaKit引用到UI层
b. 界面xaml代码书写
<Window x:Class="HRMSys.UI.TestWindow"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
xmlns:wpfmedia="clr-namespace:WPFMediaKit.DirectShow.Controls;assembly=WPFMediaKit"
Title="TestWindow" Height="300" Width="482" Loaded="Window_Loaded">
<Grid>
<StackPanel>
<ComboBox Name="cb" SelectionChanged="cb_SelectionChanged"></ComboBox>
<wpfmedia:VideoCaptureElement Height="200" Name="vce">
</wpfmedia:VideoCaptureElement>
<Button Height="30" Content="拍照" Name="btnCapture" Click="btnCapture_Click_1"></Button>
</StackPanel>
</Grid>
</Window>
c. TestWin.xaml.cs代码如下
public partial class TestWindow : Window
{
public TestWindow()
{
InitializeComponent();
}
private void Window_Loaded(object sender, RoutedEventArgs e)
{
cb.ItemsSource = MultimediaUtil.VideoInputNames;//选择多个摄像头
if (MultimediaUtil.VideoInputNames.Length>0)
{
cb.SelectedIndex = 0;//若有多个摄像头，则将第0个设为默认摄像头
}
else
{
MessageBox.Show("电脑没有安装任何可用摄像头！");
}
}
private void cb_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
vce.VideoCaptureSource = (string)cb.SelectedItem;
}
private void btnCapture_Click_1(object sender, RoutedEventArgs e)
{
RenderTargetBitmap bmp = new RenderTargetBitmap(
(int)vce.ActualWidth, (int)vce.ActualHeight,
96, 96, PixelFormats.Default);//RenderTargetBitmap作用是将一个控件的内容抓成一张图片
//(int)vce.ActualWidth, (int)vce.ActualHeight为要抓控件的宽度和高度
bmp.Render(vce);
BitmapEncoder encoder = new JpegBitmapEncoder();//jpeg保存格式
encoder.Frames.Add(BitmapFrame.Create(bmp));
using (MemoryStream ms = new MemoryStream())
{
encoder.Save(ms);
byte[] captureData = http://www.mamicode.com/ms.ToArray();
File.WriteAllBytes("d:/1.jpg", captureData);
}
vce.Pause();
}
}
回到项目中，点击拍照时弹出摄像头拍照窗口
EditWindow.xaml.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Shapes;
using HRMSys.Model;
using HRMSys.DAL;
using System.Windows.Media.Effects;
using Microsoft.Win32;
using System.IO;
namespace HRMSys.UI
{
/// <summary>
/// EmployeeEditWindow.xaml 的交互逻辑
/// </summary>
public partial class EmployeeEditWindow : Window
{
public EmployeeEditWindow()
{
InitializeComponent();
}
private void btnChoosePhoto_Click(object sender, RoutedEventArgs e)
{
OpenFileDialog ofdPhoto = new OpenFileDialog();
ofdPhoto.Filter = "jpg图片|*.jpg|png图片|*.png";
if(ofdPhoto.ShowDialog()==true)
{
Employee employee = (Employee)gridEmployee.DataContext;
string filename = ofdPhoto.FileName;
employee.Photo= File.ReadAllBytes(filename);//读取文件的二进制数组
imgPhoto.Source = new BitmapImage(new Uri(filename));
}
}
private void btnCapture_Click(object sender, RoutedEventArgs e)
{
CaptureWindow win = new CaptureWindow();
if (win.ShowDialog() == true)
{
byte[] data = http://www.mamicode.com/win.CaptureData;
ShowImg(data);
Employee employee = (Employee)gridEmployee.DataContext;
employee.Photo = data;
}
}
public Guid EditingId { get; set; }
public bool IsAddNew { get; set; }
private void Window_Loaded(object sender, RoutedEventArgs e)
{
IdNameDAL idnamedal = new IdNameDAL();
cbGender.ItemsSource = idnamedal.GetByCategory("性别");
cbMarriage.ItemsSource = idnamedal.GetByCategory("婚姻状况");
cbPartyStatus.ItemsSource = idnamedal.GetByCategory("政治面貌");
cbEducation.ItemsSource = idnamedal.GetByCategory("学历");
cbDepatment.ItemsSource = new DepartmentDAL().ListAll();
if(IsAddNew)
{
Employee employee = new Employee();
employee.InDate = DateTime.Today;//给默认值
employee.ContractStartDay = DateTime.Today;
employee.ContractEndDay = DateTime.Today.AddYears(1);
employee.BirthDay = DateTime.Today;
employee.Nationality = "汉族";
employee.Email = "@itcast.cn";
employee.Number = "YG";
gridEmployee.DataContext = employee;
}
else
{
Employee employee = new EmployeeDAL().GetById(EditingId);
gridEmployee.DataContext = employee;
if (employee.Photo != null)
{
ShowImg(employee.Photo);
}
}
}
private void ShowImg(byte[] imgBytes)
{
MemoryStream stream = new MemoryStream(imgBytes);
BitmapImage bmpImg = new BitmapImage();
bmpImg.BeginInit();
bmpImg.StreamSource = stream;
bmpImg.EndInit();
imgPhoto.Source = bmpImg;
}
private void CheckTextboxNotEmpty(ref bool isOK,params TextBox[] textboxes)
{
foreach(TextBox txtbox in textboxes)
{
if (txtbox.Text.Length <= 0)
{
isOK = false;
txtbox.Background = Brushes.Red;
}
else
{
txtbox.Background = null;
}
}
}
private void CheckComboBoxNotEmpty(ref bool isOK,params ComboBox[] cmbs)
{
foreach (ComboBox cmb in cmbs)
{
if (cmb.SelectedIndex < 0)
{
isOK = false;
cmb.Effect = new DropShadowEffect { Color = Colors.Red };
}
else
{
cmb.Effect = null;
}
}
}
private void txtSave_Click(object sender, RoutedEventArgs e)
{
bool isOK = true;//数据检验是否通过
CheckTextboxNotEmpty(ref isOK, txtName, txtNational, txtNativeAddr, txtAddr,
txtBaseSalary, txtTelNum, txtIdNum, txtPosition, txtNumber);
CheckComboBoxNotEmpty(ref isOK, cbGender, cbMarriage,
cbPartyStatus, cbEducation, cbDepatment);
if(!isOK)//如果没有通过检验非空的合法性，则不保存
{
return;
}
if (IsAddNew)
{
Employee employee = (Employee)gridEmployee.DataContext;
new EmployeeDAL().Insert(employee);
}
else
{
Employee employee = (Employee)gridEmployee.DataContext;
new EmployeeDAL().Update(employee);
}
DialogResult = true;
}
}
}
CaptureWindow.xaml.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Shapes;
using WPFMediaKit.DirectShow.Controls;
using System.IO;
namespace HRMSys.UI
{
/// <summary>
/// CaptureWindow.xaml 的交互逻辑
/// </summary>
public partial class CaptureWindow : Window
{
public CaptureWindow()
{
InitializeComponent();
}
public byte[] CaptureData { get; set; }
private void btnCapture_Click(object sender, RoutedEventArgs e)
{
//captureElement. 怎么抓取高清的原始图像
//能不能抓视频。
//todo：怎么只抓取一部分
RenderTargetBitmap bmp = new RenderTargetBitmap(
(int)captureElement.ActualWidth, (int)captureElement.ActualHeight,
96, 96, PixelFormats.Default);
bmp.Render(captureElement);
BitmapEncoder encoder = new JpegBitmapEncoder();
encoder.Frames.Add(BitmapFrame.Create(bmp));
using (MemoryStream ms = new MemoryStream())
{
encoder.Save(ms);
CaptureData = http://www.mamicode.com/ms.ToArray();
}
//captureElement.Pause();
//todo:自己完成重拍的代码
DialogResult = true;
}
private void cbCameras_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
captureElement.VideoCaptureSource = (string)cbCameras.SelectedItem;
}
private void btnOK_Click(object sender, RoutedEventArgs e)
{
}
private void Window_Loaded(object sender, RoutedEventArgs e)
{
cbCameras.ItemsSource = MultimediaUtil.VideoInputNames;
if (MultimediaUtil.VideoInputNames.Length > 0)
{
cbCameras.SelectedIndex = 0;//第0个摄像头为默认摄像头
}
else
{
MessageBox.Show("电脑没有安装任何可用摄像头");
}
}
}
}
CaptureWindow.xaml
<Window x:Class="HRMSys.UI.CaptureWindow"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
xmlns:wpfmedia="clr-namespace:WPFMediaKit.DirectShow.Controls;assembly=WPFMediaKit"
Title="CaptureWindow" Height="300" Width="300" Loaded="Window_Loaded">
<Grid>
<DockPanel>
<ComboBox DockPanel.Dock="Top" Height="30" Name="cbCameras" SelectionChanged="cbCameras_SelectionChanged"></ComboBox>
<wpfmedia:VideoCaptureElement Height="200" DockPanel.Dock="Top" Name="captureElement"></wpfmedia:VideoCaptureElement>
<StackPanel Orientation="Horizontal">
<Button Height="30" DockPanel.Dock="Top" Name="btnCapture" Content="拍照" Click="btnCapture_Click"></Button>
<Button Height="30" DockPanel.Dock="Top" Name="btnOK" Content="确定" Click="btnOK_Click"></Button>
</StackPanel>
</DockPanel>
</Grid>
</Window>
复合搜索
EditUI.xaml中新增
<GroupBox Header="搜索条件" DockPanel.Dock="Top" Height="100">
<Grid>
<CheckBox Content="姓名" Height="16" HorizontalAlignment="Left" Margin="7,18,0,0" Name="cbSearchByName" VerticalAlignment="Top" />
<TextBox Height="23" HorizontalAlignment="Left" Margin="52,15,0,0" Name="txtName" VerticalAlignment="Top" Width="120" />
<CheckBox Content="入职时间" Height="16" HorizontalAlignment="Left" Margin="200,16,0,0" Name="cbSearchByInDate" VerticalAlignment="Top" />
<DatePicker Height="25" HorizontalAlignment="Left" Margin="286,13,0,0" Name="dpInDateStart" VerticalAlignment="Top" Width="115" />
<TextBlock Height="23" HorizontalAlignment="Left" Margin="407,15,0,0" Name="textBlock1" Text="到" VerticalAlignment="Top" />
<DatePicker Height="25" HorizontalAlignment="Left" Margin="425,14,0,0" Name="dpInDateEnd" VerticalAlignment="Top" Width="115" />
<CheckBox Content="部门" Height="16" HorizontalAlignment="Left" Margin="6,55,0,0" Name="cbSearchByDepartment" VerticalAlignment="Top" />
<ComboBox DisplayMemberPath="Name" SelectedValuePath="Id" Height="23" HorizontalAlignment="Left" Margin="63,54,0,0" Name="cmbDepartment" VerticalAlignment="Top" Width="120" />
<Button Content="搜索" Height="23" HorizontalAlignment="Left" Margin="768,55,0,0" Name="btnSearch" VerticalAlignment="Top" Width="75" Click="btnSearch_Click" />
</Grid>
</GroupBox>
EditUI Load时初始化控件：
//复合搜索代码如下
cbSearchByName.IsChecked = true;
dpInDateStart.SelectedDate = DateTime.Today.AddMonths(-1);
dpInDateEnd.SelectedDate = DateTime.Today;
cmbDepartment.ItemsSource = new DepartmentDAL().ListAll();
EmployeeDAL中新增方法:
//复合搜索新增方法
public Employee[] Search(string sql,List<SqlParameter> parameters)
{
DataTable table
= SqlHelper.ExecuteReader(sql,parameters.ToArray());
Employee[] employees = new Employee[table.Rows.Count];
for (int i = 0; i < table.Rows.Count;i++ )
{
employees[i] = ToModel(table.Rows[i]);
}
return employees;
}
点击搜索按钮时：
private void btnSearch_Click(object sender, RoutedEventArgs e)
{
//动态拼SQL
List<string> whereList = new List<string>();
List<SqlParameter> paramesList = new List<SqlParameter>();
if(cbSearchByName.IsChecked==true)
{
whereList.Add("Name=@Name");
paramesList.Add(new SqlParameter("@Name",txtName.Text));
}
if(cbSearchByInDate.IsChecked==true)
{
whereList.Add("InDate between @InDateStart and @InDateEnd");
paramesList.Add(new SqlParameter(" @InDateStart", dpInDateStart.SelectedDate));
paramesList.Add(new SqlParameter(" @InDateEnd", dpInDateEnd.SelectedDate));
}
if(cbSearchByDepartment.IsChecked==true)
{
whereList.Add("DepartmentId=@DepartmentId");
paramesList.Add(new SqlParameter("@DepartmentId",cmbDepartment.SelectedValue));
}
string whereSql = string.Join(" and ",whereList);
string sql="select * from T_Employee";
if(whereSql.Length>0)
{
sql = sql + " where "+whereSql;
}
Employee[] employees = new EmployeeDAL().Search(sql,paramesList);
datagrid.ItemsSource = employees;
}
ExcelAutomation的缺点—Excel的导出
Excel的读写NPOI开发包
可以使用ExcleAutomation进行文件的读写，但是需要电脑上安装excel，对excel的版本有要求，速度慢，有安全性，并发性问题，不适合做网站类项目
NOPI是一款轻量级的进行xls文件读写的开发包，完全是二进制操作，不依赖于excel
使用NPOI开发包需将NPOI.dll和NPOI.XML添加为引用
//Workbook工作簿，Sheet页，row行，cell：单元格
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
ISheet sheet1 = hssfworkbook.CreateSheet("俺的第一页");// Isheet返回接口
IRow rowHeader = sheet1.CreateRow(0);//第0行 参数为行
rowHeader.CreateCell(0, CellType.STRING).SetCellValue("传智播客");//第一个参数为列，第二个参数为CellType.STRING指放string类型的数据
//xls,xlsx
IRow row2 = sheet1.CreateRow(1);
row2.CreateCell(3, CellType.STRING).SetCellValue("fasdfasdfas");
using (Stream stream = File.OpenWrite("d:/1.xls"))
{
hssfworkbook.Write(stream);
}
实现导出功能的详细代码
private void btnOutPutEmployee_Click(object sender, RoutedEventArgs e)
{
SaveFileDialog sdfExport = new SaveFileDialog();
sdfExport.Filter="Excel文件|*.xls";
if(sdfExport.ShowDialog()!=true)
{
return;
}
string filename = sdfExport.FileName;
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet= workbook.CreateSheet("员工数据");
IRow rowHeader=sheet.CreateRow(0);//表头行
rowHeader.CreateCell(0,CellType.STRING).SetCellValue("姓名");
rowHeader.CreateCell(1,CellType.STRING).SetCellValue("工号");
rowHeader.CreateCell(2,CellType.STRING).SetCellValue("入职日期");
Employee[] employees = (Employee[])datagrid.ItemsSource;
for (int i = 0; i < employees.Length;i++ )
{
Employee employee=employees[i];
IRow row = sheet.CreateRow(i+1);
row.CreateCell(0, CellType.STRING).SetCellValue(employee.Name);
row.CreateCell(1, CellType.STRING).SetCellValue(employee.Number);
ICellStyle styledate = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
//格式具体有哪些请看单元格右键中的格式，有说明
styledate.DataFormat = format.GetFormat("yyyy\"年\"m\"月\"d\"日\"");
ICell cellInDate = row.CreateCell(2, CellType.NUMERIC);//日期类型需为CellType.NUMERIC类型
cellInDate.CellStyle = styledate;
cellInDate.SetCellValue(employee.InDate);
}
using (Stream stream = File.OpenWrite(filename))
{
workbook.Write(stream);
}
}
在zhbxHRMSys编程过程中,在员工增删改的操作中出现下面情况:
选中行的Employee的Id始终为空，经分析是因为ListWindow加载的时候
private void Window_Loaded(object sender, RoutedEventArgs e)
{
columnDepartment.ItemsSource = new DepartmentDAL().ListAll();
columnEducationId.ItemsSource = new IdNameDAL().GetByCategory("学历");
columnGenderId.ItemsSource = new IdNameDAL().GetByCategory("性别");
columnPartyStatusId.ItemsSource = new IdNameDAL().GetByCategory("政治面貌");
datagrid.ItemsSource = new EmployeeDAL().ListAll();
}
其ListAll（）函数调用的ToModel（）方法并没有给Id赋值
public Employee ToModel(DataRow row)
{
Employee employee = new Employee();
employee.Address=(string)row["Address"];
employee.BaseSalary=(long)row["BaseSalary"];
employee.BirthDay = (DateTime)row["BirthDay"];
employee.ContractEndDay=(DateTime)row["ContractEndDay"];
employee.ContractStartDay=(DateTime)row["ContractStartDay"];
employee.DepartmentId=(Guid)row["DepartmentId"];
employee.EducationId=(Guid)row["EducationId"];
employee.Email=(string)SqlHelper.FromDBNull(row["Email"]);
employee.EmergencyContact = (string)SqlHelper.FromDBNull(row["EmergencyContact"]);
employee.GenderId=(Guid)row["GenderId"];
employee.IdNum=(string)row["IdNum"];
employee.InDate=(DateTime)row["InDate"];
employee.Major = (string)SqlHelper.FromDBNull(row["Major"]);
employee.MarriageId=(Guid)row["MarriageId"];
employee.Name=(string)row["Name"];
employee.Nationality=(string)row["Nationality"];
employee.NativeAddr=(string)row["NativeAddr"];
employee.Number=(string)row["Number"];
employee.PartyStatusId=(Guid)row["PartyStatusId"];
employee.Photo = (byte[])SqlHelper.FromDBNull(row["Photo"]);
employee.Position=(string)row["Position"];
employee.Remarks = (string)SqlHelper.FromDBNull(row["Remarks"]);
employee.Resume = (string)SqlHelper.FromDBNull(row["Resume"]);
employee.School = (string)SqlHelper.FromDBNull(row["School"]);
employee.TelNum=(string)row["TelNum"];
return employee;
}
public Employee[] ListAll()
{
DataTable table = SqlHelper.ExecuteReader("select * from T_Employee");
Employee[] employees=new Employee[table.Rows.Count];
for (int i = 0; i < table.Rows.Count;i++ )
{
DataRow row=table.Rows[i];
employees[i] = ToModel(row);
}
return employees;
}
代码生成器
using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=HRMSYSDB;User ID=hrmsa;Password=123456"))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
//只获得表的架构信息（列信息）
cmd.CommandText = "select top 0 * from T_Employee";//top 0表示列名 只获得表的架构信息，不获得表的信息
DataSet ds = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.FillSchema(ds, SchemaType.Source);//获得表信息必须要写
adapter.Fill(ds);
DataTable table = ds.Tables[0];
foreach (DataColumn col in table.Columns)
{
Console.WriteLine(col.ColumnName + "," + col.AllowDBNull + "," + col.DataType);
}
}
Console.ReadKey();
}
获得表名
界面
<Window x:Class="MyCodeGen.MainWindow"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
Title="MainWindow" Height="350" Width="752">
<Grid>
<TextBlock Height="23" HorizontalAlignment="Left" Name="textBlock1" Text="连接字符串" VerticalAlignment="Top" />
<TextBox Height="23" HorizontalAlignment="Left" Margin="66,0,0,0" Name="txtConnectionString" VerticalAlignment="Top" Width="503" />
<Button Content="连接" Height="23" HorizontalAlignment="Left" Margin="575,0,0,0" Name="btnConn" VerticalAlignment="Top" Width="75" Click="btnConn_Click" />
<ComboBox Height="23" HorizontalAlignment="Left" Margin="12,29,0,0" Name="cmbTables" VerticalAlignment="Top" Width="275" IsEnabled="False" />
<Button Content="生成代码" Height="23" HorizontalAlignment="Right" Margin="0,29,296,0" Name="btnGenCode" VerticalAlignment="Top" Width="75" IsEnabled="False" />
<TextBox TextWrapping="Wrap" IsReadOnly="True" VerticalScrollBarVisibility="Auto" HorizontalScrollBarVisibility="Auto" Height="242" HorizontalAlignment="Left" Margin="6,69,0,0" Name="txtModelCode" VerticalAlignment="Top" Width="342" />
<TextBox Height="242" IsReadOnly="True" HorizontalAlignment="Left" HorizontalScrollBarVisibility="Auto" Margin="376,69,0,0" Name="txtDALCode" TextWrapping="Wrap" VerticalAlignment="Top" VerticalScrollBarVisibility="Auto" Width="342" />
</Grid>
</Window>
由于连接字符串是用户自己输入的，所以有可能会出错，因此，需要捕获异常
private void btnConn_Click(object sender, RoutedEventArgs e)
{
DataTable table;
try
{
table= ExecuteReader(@"SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = ‘BASE TABLE‘");//获得所有的表名
}
catch (SqlException sqlex)
{
MessageBox.Show("连接数据库出错！错误消息："+sqlex.Message);
return;
}
string[] tables=new string[table.Rows.Count];
for (int i = 0; i < table.Rows.Count;i++ )
{
DataRow row=table.Rows[i];
tables[i]=(string)row["TABLE_NAME"];
}
cmbTables.ItemsSource = tables;
cmbTables.IsEnabled = true;
btnGenCode.IsEnabled = true;
}//除非真的有捕获异常的需要，否则不要try catch
全部代码如下
namespace MyCodeGen
{
/// <summary>
/// MainWindow.xaml 的交互逻辑
/// </summary>
public partial class MainWindow : Window
{
public MainWindow()
{
InitializeComponent();
}
private DataTable ExecuteReader(string sql)
{
using (SqlConnection conn = new SqlConnection(txtConnectionString.Text))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
//只获得表的架构信息（列信息）
cmd.CommandText = sql;
DataSet ds = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.FillSchema(ds, SchemaType.Source);//获得表信息必须要写
adapter.Fill(ds);
return ds.Tables[0];
}
}
}
private void btnConn_Click(object sender, RoutedEventArgs e)
{
DataTable table;
try
{
table= ExecuteReader(@"SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = ‘BASE TABLE‘");//获得所有的表名
}
catch (SqlException sqlex)
{
MessageBox.Show("连接数据库出错！错误消息："+sqlex.Message);
return;
}
string[] tables=new string[table.Rows.Count];
for (int i = 0; i < table.Rows.Count;i++ )
{
DataRow row=table.Rows[i];
tables[i]=(string)row["TABLE_NAME"];
}
cmbTables.ItemsSource = tables;
cmbTables.IsEnabled = true;
btnGenCode.IsEnabled = true;
}//除非真的有捕获异常的需要，否则不要try catch
}
}
增加保存连接字符串代码
private void btnConn_Click(object sender, RoutedEventArgs e)
{
DataTable table;
try
{
table= ExecuteReader(@"SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = ‘BASE TABLE‘");//获得所有的表名
}
catch (SqlException sqlex)
{
MessageBox.Show("连接数据库出错！错误消息："+sqlex.Message);
return;
}
string[] tables=new string[table.Rows.Count];
for (int i = 0; i < table.Rows.Count;i++ )
{
DataRow row=table.Rows[i];
tables[i]=(string)row["TABLE_NAME"];
}
cmbTables.ItemsSource = tables;
cmbTables.IsEnabled = true;
btnGenCode.IsEnabled = true;
//把连接字符串记录到文件中，避免用户每次都需要输入连接字符串
//File.WriteAllText("connstr.txt",txtConnectionString.Text);
//AppDomain.CurrentDomain.BaseDirectory//获得当前程序文件路径最稳定的方法
string currenctDir = AppDomain.CurrentDomain.BaseDirectory;
//string configFile = currenctDir + "connstr.txt";
string configFile = System.IO.Path.Combine(currenctDir + "connstr.txt");
File.WriteAllText(configFile,txtConnectionString.Text);
}//除非真的有捕获异常的需要，否则不要try catch
窗口加载的时候读出连接字符串
封装获取文件路径的方法
private static string GetConfigFilePath()
{
string currenctDir = AppDomain.CurrentDomain.BaseDirectory;
string configFile = System.IO.Path.Combine(currenctDir + "connstr.txt");
return configFile;
}
private void Window_Loaded(object sender, RoutedEventArgs e)
{
string configFile = GetConfigFilePath();
txtConnectionString.Text= File.ReadAllText(configFile);
}
点击生成代码时
StringBuilder sb = new StringBuilder();//StringBuilder类
sb.Append("aasdfasdf").Append("31545").Append("asdf3");//链式编程
sb.Append("我是");
sb.AppendLine("这是一行！");
sb.AppendLine("又一行");
string s = sb.ToString();//取得连接后的字符串
Console.WriteLine(s);
Console.ReadKey();
生成Model类所有代码
namespace MyCodeGen
{
/// <summary>
/// MainWindow.xaml 的交互逻辑
/// </summary>
public partial class MainWindow : Window
{
public MainWindow()
{
InitializeComponent();
}
private DataTable ExecuteReader(string sql)
{
using (SqlConnection conn = new SqlConnection(txtConnectionString.Text))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
//只获得表的架构信息（列信息）
cmd.CommandText = sql;
DataSet ds = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.FillSchema(ds, SchemaType.Source);//获得表信息必须要写
adapter.Fill(ds);
return ds.Tables[0];
}
}
}
private void btnConn_Click(object sender, RoutedEventArgs e)
{
DataTable table;
try
{
table= ExecuteReader(@"SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = ‘BASE TABLE‘");//获得所有的表名
}
catch (SqlException sqlex)
{
MessageBox.Show("连接数据库出错！错误消息："+sqlex.Message);
return;
}
string[] tables=new string[table.Rows.Count];
for (int i = 0; i < table.Rows.Count;i++ )
{
DataRow row=table.Rows[i];
tables[i]=(string)row["TABLE_NAME"];
}
cmbTables.ItemsSource = tables;
cmbTables.IsEnabled = true;
btnGenCode.IsEnabled = true;
//把连接字符串记录到文件中，避免用户每次都需要输入连接字符串
//File.WriteAllText("connstr.txt",txtConnectionString.Text);
//AppDomain.CurrentDomain.BaseDirectory//获得当前程序文件路径最稳定的方法
//string currenctDir = AppDomain.CurrentDomain.BaseDirectory;
//string configFile = currenctDir + "connstr.txt";
//string configFile = System.IO.Path.Combine(currenctDir + "connstr.txt");
string configFile = GetConfigFilePath();
File.WriteAllText(configFile,txtConnectionString.Text);
}
private static string GetConfigFilePath()
{
string currenctDir = AppDomain.CurrentDomain.BaseDirectory;
string configFile = System.IO.Path.Combine(currenctDir + "connstr.txt");
return configFile;
}
private void Window_Loaded(object sender, RoutedEventArgs e)
{
string configFile = GetConfigFilePath();
txtConnectionString.Text= File.ReadAllText(configFile);
}
private void btnGenCode_Click(object sender, RoutedEventArgs e)
{
//获得表的列信息，用于生成代码
string tablename = (string)cmbTables.SelectedItem;
if (tablename == null)
{
MessageBox.Show("请选择要读取的表！");
return;
}
//介绍一个类
CreateModelCode(tablename);
CreateDALCode(tablename);
}
private void CreateModelCode(string tablename)
{
//bool b = true;//bool和System.Boolean是同一个东西
//System.Boolean b1 = true;//CTS
//System.String s1 = "";
//string s2 = "";
DataTable table = ExecuteReader("select top 0 * from "
+ tablename);
StringBuilder sb = new StringBuilder();
sb.Append("public class ").Append(tablename).AppendLine("{");
foreach (DataColumn column in table.Columns)
{
string columnDataType = GetDataTypeName(column);
sb.Append("public ").Append(columnDataType).Append(" ")
.Append(column.ColumnName).AppendLine("{get;set;}");
}
sb.AppendLine("}");
txtModelCode.Text = sb.ToString();
}
//进行可空类型的处理
private static string GetDataTypeName(DataColumn column)
{
//如果列允许为null，并且列在C#中的类型是不可为空的（值类型ValueType）
if (column.AllowDBNull && column.DataType.IsValueType)//column.DataType.IsValueType判断是否为值类型
//值类型不能为null
{
return column.DataType + "?";
}
else
{
return column.DataType.ToString();
}
}
private void CreateDALCode(string tablename)
{
DataTable table = ExecuteReader("select top 0 * from "
+ tablename);
StringBuilder sb = new StringBuilder();
sb.Append("public class ").Append(tablename).Append("DAL {");
//tomodel开始
sb.Append("private ").Append(tablename)
.AppendLine(" ToModel(DataRow row){");
sb.Append(tablename).AppendLine(" model = new " + tablename + "();");
foreach (DataColumn column in table.Columns)
{
//无论列是否允许为空，都进行判断DbNull的处理（省事）
//model.Id = (Guid)SqlHelper.FromDbValue(row["Id"]);
sb.Append("model.").Append(column.ColumnName).Append("=(")
.Append(GetDataTypeName(column)).Append(")SqlHelper.FromDbValue(row[\"")
.Append(column.ColumnName).AppendLine("\"]);");
}
sb.AppendLine("return model;");
sb.AppendLine("}");
//tomodel的结束
//listall开始
//public IEnumerable<Department> ListAll()
sb.Append("public IEnumerable<").Append(table)
.AppendLine("> ListAll(){");
//List<Department> list = new List<Department>();
sb.Append("List<").Append(tablename).Append("> list=new List<")
.Append(tablename).AppendLine(">();");
// DataTable dt = SqlHelper.ExecuteDataTable
//("select * from T_Department");
sb.Append("DataTable dt = SqlHelper.ExecuteDataTable(\"")
.Append("select * from " + tablename).AppendLine("\");");
sb.AppendLine("foreach (DataRow row in dt.Rows)");
//Department dept = ToModel(row);
sb.Append(tablename).AppendLine(" model=ToModel(row);");
//list.Add(model);
sb.AppendLine("list.Add(model);}");
sb.AppendLine("}");
//listall结束
//GetById();
//DeleteById();
//生成器要求列名必须是Id，类型必须是Guid
//Insert开始
//public void Insert(Operator op)
sb.Append("public void Insert(")
.Append(tablename).AppendLine(" model){");
//SqlHelper.ExecuteNonQuery(@"insert into T_Operator(
sb.Append("SqlHelper.ExecuteNonQuery(@\"")
.Append("insert into ").Append(tablename).AppendLine("(");
string[] colNames = GetColumnNames(table);
sb.AppendLine(string.Join(",", colNames));
string[] colParamNames = GetParamColumnNames(table);
sb.Append("values(").AppendLine(string.Join(",", colParamNames));
sb.AppendLine("}");
//Insert结束
sb.AppendLine("}");
txtDALCode.Text = sb.ToString();
}
//以数组形式返回列名
private static string[] GetColumnNames(DataTable table)
{
string[] colnames = new string[table.Columns.Count];
for (int i = 0; i < table.Columns.Count; i++)
{
DataColumn dataCol = table.Columns[i];
colnames[i] = dataCol.ColumnName;
}
return colnames;
}
//以数组形式返回@列名
private static string[] GetParamColumnNames(DataTable table)
{
string[] colnames = new string[table.Columns.Count];
for (int i = 0; i < table.Columns.Count; i++)
{
DataColumn dataCol = table.Columns[i];
colnames[i] = "@" + dataCol.ColumnName;
}
return colnames;
}
}
}
生成操作日志
a.建表
b.生成Model和DAL代码
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace HRMSys.Model
{
public class OperationLog
{
public System.Guid Id { get; set; }
public System.Guid OperatorId { get; set; }
public System.DateTime MakeDate { get; set; }
public System.String ActionDesc { get; set; }
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using HRMSys.Model;
using System.Data;
namespace HRMSys.DAL
{
public class OperationLogDAL
{
public OperationLog ToModel(DataRow row)
{
OperationLog model = new OperationLog();
model.Id = (System.Guid)SqlHelper.FromDBNull(row["Id"]);
model.OperatorId = (System.Guid)SqlHelper.FromDBNull(row["OperatorId"]);
model.MakeDate = (System.DateTime)SqlHelper.FromDBNull(row["MakeDate"]);
model.ActionDesc = (System.String)SqlHelper.FromDBNull(row["ActionDesc"]);
return model;
}
public OperationLog[] ListAll()
{
DataTable table = SqlHelper.ExecuteReader("select * from OperationLog");
OperationLog[] items = new OperationLog[table.Rows.Count];
for (int i = 0; i < table.Rows.Count; i++)
{
OperationLog item = ToModel(table.Rows[i]);
items[i] = item;
}
return items;
}
public OperationLog GetById(Guid id)
{
DataTable table = SqlHelper.ExecuteReader("select * from OperationLog where Id=@Id", new SqlParameter("@Id", id));
if (table.Rows.Count <= 0)
{
return null;
}
else if (table.Rows.Count == 1)
{
return ToModel(table.Rows[0]);
}
else
{
throw new Exception();
}
}
public void Insert(Guid operatorId,string actionDesc)
{
SqlHelper.ExecuteNonQuery(@"INSERT INTO T_OperationLog
(Id,OperatorId,MakeDate,ActionDesc) values
(newid(),@OperatorId,getdate(),@ActionDesc)"
, new SqlParameter("@OperatorId", operatorId)
//, new SqlParameter("@MakeDate", SqlHelper.ToDBValue(model.MakeDate))
, new SqlParameter("@ActionDesc", actionDesc)
);
}
public void DeleteById(Guid id)
{
SqlHelper.ExecuteNonQuery("delete from T_OperationLog where Id=@Id"
, new SqlParameter("@Id", id));
}
public void Update(OperationLog model)
{
SqlHelper.ExecuteNonQuery(@"Update T_OperationLog set OperatorId=@OperatorId,
MakeDate=@MakeDate,
ActionDesc=@ActionDesc where Id=@Id"
, new SqlParameter("@OperatorId", SqlHelper.ToDBValue(model.OperatorId))
, new SqlParameter("@MakeDate", SqlHelper.ToDBValue(model.MakeDate))
, new SqlParameter("@ActionDesc", SqlHelper.ToDBValue(model.ActionDesc))
, new SqlParameter("@Id", model.Id));
}
}
}
c.在关键地方插入到表中记录操作
登录：
string myMd5 = CommonHelper.GetMD5(pwdPassword.Password+CommonHelper.GetPasswordSalt());
if (myMd5 == op.Password)
{
//操作日志
new OperationLogDAL().Insert(op.Id,"登录成功！");
//把登录操作者的Id保存到全局的“Session”存到Application.Current.Properties里面的在程序的其他地方也可以取
Application.Current.Properties["OperatorId"]=op;
DialogResult = true;
}
else
{
//操作日志
new OperationLogDAL().Insert(op.Id, "尝试登录，密码错误！");
MessageBox.Show("密码错误，请重新输入！");
return;
}
软删除：
//操作日志
Guid operatorId=(Guid)Application.Current.Properties["OperatorId"];
new OperationLogDAL().Insert(operatorId, "删除管理员" + op.UserName + "成功！");
将该方法封装到commonHelper中
//封装方法用于写操作日志：
//获得当前登录用户的Id
public static Guid GetOperatorId()
{
Guid operatorId = (Guid)Application.Current.Properties["OperatorId"];
return operatorId;
}
封装后在关键地方可直接插入到操作日志数据库中
//系统日志
Guid operatorId = CommonHelper.GetOperatorId();
new OperationLogDAL().Insert(operatorId, "修改操作员" + op.UserName + "成功！");
操作日志的复合搜索
private void btnSearch_Click(object sender, RoutedEventArgs e)
{
List<string> whereList = new List<string>();
List<SqlParameter> paramsList = new List<SqlParameter>();
if(cbSearchByOperator.IsChecked==true)
{
if(cmbOperator.SelectedIndex<0)
{
MessageBox.Show("请选择操作员！");
return;
}
whereList.Add("Operator=@OperatorId");
paramsList.Add(new SqlParameter("@OperatorId"
,cmbOperator.SelectedValue));
}
if(cbSearchByMakeDate.IsChecked==true)
{
if(dpBeginDate.SelectedDate==null||dpEndDate.SelectedDate==null)
{
MessageBox.Show("请指定操作时间区间！");
return;
}
whereList.Add("MakeDate Between @BeginDate and @EndDate");
paramsList.Add(new SqlParameter("@BeginDate",dpBeginDate.SelectedDate));
paramsList.Add(new SqlParameter("@EndDate",dpEndDate.SelectedDate));
}
if(cbSearchByMakeDate.IsChecked==true)
{
if(txtAcionDesc.Text==null)
{
MessageBox.Show("请指明操作简要描述！");
return;
}
whereList.Add("ActionDesc like @ActionDesc");
paramsList.Add(new SqlParameter("@ActionDesc",
"%"+txtAcionDesc.Text+"%"));
}
}
<Window x:Class="HRMSys.OperationLogWin.OperationLogWindow"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
Title="OperationLogWindow" Height="800" Width="600" Loaded="Window_Loaded">
<Grid>
<DockPanel>
<GroupBox Height="100" Header="搜索条件" DockPanel.Dock="Top">
<Grid>
<CheckBox Content="操作员" Height="16" HorizontalAlignment="Left" Margin="6,6,0,0" Name="cbSearchByOperator" VerticalAlignment="Top" />
<CheckBox Content="操作日期" Height="16" HorizontalAlignment="Left" Margin="198,6,0,0" Name="cbSearchByMakeDate" VerticalAlignment="Top" />
<CheckBox Content="操作描述" Height="16" HorizontalAlignment="Left" Margin="11,45,0,0" Name="cbSearchByAction" VerticalAlignment="Top" />
<ComboBox DisplayMemberPath="UserName" SelectedValuePath="Id" Height="23" HorizontalAlignment="Left" Margin="72,6,0,0" Name="cmbOperator" VerticalAlignment="Top" Width="106" />
<DatePicker Height="25" HorizontalAlignment="Left" Margin="272,4,0,0" Name="dpBeginDate" VerticalAlignment="Top" Width="115" />
<DatePicker Height="25" HorizontalAlignment="Left" Margin="445,4,0,0" Name="dpEndDate" VerticalAlignment="Top" Width="115" />
<TextBlock Height="23" HorizontalAlignment="Left" Margin="398,5,0,0" Name="textBlock1" Text="到" VerticalAlignment="Top" />
<TextBox Height="23" HorizontalAlignment="Left" Margin="95,38,0,0" Name="txtAcionDesc" VerticalAlignment="Top" Width="120" />
<Button Content="搜索" Height="23" HorizontalAlignment="Left" Margin="264,38,0,0" Name="btnSearch" VerticalAlignment="Top" Width="75" Click="btnSearch_Click" />
</Grid>
</GroupBox>
<DataGrid DockPanel.Dock="Top" Name="datagrid" IsReadOnly="True" AutoGenerateColumns="False">
<DataGrid.Columns>
<DataGridComboBoxColumn Header="操作员" SelectedValueBinding="{Binding OperatorId}"
SelectedValuePath="Id"
DisplayMemberPath="UserName" x:Name="colOperator"></DataGridComboBoxColumn>
<DataGridTextColumn Header="日期" Binding="{Binding MakeDate}"></DataGridTextColumn>
<DataGridTextColumn Header="说明" Binding="{Binding ActionDesc}"></DataGridTextColumn>
</DataGrid.Columns>
</DataGrid>
</DockPanel>
</Grid>
</Window>
//必须勾选一个查询条件
if(whereList.Count<=0)
{
MessageBox.Show("至少选择一个查询条件！");
return;//防止查询结果过多
}
string sql = "select * from T_OperationLog where "+string.Join(" and",whereList);
OperationLogDAL层中新增方法：
public OperationLog[] Search(string sql,SqlParameter[] parameters)
{
DataTable table = SqlHelper.ExecuteReader(sql,parameters);
OperationLog[] logs=new OperationLog[table.Rows.Count];
for (int i = 0; i < table.Rows.Count;i++ )
{
logs[i] = ToModel(table.Rows[i]);
}
return logs;
}
private void btnSearch_Click(object sender, RoutedEventArgs e)
{
List<string> whereList = new List<string>();
List<SqlParameter> paramsList = new List<SqlParameter>();
if(cbSearchByOperator.IsChecked==true)
{
if(cmbOperator.SelectedIndex<0)
{
MessageBox.Show("请选择操作员！");
return;
}
whereList.Add("OperatorId=@OperatorId");
paramsList.Add(new SqlParameter("@OperatorId"
,cmbOperator.SelectedValue));
}
if(cbSearchByMakeDate.IsChecked==true)
{
if(dpBeginDate.SelectedDate==null||dpEndDate.SelectedDate==null)
{
MessageBox.Show("请指定操作时间区间！");
return;
}
whereList.Add("MakeDate Between @BeginDate and @EndDate");
paramsList.Add(new SqlParameter("@BeginDate",dpBeginDate.SelectedDate));
paramsList.Add(new SqlParameter("@EndDate",dpEndDate.SelectedDate));
}
if(cbSearchByMakeDate.IsChecked==true)
{
if(txtAcionDesc.Text==null)
{
MessageBox.Show("请指明操作简要描述！");
return;
}
whereList.Add("ActionDesc like @ActionDesc");
paramsList.Add(new SqlParameter("@ActionDesc",
"%"+txtAcionDesc.Text+"%"));
}
//必须勾选一个查询条件
if(whereList.Count<=0)
{
MessageBox.Show("至少选择一个查询条件！");
return;//防止查询结果过多
}
string sql = "select * from T_OperationLog where "+string.Join(" and",whereList);
OperationLog[] logs= new OperationLogDAL().Search(sql,paramsList.ToArray());
datagrid.ItemsSource = logs;
}
private void Window_Loaded(object sender, RoutedEventArgs e)
{
Operator[] operators=new OperatorDAL().ListAll();
cmbOperator.ItemsSource = operators;
colOperator.ItemsSource=operators;
}
系统设置
系统配置很多,存到一张表中
设计表:
代码生成器Model、DAL
书写方法：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using HRMSys.Model;
using System.Data;
namespace HRMSys.DAL
{
public class SettingDAL
{
public Setting ToModel(DataRow row)
{
Setting model = new Setting();
model.Id = (System.Guid)SqlHelper.FromDBNull(row["Id"]);
model.Name = (System.String)SqlHelper.FromDBNull(row["Name"]);
model.Value = http://www.mamicode.com/(System.String)SqlHelper.FromDBNull(row["Value"]);
return model;
}
public Setting[] ListAll()
{
DataTable table = SqlHelper.ExecuteReader("select * from T_Setting");
Setting[] items = new Setting[table.Rows.Count];
for (int i = 0; i < table.Rows.Count; i++)
{
Setting item = ToModel(table.Rows[i]);
items[i] = item;
}
return items;
}
public Setting GetById(Guid id)
{
DataTable table = SqlHelper.ExecuteReader("select * from T_Setting where Id=@Id", new SqlParameter("@Id", id));
if (table.Rows.Count <= 0)
{
return null;
}
else if (table.Rows.Count == 1)
{
return ToModel(table.Rows[0]);
}
else
{
throw new Exception();
}
}
public void Insert(Setting model)
{
SqlHelper.ExecuteNonQuery(@"INSERT INTO T_Setting
(Id,Name,Value)
values (newid(),@Name,@Value)", new SqlParameter("@Id", SqlHelper.ToDBValue(model.Id))
, new SqlParameter("@Name", SqlHelper.ToDBValue(model.Name))
, new SqlParameter("@Value", SqlHelper.ToDBValue(model.Value))
);
}
public void DeleteById(Guid id)
{
SqlHelper.ExecuteNonQuery("delete from T_Setting where Id=@Id"
, new SqlParameter("@Id", id));
}
public void Update(Setting model)
{
SqlHelper.ExecuteNonQuery(@"Update T_Setting set Name=@Name,
Value=http://www.mamicode.com/@Value where Id=@Id"
, new SqlParameter("@Name", SqlHelper.ToDBValue(model.Name))
, new SqlParameter("@Value", SqlHelper.ToDBValue(model.Value))
, new SqlParameter("@Id", model.Id));
}
public void SetValue(string name, string value)
{
int i = SqlHelper.ExecuteNonQuery("Update T_Setting set Value=http://www.mamicode.com/@Value where Name=@Name",
new SqlParameter("@Value", value),
new SqlParameter("@Name", name));
if (i != 1)//只可能出现在开发、测试阶段
{
throw new Exception("影响行数不是1，而是" + i);
}
}
public void SetValue(string name, bool value)
{
SetValue(name, value.ToString());
}
public void SetValue(string name, int value)
{
SetValue(name, value.ToString());
}
public string GetValue(string name)
{
DataTable table = SqlHelper.ExecuteReader("select Value from T_Setting where Name=@Name",
new SqlParameter("@Name", name));
if (table.Rows.Count <= 0)
{
throw new Exception(name + "不存在！");
}
else if (table.Rows.Count > 1)
{
throw new Exception("出现"
+ table.Rows.Count + "条Name=" + name + "的Settings数据");
}
else
{
DataRow row = table.Rows[0];
return (string)row["Value"];
}
}
//todo:重载！
public bool GetBoolValue(string name)
{
return Convert.ToBoolean(GetValue(name));
}
public int GetIntValue(string name)
{
return Convert.ToInt32(GetValue(name));
}
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Shapes;
using HRMSys.DAL;
namespace HRMSys.OperatorWin
{
/// <summary>
/// SettingWindow.xaml 的交互逻辑
/// </summary>
public partial class SettingWindow : Window
{
public SettingWindow()
{
InitializeComponent();
}
private void Window_Loaded(object sender, RoutedEventArgs e)
{
SettingDAL dal = new SettingDAL();
txtCompanyName.Text = dal.GetValue("公司名称");
txtCompanySite.Text = dal.GetValue("公司网站");
cbBirthDayPrompt.IsChecked = dal.GetBoolValue("启用生日提醒");
txtBirthDayDays.Text = dal.GetValue("生日提醒天数");
txtEmployeeNumberPrefix.Text = dal.GetValue("员工工号前缀");
}
private void btnSave_Click(object sender, RoutedEventArgs e)
{
SettingDAL dal = new SettingDAL();
dal.SetValue("公司名称", txtCompanyName.Text);
dal.SetValue("公司网站", txtCompanySite.Text);
dal.SetValue("启用生日提醒", (bool)cbBirthDayPrompt.IsChecked);
dal.SetValue("生日提醒天数", txtBirthDayDays.Text);
dal.SetValue("员工工号前缀", txtEmployeeNumberPrefix.Text);
DialogResult = true;
}
}
}
<Window x:Class="HRMSys.OperatorWin.SettingWindow"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
Title="SettingWindow" Height="300" Width="300" Loaded="Window_Loaded">
<Grid>
<TextBlock Height="23" HorizontalAlignment="Left" Margin="8,10,0,0" Name="textBlock1" Text="公司名称" VerticalAlignment="Top" />
<TextBox Height="23" HorizontalAlignment="Left" Margin="62,0,0,0" Name="txtCompanyName" VerticalAlignment="Top" Width="145" />
<CheckBox Content="启用生日提醒，提前天数" Height="16" HorizontalAlignment="Left" Margin="8,77,0,0" Name="cbBirthDayPrompt" VerticalAlignment="Top" />
<TextBlock Height="23" HorizontalAlignment="Left" Margin="8,39,0,0" Name="textBlock2" Text="公司网站" VerticalAlignment="Top" />
<TextBox Height="23" HorizontalAlignment="Left" Margin="62,38,0,0" Name="txtCompanySite" VerticalAlignment="Top" Width="145" />
<TextBox Height="23" HorizontalAlignment="Left" Margin="165,70,0,0" Name="txtBirthDayDays" VerticalAlignment="Top" Width="42" />
<TextBlock Height="23" HorizontalAlignment="Left" Margin="11,105,0,0" Name="textBlock3" Text="工号前缀" VerticalAlignment="Top" />
<TextBox Height="23" HorizontalAlignment="Left" Margin="62,104,0,0" Name="txtEmployeeNumberPrefix" VerticalAlignment="Top" Width="145" />
<Button Content="保存" Height="23" HorizontalAlignment="Left" Margin="153,179,0,0" Name="btnSave" VerticalAlignment="Top" Width="75" Click="btnSave_Click" />
</Grid>
</Window>
为什么不把配置文件放到App.Config中
云操作：配置信息、Md5盐放到数据库中
好的处理异常的习惯
不要随便try catch 代码中没有必要每个地方都try catch 不要只catch不处理，会隐瞒错误，造成数据混乱，或者好像保存了，其实没保存
异常看不见不等于没有，程序中出现未处理异常会直接退出，每个地方都try catch太麻烦，只要在App中处理DispatcherUnhandleException统一进行异常处理
try
{
table = ExecuteReader(@"SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = ‘BASE TABLE‘");
}
catch(Exception sqlex)
{
MessageBox.Show("连接数据库出错！错误消息："+sqlex.Message);
return;
}
App.xaml
<Application x:Class="HRMSys.App"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
StartupUri="MainWindow.xaml" DispatcherUnhandledException="Application_DispatcherUnhandledException">
<Application.Resources>
</Application.Resources>
</Application>
App.xml.cs
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Windows;
App.xaml.cs
namespace HRMSys
{
/// <summary>
/// App.xaml 的交互逻辑
/// </summary>
public partial class App : Application
{
private void Application_DispatcherUnhandledException(object sender, System.Windows.Threading.DispatcherUnhandledExceptionEventArgs e)
{
MessageBox.Show("程序中出现严重错误，请联系系统开发员！"+e.Exception.Message);
e.Handled = true;//表示异常已经处理了
}//程序中发生未处理异常的时候该方法会被调用
}
}
工资表的生成(需求:进入系统,选择针对哪个月生生成工资,选择部门(帐套),生成这个月份下部门的所有员工的初始工资单,然后可以手工调整,调整后打印工资表和工资单,如果已经存在可以覆盖
如果已经存在可以覆盖重新生成)
降低难度，公开课不讲解自定义工资项，固定是：基本工资、奖金、绩效奖金、罚款，直接生成工资表的功能（生成之前判断是否已经存在）。工资表允许编辑并且打印工资条
进入系统，选择针对哪个月生成工资，选择部门（帐套），生成这个月份下部门的所有员工的初始工资单（如果已经存在可以覆盖），可以手工调整，调整后打印工资表和工资条
设计数据库结构：(主从明细表)
工资表表头： Id、Year、Month、DepartmentId
工资表明细：Id、表头Id、EmployeeId、奖金、基本工资、罚款、其他工资项、（应得工资）
表头表
明细表
主从表,第一张表为主表,第二张表为从表,从表中有个字段指向主表,(SheetId指向主表的Id)
接着搭建三层
自动生成(代码生成器 )
………………
接下来做生成工资单
BuildSalarySheetWindow
<Grid>
<ComboBox Height="23" HorizontalAlignment="Left" Margin="16,10,0,0" Name="cmbYear" VerticalAlignment="Top" Width="100" />
<TextBlock Height="23" HorizontalAlignment="Left" Margin="122,12,0,0" Name="textBlock1" Text="年" VerticalAlignment="Top" />
<ComboBox Height="23" HorizontalAlignment="Left" Margin="140,10,0,0" Name="cmbMonth" VerticalAlignment="Top" Width="120" />
<TextBlock Height="23" HorizontalAlignment="Left" Margin="282,7,0,0" Name="textBlock2" Text="月" VerticalAlignment="Top" />
<ComboBox Height="23" HorizontalAlignment="Left" Margin="320,12,0,0" Name="cmbDept" VerticalAlignment="Top" Width="120" DisplayMemberPath="Name" SelectedValuePath="Id" />
<Button Content="生成工资表" Height="23" HorizontalAlignment="Left" Margin="464,11,0,0" Name="btnCreateSalarySheet" VerticalAlignment="Top" Width="75" Click="btnCreateSalarySheet_Click" />
</Grid>
窗口加载时：
private void Window_Loaded(object sender, RoutedEventArgs e)
{
List<int> listYears = new List<int>();
for (int i = DateTime.Now.Year - 5; i <= DateTime.Now.Year + 5; i++)
{
listYears.Add(i);
}
List<int> months = new List<int>();
for (int i = 1; i <= 12; i++)
{
months.Add(i);
}
cmbYear.ItemsSource = listYears;
cmbMonth.ItemsSource = months;
cmbYear.SelectedValue = http://www.mamicode.com/DateTime.Now.Year;//初始化
cmbMonth.SelectedValue = http://www.mamicode.com/DateTime.Now.Month;
cmbDept.ItemsSource = new DepartmentDAL().ListAll();
}
点击生成工资单时----（年、月、日、部门的工资单）
private void btnCreateSalarySheet_Click(object sender, RoutedEventArgs e)
{
int year = (int)cmbYear.SelectedValue;
int month = (int)cmbMonth.SelectedValue;
Guid deptId = (Guid)cmbDept.SelectedValue;
SalarySheetDAL dal = new SalarySheetDAL();
if (dal.IsExists(year, month, deptId))
{
if (MessageBox.Show("工资单已经生成，是否重新生成？",
"提示", MessageBoxButton.YesNo) == MessageBoxResult.Yes)
{
dal.Clear(year, month, deptId);
}
}
dal.Build(year, month, deptId);
MessageBox.Show("生成成功！");
}
在DAL中写判断是否已经生成工资单函数
/// <summary>
/// 判断是否已经生成指定年月、部门的工资单
/// </summary>
/// <param name="year"></param>
/// <param name="month"></param>
/// <param name="deptId"></param>
/// <returns></returns>
public bool IsExists(int year, int month, Guid deptId)
{
object obj = SqlHelper.ExecuteScalar(@"select count(*) from T_SalarySheet
where Year=@Year and Month=@Month and DepartmentId=@DepartmentId",
new SqlParameter("@Year", year),
new SqlParameter("@Month", month),
new SqlParameter("@DepartmentId", deptId));
return Convert.ToInt32(obj) > 0;
}
若存在 则清除已有的工资单
/// <summary>
/// 清理已经生成的工资单
/// </summary>
/// <param name="year"></param>
/// <param name="month"></param>
/// <param name="deptId"></param>
public void Clear(int year, int month, Guid deptId)
{
object obj = SqlHelper.ExecuteScalar(@"select Id from T_SalarySheet
where Year=@Year and Month=@Month and DepartmentId=@DepartmentId",
new SqlParameter("@Year", year),
new SqlParameter("@Month", month),
new SqlParameter("@DepartmentId", deptId));
Guid sheetId = (Guid)obj;
//一般先删明细表再删主表---先删除子表，再删主表
SqlHelper.ExecuteNonQuery("delete from T_SalarySheetItem where SheetId=@SheetId",
new SqlParameter("@SheetId", sheetId));
SqlHelper.ExecuteNonQuery("delete from T_SalarySheet where Id=@Id",
new SqlParameter("@Id", sheetId));
}
生成工资单：
public void Build(int year, int month, Guid deptId)
{
//生成表头T_SalarySheet
//查询部门的所有员工
//foreach(员工 in 员工们)
//{针对每个员工都生成一条T_SalarySheetItem}
//生成的时候是先生成主表，再生成明细表。因为明细表需要主表的Id（因为明细表需要主表的Id）---与删除的顺序相反
Guid sheetId = Guid.NewGuid();
SqlHelper.ExecuteNonQuery(@"Insert into T_SalarySheet(Id,Year,Month,DepartmentId)
Values(@Id,@Year,@Month,@DepartmentId)",
new SqlParameter("@Id", sheetId), new SqlParameter("@Year", year),
new SqlParameter("@Month", month), new SqlParameter("@DepartmentId", deptId)); -----生成表头
Employee[] employees = new EmployeeDAL().ListByDepment(deptId);
foreach (Employee employee in employees)
{
SqlHelper.ExecuteNonQuery(@"Insert into T_SalarySheetItem
(Id,SheetId,EmployeeId,Bonus,BaseSalary,Fine,Other)
values(newid(),@SheetId,@EmployeeId,0,0,0,0)",
new SqlParameter("@SheetId",sheetId),
new SqlParameter("@EmployeeId", employee.Id));
}
}
在EmployeeDAL中写根据部门查部门所有员工的方法
/// <summary>
/// 查询部门下所有的员工
/// </summary>
/// <param name="deptId"></param>
/// <returns></returns>
public Employee[] ListByDepment(Guid deptId)
{
DataTable table =
SqlHelper.ExecuteDataTable(@"select * from T_Employee
where DepartmentId=@DepartmentId",
new SqlParameter("@DepartmentId", deptId));
return ToEmployees(table);
}
private Employee[] ToEmployees(DataTable table)
{
Employee[] items = new Employee[table.Rows.Count];
for (int i = 0; i < table.Rows.Count; i++)
{
items[i] = ToModel(table.Rows[i]);
}
return items;
}
private void btnCreateSalarySheet_Click(object sender, RoutedEventArgs e)
{
int year = (int)cmbYear.SelectedValue;
int month = (int)cmbMonth.SelectedValue;
Guid deptId = (Guid)cmbDept.SelectedValue;
SalarySheetDAL dal = new SalarySheetDAL();
if (dal.IsExists(year, month, deptId))
{
if (MessageBox.Show("工资单已经生成，是否重新生成？",
"提示", MessageBoxButton.YesNo) == MessageBoxResult.Yes)
{
dal.Clear(year, month, deptId);
}
}
dal.Build(year, month, deptId);
MessageBox.Show("生成成功！");
}
工资表的编辑
在SalarySheetDAL中新增方法
从T_SalarySheetItem中查DataTable
SalarySheetItem[] Items=new SalarySheetItem[table.Rows.count]；
For(int i=0;i<table.rows.count;i++)
{
SalarySheetItem item=new SalarySheetItem();
Item.id=(Guid)
Item.basesalary=(decimal)
Item.
………………
Items[i]=item;
}
public SalarySheetItem[] GetSalarySheetItems(int year, int month, Guid deptId)
{
DataTable tableMain = SqlHelper.ExecuteDataTable(@"select * from T_SalarySheet
where Year=@Year and Month=@Month and DepartmentId=@DepartmentId",
new SqlParameter("@Year", year),
new SqlParameter("@Month", month),
new SqlParameter("@DepartmentId", deptId));
//先查询指定年月、部门的工资数据主表Id。再查询子表信息
//todo:可以使用“子查询”技术来简化
if (tableMain.Rows.Count == 1)
{
Guid sheetId = (Guid)tableMain.Rows[0]["Id"];
DataTable table = SqlHelper.ExecuteDataTable(@"select * from T_SalarySheetItem where
SheetId=@SheetId",
new SqlParameter("@SheetId",sheetId));
SalarySheetItem[] items = new SalarySheetItem[table.Rows.Count];
for (int i = 0; i < table.Rows.Count; i++)
{
DataRow row = table.Rows[i];
SalarySheetItem item = new SalarySheetItem();
item.Id = (Guid)row["Id"];
item.BaseSalary = (decimal)row["BaseSalary"];
item.Bonus = (decimal)row["Bonus"];
item.Fine = (decimal)row["Fine"];
item.Other = (decimal)row["Other"];
item.EmployeeId = (Guid)row["EmployeeId"];
item.SheetId = (Guid)row["SheetId"];
items[i] = item;
}
return items;
}
else if (tableMain.Rows.Count <= 0)
{
return new SalarySheetItem[0];
}
else
{
throw new Exception();
}
}
public void Update(SalarySheetItem item)
{
SqlHelper.ExecuteNonQuery(@"Update T_SalarySheetItem
Set BaseSalary=@BaseSalary,Bonus=@Bonus,
Fine=@Fine,Other=@Other where Id=@Id",
new SqlParameter("@BaseSalary",item.BaseSalary),
new SqlParameter("@Bonus", item.Bonus),
new SqlParameter("@Fine", item.Fine),
new SqlParameter("@Other", item.Other),
new SqlParameter("@Id", item.Id));
}
查出来放到
BuildSalarySheetWindow 中 编辑完后需要更新
更新方法如下 代码生成器已经生成
Public void Update()
{
}
BuildSalarySheetWindow中DataGrid
public void Update(SalarySheetItem item)
{
SqlHelper.ExecuteNonQuery(@"Update T_SalarySheetItem
Set BaseSalary=@BaseSalary,Bonus=@Bonus,
Fine=@Fine,Other=@Other where Id=@Id",
new SqlParameter("@BaseSalary",item.BaseSalary),
new SqlParameter("@Bonus", item.Bonus),
new SqlParameter("@Fine", item.Fine),
new SqlParameter("@Other", item.Other),
new SqlParameter("@Id", item.Id));
}
操作方法与之前类似
<Window x:Class="HRMSys.UI.BuildSalarySheetWindow"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
Title="BuildSalarySheetWindow" Height="300" Width="600" Loaded="Window_Loaded">
<Grid>
<ComboBox Height="23" HorizontalAlignment="Left" Margin="16,10,0,0" Name="cmbYear" VerticalAlignment="Top" Width="100" />
<TextBlock Height="23" HorizontalAlignment="Left" Margin="122,12,0,0" Name="textBlock1" Text="年" VerticalAlignment="Top" />
<ComboBox Height="23" HorizontalAlignment="Left" Margin="140,10,0,0" Name="cmbMonth" VerticalAlignment="Top" Width="120" />
<TextBlock Height="23" HorizontalAlignment="Left" Margin="282,7,0,0" Name="textBlock2" Text="月" VerticalAlignment="Top" />
<ComboBox Height="23" HorizontalAlignment="Left" Margin="320,12,0,0" Name="cmbDept" VerticalAlignment="Top" Width="120" DisplayMemberPath="Name" SelectedValuePath="Id" />
<Button Content="生成工资表" Height="23" HorizontalAlignment="Left" Margin="464,11,0,0" Name="btnCreateSalarySheet" VerticalAlignment="Top" Width="75" Click="btnCreateSalarySheet_Click" />
<DataGrid AutoGenerateColumns="False" Height="200" HorizontalAlignment="Left" Margin="21,48,0,0" Name="datagridItems" VerticalAlignment="Top" Width="545" RowEditEnding="datagridItems_RowEditEnding">
<DataGrid.Columns>
<DataGridComboBoxColumn Width="100" IsReadOnly="True" x:Name="colEmployee" Header="员工" DisplayMemberPath="Name"
SelectedValuePath="Id" SelectedValueBinding="{Binding EmployeeId}"></DataGridComboBoxColumn>
<DataGridTextColumn Width="100" Header="基本工资" Binding="{Binding BaseSalary,UpdateSourceTrigger=PropertyChanged}"></DataGridTextColumn>
<DataGridTextColumn Width="100" Header="奖金" Binding="{Binding Bonus,UpdateSourceTrigger=PropertyChanged}"></DataGridTextColumn>
<DataGridTextColumn Width="100" Header="罚款" Binding="{Binding Fine,UpdateSourceTrigger=PropertyChanged}"></DataGridTextColumn>
<DataGridTextColumn Width="100" Header="其他" Binding="{Binding Other,UpdateSourceTrigger=PropertyChanged}"></DataGridTextColumn>
</DataGrid.Columns>
</DataGrid>
</Grid>
</Window>
Datagrid.itemssource=new ().listAll();
private void btnCreatSalarySheet_Click(object sender, RoutedEventArgs e)
{
int year = (int)cmbYear.SelectedValue;
int month = (int)cmbMonth.SelectedValue;
Guid deptId = (Guid)cmbDept.SelectedValue;
SalarySheetDAL dal = new SalarySheetDAL();
if (dal.IsExists(year, month, deptId))
{
if (MessageBox.Show("工资单已经生成，是否重新生成？",
"提示", MessageBoxButton.YesNo) == MessageBoxResult.Yes)
{
dal.Clear(year, month, deptId);
}
}
dal.Build(year, month, deptId);
//MessageBox.Show("生成成功！");
datagridItems.ItemsSource = new SalarySheetDAL().GetSalarySheetItems(year,month,deptId);
}
此时生成的工资表员工列信息为空
现给其ItemsSource赋值
private void btnCreatSalarySheet_Click(object sender, RoutedEventArgs e)
{
int year = (int)cmbYear.SelectedValue;
int month = (int)cmbMonth.SelectedValue;
Guid deptId = (Guid)cmbDept.SelectedValue;
SalarySheetDAL dal = new SalarySheetDAL();
if (dal.IsExists(year, month, deptId))
{
if (MessageBox.Show("工资单已经生成，是否重新生成？",
"提示", MessageBoxButton.YesNo) == MessageBoxResult.Yes)
{
dal.Clear(year, month, deptId);
}
}
dal.Build(year, month, deptId);
//MessageBox.Show("生成成功！");
//处理员工列
colEmployee.ItemsSource = new EmployeeDAL().ListByDepment(deptId);
datagridItems.ItemsSource = new SalarySheetDAL().GetSalarySheetItems(year,month,deptId);
<DataGridTextColumn Width="100" Header="基本工资" Binding="{Binding BaseSalary,UpdateSourceTrigger=PropertyChanged}"></DataGridTextColumn>
<DataGridTextColumn Width="100" Header="奖金" Binding="{Binding Bonus,UpdateSourceTrigger=PropertyChanged}"></DataGridTextColumn>
<DataGridTextColumn Width="100" Header="罚款" Binding="{Binding Fine,UpdateSourceTrigger=PropertyChanged}"></DataGridTextColumn>
<DataGridTextColumn Width="100" Header="其他" Binding="{Binding Other,UpdateSourceTrigger=PropertyChanged}"></DataGridTextColumn>
UpdateSourceTrigger=PropertyChanged}" 改变数据源会改变界面,同样界面的改变也会改变数据源此方法实现此功能改变界面同步到改变数据源
监听DataGrid的RowEditEnding事件（行编辑结束）
private void datagridItems_RowEditEnding(object sender, DataGridRowEditEndingEventArgs e)
{
//Binding="{Binding BaseSalary,UpdateSourceTrigger=PropertyChanged}"
SalarySheetItem item = (SalarySheetItem)e.Row.DataContext;
//e.Row.DataContext修改后的数据对象
new SalarySheetDAL().Update(item);
}
界面值的改变，会导致数据库中的值被改变
分布式事务
引言：
public void Clear(int year, int month, Guid deptId)
{
object obj = SqlHelper.ExecuteScalar(@"select Id from T_SalarySheet
where Year=@Year and Month=@Month and DepartmentId=@DepartmentId",
new SqlParameter("@Year", year),
new SqlParameter("@Month", month),
new SqlParameter("@DepartmentId", deptId));
Guid sheetId = (Guid)obj;
//一般先删明细表再删主表
//todo：使用事务保证原子性
SqlHelper.ExecuteNonQuery("delete from T_SalarySheetItem where SheetId=@SheetId",
new SqlParameter("@SheetId", sheetId));
SqlHelper.ExecuteNonQuery("delete from T_SalarySheet where Id=@Id",
new SqlParameter("@Id", sheetId));
}
这里的删除有两部，先删子表，在删主表，但是如果第一张表成功删除，第二张表的删除过程中出现异常，删除未成功，但此时，第一张表的数据已经删除了，这样会有问题
比如银行转账A给B转3000块钱
分两部：A-3000
B+3000
若A-3000成功但执行第二步时出现异常，于是乎就会出现一个很诡异的现象，A的账户少了3000但是B的账户并没有多3000,3000块钱；离奇消失了
此时需要一个回滚的操作。叫做数据库的原子性，要么全部成功，要么全部失败。
事务：原子性
1.打开服务：MSDTC服务
在调用Ado.net和安装sql server服务器的机器上均需要启动
2.添加对System.Transactions的引用
中间某一步出现异常，则ts.Complete()不会完成，事务不会成功
using (TransactionScope ts = new TransactionScope())
{
DepartmentDAL dal = new DepartmentDAL();
dal.Insert("测试1");
//EmployeeDAL employeeDAl = new EmployeeDAL();
//Employee emp = new Employee();
//emp.
//employeeDAl.Insert(emp);
Operator op = new Operator();
op.UserName = "hello";
op.Password = "33333";
op.RealName = "haha";
new OperatorDAL().Insert(op);
ts.Complete();
}
水晶报表基础
GDI(GDI+)编程(本质)
新建窗口: ReportTestWindow
UI修改构建目标
.net framework 4 client profile 去掉了服务端的类而.net framework 4包含了服务端的类本次编程会用到服务端的类，因此需要修改目标框架
添加水晶报表选项卡：
1.
2.
3.
窗口加载时：
private void Window_Loaded(object sender, RoutedEventArgs e)
{
OperatorDAL dal = new OperatorDAL();
Operator[] operators= dal.ListAll();
}
private void Window_Loaded(object sender, RoutedEventArgs e)
{
OperatorDAL dal = new OperatorDAL();
Operator[] operators= dal.ListAll();
TestCrystalReport1 rpt = new TestCrystalReport1();
rpt.SetDataSource(operators);//报表展示的数据源
crystalReportsViewer1.ViewerCore.ReportSource = rpt;//给crystalReportsViewer1设置数据源
}
批量造数据：
insert into T_Operator(UserName,Password)
select UserName,Password from T_Operator
没有找到Operator类
因此需要在UI层直接新建一个类
新建一个类ReportOperator
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace HRMSys
{
public class ReportOperator
{
public string UserName { get; set; }
public string Password { get; set; }
}
}
在测试窗口加载中新增代码：
private void Window_Loaded(object sender, RoutedEventArgs e)
{
OperatorDAL dal = new OperatorDAL();
Operator[] operators= dal.ListAll();
ReportOperator[] rtpOperators =
new ReportOperator[operators.Length];
for (int i = 0; i < rtpOperators.Length;i++ )
{
ReportOperator rptOp = new ReportOperator();
rptOp.UserName = operators[i].UserName;
rptOp.Password = operators[i].Password;
rtpOperators[i] = rptOp;
}
TestCrystalReport1 rpt = new TestCrystalReport1();
rpt.SetDataSource(rtpOperators);//报表展示的数据源
crystalReportsViewer1.ViewerCore.ReportSource = rpt;//给crystalReportsViewer1设置数据源
}
将该窗口设置为启动项
报错：
运行结果：
//步骤：1.设计rpt报表2.从BLL层（DAL）中取数据，扔给rpt实例，然后通过crystalReportsViewer展示
<my:CrystalReportsViewer HorizontalAlignment="Left" Name="crystalReportsViewer1" VerticalAlignment="Top" />
把宽、高Margin去掉就会全屏显示了
<DockPanel>
<GroupBox Header="查询条件" Height="80" DockPanel.Dock="Top"></GroupBox>
<my:CrystalReportsViewer HorizontalAlignment="Left" Name="crystalReportsViewer1" VerticalAlignment="Top" />
</DockPanel>
线条对象
private void Window_Loaded(object sender, RoutedEventArgs e)
{
OperatorDAL dal = new OperatorDAL();
Operator[] operators= dal.ListAll();
ReportOperator[] rtpOperators =
new ReportOperator[operators.Length];
for (int i = 0; i < rtpOperators.Length;i++ )
{
ReportOperator rptOp = new ReportOperator();
rptOp.UserName = operators[i].UserName;
rptOp.Password = operators[i].Password;
rtpOperators[i] = rptOp;
}
TestCrystalReport1 rpt = new TestCrystalReport1();
rpt.SetDataSource(rtpOperators);//报表展示的数据源
rpt.SetParameterValue("部门名称","产品开发部");
crystalReportsViewer1.ViewerCore.ReportSource = rpt;//给crystalReportsViewer1设置数据源
//步骤：1.设计rpt报表2.从BLL层（DAL）中取数据，扔给rpt实例，然后通过crystalReportsViewer展示
}
运行总计字段;
水晶报表实现打印工资表：
1.打印窗口：
PrintSalarySheetWindow
2.在UI层添加新建项→Report→空白表→
3.添加打印模型类（UI层中）SalarySheetItemRpt
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace HRMSys
{
class SalarySheetItemRpt
{
public System.Decimal Bonus { get; set; }
public System.Decimal BaseSalary { get; set; }
public System.Decimal Fine { get; set; }
public System.Decimal Other { get; set; }
public string EmployeeName { get; set; }
}
}
4.
增加工资总计字段
报表头、年、月、部门工资，传三个参数
报表设计完成：
回到PrintSalarySheetWindow（工资打印窗口）
查询XAML代码如下
<GroupBox DockPanel.Dock="Top" Header="查询条件" Height="80">
<Grid>
<TextBox Height="23" HorizontalAlignment="Left" Margin="25,6,0,0" Name="txtYear" VerticalAlignment="Top" Width="78" />
<TextBox Height="23" HorizontalAlignment="Right" Margin="0,6,509,0" Name="txtMonth" VerticalAlignment="Top" Width="78" />
<TextBlock Height="23" HorizontalAlignment="Left" Margin="109,9,0,0" Name="textBlock1" Text="年" VerticalAlignment="Top" />
<TextBlock Height="23" HorizontalAlignment="Left" Margin="263,6,0,0" Name="textBlock2" Text="月" VerticalAlignment="Top" />
<TextBlock Height="22" HorizontalAlignment="Left" Margin="446,9,0,0" Name="textBlock3" Text="部门" VerticalAlignment="Top" />
<ComboBox Height="23" HorizontalAlignment="Left" Margin="311,6,0,0" Name="cmbDepts" VerticalAlignment="Top" Width="120" />
<Button Content="查询" Height="23" HorizontalAlignment="Right" Margin="0,10,139,0" Name="btnQuery" VerticalAlignment="Top" Width="75" />
</Grid>
</GroupBox>
<ComboBox DisplayMemberPath="Name" SelectedValuePath="Id"
点击查询之后：
private void btnQuery_Click(object sender, RoutedEventArgs e)
{
int year = Convert.ToInt32(txtYear.Text);
int month = Convert.ToInt32(txtMonth.Text);
Guid deptId = (Guid)cmbDepts.SelectedValue;
SalarySheetDAL sheetDAL = new SalarySheetDAL();
if (sheetDAL.IsExists(year, month, deptId) == false)
{
MessageBox.Show("还未生成工资！");
return;
}
SalarySheetItem[] items=
sheetDAL.GetSalarySheetItems(year,month,deptId);
SalarySheetItemRpt[] rptItems=new SalarySheetItemRpt[items.Length];
for (int i = 0; i < items.Length;i++ )
{
SalarySheetItem item=items[i];
SalarySheetItemRpt rptItem = new SalarySheetItemRpt();
rptItem.BaseSalary = item.BaseSalary;
rptItem.Bonus = item.Bonus;
rptItem.Fine = item.Bonus;
rptItem.Other = item.Other;
rptItem.EmployeeName
= new EmployeeDAL().GetById(item.EmployeeId).Name;
rptItems[i] = rptItem;
}
SalarySheetReport report = new SalarySheetReport();
report.SetDataSource(rptItems);
report.SetParameterValue("年",year);
report.SetParameterValue("月", month);
report.SetParameterValue("部门名称", cmbDepts.Text);
crystalReportsViewer1.ViewerCore.ReportSource = report;
}
打印工资条
添加线条：
