20150505 Data Access 2 (practice)

Public Const string CONNECTIONSTRING = "server=.; Database=mydb;uid=sa;pwd=123 ";
static string Getnationname (String code)
{
String str = "";

Search national names according to national code
SqlConnection conn = new SqlConnection (CONNECTIONSTRING);
Conn. Open ();

SqlCommand cmd = conn. CreateCommand ();
Cmd.commandtext = "SELECT * from Nation where code= '" + code + "'";
SqlDataReader dr = cmd. ExecuteReader ();
if (Dr. HasRows)
{
Dr. Read ();
str = dr["Name"]. ToString ();
}
Else
{
str = "Unknown";
}

Conn. Close ();

return str;
}
static string Getworks (String code)
{
String str = "";

SqlConnection conn = new SqlConnection (CONNECTIONSTRING);
Conn. Open ();

SqlCommand cmd = conn. CreateCommand ();
Cmd.commandtext = "SELECT * from work where infocode= '" + code + "'";
SqlDataReader dr = cmd. ExecuteReader ();
while (Dr. Read ())
{
str + = ((DateTime) dr["StartDate"]). ToString ("yyyy years mm DD Day") + "\ T";
str + = ((DateTime) dr["EndDate"]). ToString ("yyyy years mm DD Day") + "\ T";
str + = dr["Firm"]. ToString () + "\ T";
str + = dr["Depart"]. ToString () + "\ n";
}

Conn. Close ();

return str;
}
static void Main (string[] args)
{
Show
SqlConnection conn = new SqlConnection (CONNECTIONSTRING);
Conn. Open ();

SqlCommand cmd = conn. CreateCommand ();
Cmd.commandtext = "Select Info.code,info.name,info.sex,nation.name as Nationname,info.birthday from Info,nation where Info.nation = Nation.code ";
Cmd. CommandText = "Select Code,name,sex, (select name from Nation where Info.nation=nation.code) as Nationname,birthday from Fo ";
Cmd.commandtext = "SELECT * from Info";
SqlDataReader dr = cmd. ExecuteReader ();
while (Dr. Read ())
{
String code = dr["Code"]. ToString ();
String name = dr["Name"]. ToString ();
string sex = ((bool) dr["Sex"])? "Male": "Female";
String nation = Getnationname (dr["Nation"]. ToString ());
string birthday = ((DateTime) dr["Birthday"]). ToString ("yyyy mm month DD day");

Console.foregroundcolor = Consolecolor.yellow;
Console.WriteLine (code + "\ T" + name + "\ t" + sex + "\ T" + Nation + "\ T" + birthday);
Console.resetcolor ();
Show work Resume
Console.WriteLine ("* * * * * * * * * * * *);
Console.WriteLine (Getworks (code));
}

Conn. Close ();

20150505 Data Access 2 (practice)