数据访问----实例2

  1 using System;
  2 using System.Collections.Generic;
  3 using System.Linq;
  4 using System.Text;
  5 using System.Threading.Tasks;
  6 using System.Data;
  7 using System.Data.SqlClient;
  8 namespace zz
  9 {
 10     class Program
 11     {
 12         public const string CONSTR = "server=.;database=mydb;uid=sa;pwd=111111";
 13         public static void se()
 14         {
 15             SqlConnection con = new SqlConnection(CONSTR);
 16             con.Open();
 17             SqlCommand cmd = con.CreateCommand();
 18             cmd.CommandText = "select * from info";
 19             SqlDataReader re = cmd.ExecuteReader();
 20             while (re.Read())
 21             {
 22                 string code = re["Code"].ToString();
 23                 string name = re["Name"].ToString();
 24                 string sex = ((bool)re["Sex"])?"男":"女";
 25                 string nation = getNation(re["Nation"].ToString());
 26                 string birthday = ((DateTime)re["Birthday"]).ToString("yyyy年MM月dd日");
 27 
 28                 Console.WriteLine(code + "	" + name + "	" + sex + "	" + nation + "	" + birthday + "
");
 29                 Console.ForegroundColor = ConsoleColor.Yellow;
 30                 Console.WriteLine("**************************个人简历***************************");
 31                 Console.WriteLine(getWork(code));
 32                 Console.ResetColor();
 33                 Console.ForegroundColor = ConsoleColor.Blue;
 34                 Console.WriteLine("**************************家庭情况***************************");
 35                 Console.WriteLine(getFamily(code));
 36                 Console.ResetColor();
 37 
 38             }
 39 
 40             con.Close();
 41 
 42         }
 43         public static string getNation(string nation)
 44         {
 45             string tr = "";
 46             SqlConnection con = new SqlConnection(CONSTR);
 47             con.Open();
 48             SqlCommand cmd = con.CreateCommand();
 49             cmd.CommandText = "select Name from nation where Code='"+nation+"'";
 50             SqlDataReader re=cmd.ExecuteReader();
 51             //if (re.HasRows)
 52             //{
 53             //    re.Read();
 54             //    tr = re["Name"].ToString();
 55             //}
 56             //else
 57             //{
 58             //    tr = "null";
 59             //}
 60             while (re.Read())
 61             {
 62                 tr = re["Name"].ToString();
 63             }
 64 
 65             con.Close();
 66 
 67             return tr;
 68         }
 69         public static string getWork(string code)
 70         {
 71             string tr = "";
 72             SqlConnection con = new SqlConnection(CONSTR);
 73             con.Open();
 74             SqlCommand cmd = con.CreateCommand();
 75             cmd.CommandText = "select StartDate,EndDate,Firm,Depart from work where InfoCode='" + code + "'";
 76             SqlDataReader re = cmd.ExecuteReader();
 77             while (re.Read())
 78             {
 79                 tr += ((DateTime)re["StartDate"]).ToString("yyyy年MM月dd日") + "	";
 80                 tr += ((DateTime)re["EndDate"]).ToString("yyyy年MM月dd日") + "	";
 81                 tr += re["Firm"].ToString() + "	";
 82                 tr += re["Depart"].ToString() + "
";
 83             }
 84             con.Close();
 85             return tr;
 86         }
 87         public static string getFamily(string code)
 88         {
 89             string tr = "";
 90             SqlConnection con = new SqlConnection(CONSTR);
 91             con.Open();
 92             SqlCommand cmd = con.CreateCommand();
 93             cmd.CommandText = "select * from family where InfoCode='" + code + "'";
 94             SqlDataReader re = cmd.ExecuteReader();
 95             while (re.Read())
 96             {
 97                 
 98                 tr += re["Name"].ToString() + "	";
 99                 tr += getTitle(re["title"].ToString()) + "	";
100                 tr += re["Firm"].ToString() + "
";
101               
102             }
103             con.Close();
104 
105             return tr;
106         }
107         public static string getTitle(string title)
108         {
109             string tr = "";
110             SqlConnection con = new SqlConnection(CONSTR);
111             con.Open();
112             SqlCommand cmd = con.CreateCommand();
113             cmd.CommandText = "select * from title where Code='" + title + "'";
114             SqlDataReader re = cmd.ExecuteReader();
115             while (re.Read())
116             {
117 
118                 tr += re["Name"].ToString();
119             }
120             con.Close();
121             return tr;
122         }
123         static void Main(string[] args)
124         {
125             se();
126             Console.ReadKey();
127             Main(args);
128         }
129     }
130 }