C#与数据库链接---小练2--查询

******************************数据库的建立**************************************

create database wo0505
on
(
name=wo0505_dat,
filename="D:\peixun\zuoye\VS2010\20150505\sql002\wo0505.mdf",
size=10,
maxsize=50,
filegrowth=10%
)
log on
(
name=wo0505_log,
filename="D:\peixun\zuoye\VS2010\20150505\sql002\wo0505.ldf",
size=10,
maxsize=50,
filegrowth=10%
)

--drop database wo0505;



/*
create database mydb
go
use mydb
go
*/
--民族表
create table Nation
(
    Code varchar(50) primary key,
    Name varchar(50)
)
go
--称谓表
create table Title
(
    Code varchar(50) primary key,
    Name varchar(50)
)
go
--人员基本信息个
create table Info
(
    Code varchar(50) primary key,
    Name varchar(50),
    Sex bit,
    Nation varchar(50) references Nation(Code),
    Birthday datetime,
)
go
--工作简历
create table Work
(
    Ids int identity primary key,
    InfoCode varchar(50) references Info(Code),
    StartDate datetime,
    EndDate datetime,
    Firm varchar(50),
    Depart varchar(50),
    Orders int
)
go
--家庭关系
create table Family
(
    Ids int identity primary key,
    InfoCode varchar(50) references Info(Code),
    Name varchar(50),
    Title varchar(50) references Title(Code),
    Firm varchar(50),
    Orders int
)
go

insert into Nation values(‘n001‘,‘汉族‘)
insert into Nation values(‘n002‘,‘回族‘)
insert into Nation values(‘n003‘,‘满族‘)
insert into Nation values(‘n004‘,‘苗族‘)

insert into Title values(‘T001‘,‘父亲‘)
insert into Title values(‘T002‘,‘母亲‘)
insert into Title values(‘T003‘,‘配偶‘)
insert into Title values(‘T004‘,‘孩子‘)

insert into Info values(‘p001‘,‘胡军‘,1,‘n003‘,‘1985-8-9‘)
insert into Info values(‘p002‘,‘周丹‘,0,‘n001‘,‘1984-4-17‘)
insert into Info values(‘p003‘,‘吴倩‘,0,‘n004‘,‘1981-10-29‘)
insert into Info values(‘p004‘,‘唐墨‘,1,‘n001‘,‘1983-2-25‘)

insert into Work values(‘p001‘,‘2001-9-1‘,‘2003-7-3‘,‘内蒙古呼和浩特市工商管理学院‘,‘经济学院‘,1)
insert into Work values(‘p001‘,‘2003-8-1‘,‘2005-12-31‘,‘中国农业银行二连浩特支行‘,‘信贷科‘,2)
insert into Work values(‘p001‘,‘2006-1-1‘,‘2007-4-30‘,‘中国农业很行包头支行‘,‘人事部‘,3)
insert into Work values(‘p002‘,‘1999-9-1‘,‘2003-6-30‘,‘山东大学‘,‘信息学院‘,1)
insert into Work values(‘p002‘,‘2003-7-1‘,‘2005-3-3‘,‘中国网通济南分公司‘,‘网络部‘,2)
insert into Work values(‘p002‘,‘2005-5-1‘,‘2008-5-3‘,‘胜达网络有限公司‘,‘技术部‘,3)
insert into Work values(‘p003‘,‘1998-9-7‘,‘2002-6-30‘,‘青岛科技大学‘,‘艺术系‘,1)
insert into Work values(‘p003‘,‘2002-8-1‘,‘2003-3-30‘,‘半岛日报社‘,‘美编部‘,2)
insert into Work values(‘p003‘,‘2003-4-1‘,‘2006-10-30‘,‘青岛仲盛房地产有限公司‘,‘广告部‘,3)
insert into Work values(‘p003‘,‘2006-11-1‘,‘2008-4-3‘,‘山东新意网络传媒有限公司‘,‘设计部‘,4)
insert into Work values(‘p004‘,‘2002-9-1‘,‘2006-8-30‘,‘山东师范大学‘,‘中文系‘,1)
insert into Work values(‘p004‘,‘2006-9-1‘,‘2001-7-3‘,‘济南市公共事业局‘,‘秘书处‘,2)

