ADO. NET Study Notes (5)

(3) Data Import and Export Database: using System; using System. collections. generic; using System. componentModel; using System. data; using System. drawing; using System. linq; using System. text; using System. windows. forms; using System. data. sqlClient; using System. IO; using System. configuration; namespace data import and export {public partial class Form1: Form {public Form1 () {InitializeComponent ();} private void odfImport_Click (object sender, E VentArgs e) {// use nvalchar (50) for database fields such as passwords and nicknames that contain Chinese characters, and n for various characters. // note: N must be added before the imported Chinese characters, for example, insert into T_user (Name, NickName) values ('zhx', n' Zhu Hongxing ') if (odfImport. showDialog ()! = DialogResult. OK) {return;} using (FileStream fileStream = File. openRead (odfImport. fileName) {using (StreamReader streamReader = new StreamReader (fileStream, System. text. ASCIIEncoding. default) {// It is time consuming to create a connection. Therefore, do not create a connection using (SqlConnection conn = new SqlConnection (@ "Data Source =. \ SQLEXPRESS; AttachDBFilename = | DataDirectory | \ Database1.mdf; Integrated Security = True; User Instance = True ")) {Conn. Open (); using (SqlCommand cmd = conn. CreateCommand () {string line = null; while (line = streamReader. ReadLine ())! = Null) {string [] strs = line. split ('|'); string name = strs [0]; int age = Convert. toInt32 (strs [1]); cmd. commandText = "insert into T_Persons (Name, Age) values (@ Name, @ Age)"; cmd. parameters. clear (); // The parameter cannot be added repeatedly. A SqlCommand is always used in the while, that is, a Cmd object (container) is assigned twice, resulting in too many parameters, the jar cmd should be cleared after each assignment. parameters. add (new SqlParameter ("Name", name); cmd. parameters. add (new SqlParameter ("Age", age); cmd. executeNonQuery ();}}}} MessageBox. Show ("imported successfully! ");/* If (odfImport. showDialog () = DialogResult. OK) {// if there is no prompt for the first knock, right-click and resolve the included using .......... using (FileStream fileStream = File. openRead (odfImport. fileName) {using (StreamReader streamReader = new StreamReader (fileStream) {string line = null; while (line = streamReader. readLine ())! = Null) {string [] strs = line. split ('|'); string name = strs [0]; int age = Convert. toInt32 (strs [1]); using (SqlConnection conn = new SqlConnection (@ "Data Source =. \ SQLEXPRESS; AttachDBFilename = | DataDirectory | \ Database1.mdf; Integrated Security = True; User Instance = True ") // implements the IDisposable interface, which is enclosed by using for automatic release, the IDisposabl method is called after using (). It first checks whether there is a conn. close (); if not, Close it before releasing {conn. open (); using (SqlCommand Cmd = conn. createCommand () // create an instance of the command object and establish a connection with the first database. The connection using is in, so that this method does not need to be released directly. The brackets will automatically release {cmd. commandText = "insert into T_Persons (Name, Age) values (@ Name, @ Age)"; cmd. parameters. add (new SqlParameter ("Name", name); cmd. parameters. add (new SqlParameter ("Age", age); cmd. executeNonQuery () ;}}}} MessageBox. show ("Import successful! "); */} Private void ofdExport_Click (object sender, EventArgs e) {if (odfImport. ShowDialog ()! = DialogResult. OK) {return;} using (FileStream filestream = File. openWrite (odfImport. fileName) // file stream writing. The key step is to open the existing file and write {using (StreamWriter streamWrite = new StreamWriter (filestream, System. text. encoding. default) // file stream write {using (SqlConnection conn = new SqlConnection (@ "Data Source =. \ SQLEXPRESS; AttachDBFilename = | DataDirectory | \ Database1.mdf; Integrated Security = True; User Instance = True ") {c Onn. open (); using (SqlCommand cmd = conn. createCommand () {cmd. commandText = "select * from T_Persons"; using (SqlDataReader reader = cmd. executeReader () {while (reader. read () {string Name = reader. getString (reader. getOrdinal ("Name"); string Age = reader. getString (reader. getOrdinal ("Age"); streamWrite. writeLine ("{0} | {1}", Name, Age); // formatted and written, similar to the Console. writeLine () ;}}}}} MessageBox. sho W ("exported successfully ~ ");}}}