Ado. NET Exercises

static void Main (string[] Args)
{
Practice
1, Let the user enter a code name, find a data (car,info), and display
2, output hint, input 1 Delete the data, enter 2 to modify data, enter 3 to add data
3, when the user input 1, the detected data deleted
4, when the user input 2, prompted to enter other values, the primary key is not modified, input completed, modify this data
5. When the user enters 3, prompts the user to enter all the columns, and then saves to the database

String [] AR = new string[] {"code", "name", "gender", "national", "birthday"};
User input Code
Console.WriteLine ("please Enter the code to be queried");
String code = Console.ReadLine ();

Connecting objects
SqlConnection conn = new SqlConnection ("server=.; DATABASE=MYDB;USER=SA;PWD=DIUSHIDEWUTONG0 ");

Create Command object
SqlCommand CMD = Conn. CreateCommand ();

To the Command object SQL statement
Cmd.commandtext = "select * from Info where [email protected]";
Anti-injection
The variable binding parameter
Cmd. Parameters.clear ();
Cmd. Parameters.addwithvalue ("@code", code);

Open connection
Conn. Open ();

SqlDataReader dr = cmd. ExecuteReader ();

If (dr. Hasrows)
{
While (dr. Read ())
{
int i = 0;

While (i < Dr. Fieldcount)
{
Console.Write (ar[i] + "\ t");
i++;
}
Console.Write ("\ n");
i = 0;
While (i < Dr. Fieldcount)
{
Console.Write (dr[i] + "\ t");
i++;
}
Console.Write ("\ n");
}
}
Else
{
Console.WriteLine ("data not queried");
While (true)
{
Dr. Close ();
Console.WriteLine ("please re-enter the code to be queried");
Code = Console.ReadLine ();

Cmd.commandtext = "select * from Info where [email protected]";
Cmd. Parameters.clear ();
Cmd. Parameters.addwithvalue ("@code", code);
Dr = Cmd. ExecuteReader ();
If (dr. Hasrows)
{
While (dr. Read ())
{
int i = 0;

While (i < Dr. Fieldcount)
{
Console.Write (ar[i] + "\ t");
i++;
}
Console.Write ("\ n");
i = 0;
While (i < Dr. Fieldcount)
{
Console.Write (dr[i] + "\ t");
i++;
}
Console.Write ("\ n");
}
Break
}
Else
{
Console.WriteLine ("data not queried");
}
}
}


While (true)
{
Console.WriteLine ("input 1 Delete the data, enter 2 to modify the data, enter 3 to add data, enter other out of the program");
string s = Console.ReadLine ();
if (s = = "1")
{
If (dr. Hasrows)
{
Console.WriteLine ("confirm Delete Please enter: 1");
int SC = Int. Parse (console.readline ());
if (sc = = 1)
{
Dr. Close ();
Cmd.commandtext = "delete from Info where [email protected]";
Cmd. Parameters.clear ();
Cmd. Parameters.addwithvalue ("@code", code);
Cmd. ExecuteNonQuery ();
Console.WriteLine ("delete succeeded");
}
Else
{
Console.WriteLine ("cancel operation");
}
}
}
else if (s = = "2")//modify Content
{
While (true)
{
Dr. Close ();
Console.Write ("please Enter the name of the column to be modified:");
String str = Console.ReadLine ();
string Gai = "";
Switch (str)
{
Case "name":
Gai = "Name";
Break
Case "gender":
Gai = "Sex";
Break
Case "nation":
Gai = "Nation";
Break
Case "birthday":
Gai = "Birthday";
Break
Default
Console.WriteLine ("input error");
Continue

}
if (gai = = "Sex")
{
Console.Write ("please Enter the content to be modified:");
bool Sex = console.readline () = = "male"? true:false;
Cmd.commandtext = "update Info set" + gai + "=" + sex + "where [email protected]";
Cmd. ExecuteNonQuery ();
}
else if (gai = = "Nation")
{
String Nationcode = "n001";
Console.Write ("please Enter the content to be modified:");
String nation = Console.ReadLine ();
Cmd.commandtext = "select Code from Nation where Name = '" + Nation + "'";
Dr. Close (); Dr = Cmd. ExecuteReader ();
If (dr. Hasrows)
{
Dr. Read ();
Nationcode = dr[0]. ToString ();
}
Cmd.commandtext = "update Info set" + gai + "=" + nationcode + "where [email protected]";
Dr. Close ();
Cmd. ExecuteNonQuery ();
}
Else
{
Console.Write ("please Enter the content to be modified:");
String Neirong = Console.ReadLine ();
Cmd.commandtext = "update Info set" + gai + "=" + neirong + "where [email protected]";
Cmd. ExecuteNonQuery ();
}
Console.WriteLine ("modified successfully!");
Console.WriteLine ("modified Content is:");

Cmd.commandtext = "select * from Info where [email protected]";
Cmd. Parameters.clear ();
Cmd. Parameters.addwithvalue ("@code", code);
Dr = Cmd. ExecuteReader ();
If (dr. Hasrows)
{
While (dr. Read ())
{
int i = 0;

While (i < Dr. Fieldcount)
{
Console.Write (ar[i] + "\ t");
i++;
}
Console.Write ("\ n");
i = 0;
While (i < Dr. Fieldcount)
{
Console.Write (dr[i] + "\ t");
i++;
}
Console.Write ("\ n");
}
}
Console.WriteLine ("do you want to continue Modifying?") Continue to modify input 1, do not modify the input any key ");
int B = Int. Parse (console.readline ());
if (b = = 1)
{
Console.WriteLine ("continue to modify");
}
Else
{
Break
}

}

}
else if (s = = "3")//add data
{
Let the user enter the content to be added
Console.WriteLine ("please Enter the code you want to add:");
Code = Console.ReadLine ();

Console.WriteLine ("please Enter name:");
String name = Console.ReadLine ();

Console.WriteLine ("please Enter Gender: (male or female)");
bool Sex = console.readline () = = "male"? true:false;

Console.WriteLine ("please Enter the nation:");
String nation = Console.ReadLine ();

Console.WriteLine ("please Enter your birthday:");
string birthday = Console.ReadLine ();

String Nationcode = "n001";

Turn the national name into the name of the Family Code
Cmd.commandtext = "select Code from Nation where Name = '" + Nation + "'";
Dr. Close ();

Dr = Cmd. ExecuteReader ();
If (dr. Hasrows)
{
Dr. Read ();
Nationcode = dr[0]. ToString ();
}
Adding data to the Info table
Cmd. CommandText = "insert into Info values ('" + code + "', '" + name + "', '" + sex + "', '" + nationcode + "', '" + birthday + "') )";
Dr. Close ();
Cmd. ExecuteNonQuery ();
Console.WriteLine ("added successfully! ");

Console.WriteLine ("added content:");
Cmd.commandtext = "select * from Info where [email protected]";
Cmd. Parameters.clear ();
Cmd. Parameters.addwithvalue ("@code", code);
Dr = Cmd. ExecuteReader ();
If (dr. Hasrows)
{
While (dr. Read ())
{
int i = 0;

While (i < Dr. Fieldcount)
{
Console.Write (ar[i] + "\ t");
i++;
}
Console.Write ("\ n");
i = 0;
While (i < Dr. Fieldcount)
{
Console.Write (dr[i] + "\ t");
i++;
}
Console.Write ("\ n");
}
}

}
Else
{
Console.WriteLine ("program jumps out");
Break
}
}
Conn. Close ();
Console.ReadLine ();

Ado. NET Exercises