insert into Family values(‘p001‘,‘胡志敬‘,‘T001‘,‘内蒙古呼和浩特市公安局‘,1)
insert into Family values(‘p001‘,‘王丽‘,‘T002‘,‘内蒙古呼和浩特市工委‘,2)
insert into Family values(‘p002‘,‘周成桥‘,‘T001‘,‘山东省省委党校‘,1)
insert into Family values(‘p002‘,‘吕娜‘,‘T002‘,‘齐鲁石化济南分公司‘,2)
insert into Family values(‘p003‘,‘吴多运‘,‘T001‘,‘青岛爱华外贸有限公司‘,1)
insert into Family values(‘p003‘,‘刘少萍‘,‘T002‘,‘青岛食品有限公司‘,2)
insert into Family values(‘p003‘,‘王树远‘,‘T003‘,‘海信集团‘,3)
insert into Family values(‘p003‘,‘王娉‘,‘T004‘,‘青岛市四方区机关幼儿园‘,4)
insert into Family values(‘p004‘,‘唐杰‘,‘T001‘,‘山东省丽景型材有限公司‘,1)
insert into Family values(‘p004‘,‘李青‘,‘T002‘,‘济南市城建局‘,2)
insert into Family values(‘p004‘,‘梅婷婷‘,‘T003‘,‘济南市好宜家家居广场‘,3)

*********************************************************************************

******************************C#代码----------查询相应数据*******************************

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;

namespace ConsoleApplication2
{
    class Program
    {
        public const string AB="server=.;database=wo0505;uid=sa;pwd=123";
        static string Getnation(string op)
        {
            string str = "";
            SqlConnection con = new SqlConnection(AB);
            con.Open();

            SqlCommand cmd = con.CreateCommand();
            cmd.CommandText = "select * from Nation where Code=‘"+op+"‘";
            SqlDataReader dr = cmd.ExecuteReader();
            if (dr.HasRows)
            {
                dr.Read();
                str = dr["Name"].ToString();
            }
            else
            {
                str = "null";
            }
            con.Close();
            return str;
        }
        static string Getwork(string ff)
        {
            string str = "";
            SqlConnection con = new SqlConnection(AB);
            con.Open();
            SqlCommand cmd = con.CreateCommand();
            cmd.CommandText = "select * from Work where InfoCode=‘"+ff+"‘";
            SqlDataReader dr = cmd.ExecuteReader();
            while(dr.Read())
            {
                str+=((DateTime)dr["StartDate"]).ToString("yyyy年MM月dd日")+"\t";
                str += ((DateTime)dr["EndDate"]).ToString("yyyy年MM月dd日")+"\t";
                str+=dr["Firm"].ToString()+"\t";
                str+=dr["Depart"].ToString()+"\n";
                
            }
            con.Close();

            return str;
        }
        static void Main(string[] args)
        {
            SqlConnection con=new SqlConnection(AB);
            con.Open();

            SqlCommand cmd=con.CreateCommand();
            cmd.CommandText="select *from Info";
            SqlDataReader dr=cmd.ExecuteReader();
            while(dr.Read())
            {
                string aa=dr["Code"].ToString();
                string bb = dr["Name"].ToString();
                string cc = ((bool)dr["Sex"])?"男":"女";
                string dd = Getnation(dr["Nation"].ToString());
                string ee=((DateTime)dr["Birthday"]).ToString("yyyy年MM月dd日");
                Console.WriteLine(aa+"\t"+bb+"\t"+cc+"\t"+dd+"\t"+ee);
                string ff = Getwork(aa);
                Console.ForegroundColor = ConsoleColor.Blue;
                Console.WriteLine(ff);
                Console.ResetColor();
            }

            con.Close();
            Console.ReadLine();
        }
    }
}