C # Operating SQL Server databases

I just got my graduation certificate a few days ago. I reported to my company yesterday and started my career again. There are so many joys and sorrows. From now on, I am no longer a student.

Today, the manager asked me to write a small program to import the data in the text file into the database. Because the data volume is large, I can only write the program to read it.

I have never used SQL Server database operations before. Please try it now. To share with you.

using System.Data.SqlClient;

This namespace needs to be introduced.

How to connect to the database and create a table

String connstring = "Server = 192.168.1.85; database = Bridge Monitoring; uid = sa; Pwd = 123456"; sqlconnection = new sqlconnection (connstring); sqlconnection. open (); string SQL = "Create Table S4" + "(gpsindex bigint primary key, adatetime datetime, X float, y float, height float," + "dltax float, dltay float, dltah float) "; sqlcommand cmd = new sqlcommand (SQL, sqlconnection); cmd. executenonquery ();

The server corresponds to the IP address of the database. You can connect to the sqlserver database later.

Filestream afile = new filestream ("station_8008_ay.txt", filemode. Open); streamreader sr = new streamreader (afile); strline = Sr. Readline (); While (strline! = NULL) {string [] STR = strline. split (''); int I = 0; // double X1 = 0, Y1 = 0; // if the data is too long, double float X1 = 0, Y1 = 0; float height1 = 0; foreach (string strtest in Str) {If (strtest. trim ()! = "") // Trim () removes spaces from the string header and tail {if (I> 1) {if (I = 2) {x1 = float. parse (strtest);} if (I = 3) {y1 = float. parse (strtest);} if (I = 4) {height1 = float. parse (strtest) ;}} I ++ ;}}

 

The above is the code I wrote today. I learned a lot from this operation and hope to accumulate more.

String. the TRIM () function is very useful. It can remove the spaces at the header and tail of a string. In areas related to database operations, the string must be very fine and careless, so this function is useful.

 

                DateTime dt = System.DateTime.Now;                Int64 milliseconds = (Int64)(System.DateTime.Now - new DateTime(1970, 1, 1)).TotalMilliseconds + System.DateTime.Now.Millisecond;                string sql1 = "INSERT INTO s4(GPSIndex,aDatetime,X,Y,Height,dltaX,dltaY,dltaH)"                    + "VALUES(" + milliseconds.ToString()                    + "," + "'" + dt.ToString("yyyy-MM-dd hh:mm:ss")                    +"'"                     + ","                     + X1.ToString()                    +","                    +Y1.ToString()                    +","+Height1.ToString()                    +",0,0,0)";              //MessageBox.Show(sql1);                cmd = new SqlCommand(sql1, sqlConnection);                cmd.ExecuteNonQuery();                strLine = sr.ReadLine();                Thread.Sleep(1000);            }

 

When the insertion time is reached, an error is certainly returned in the case of -- DT. tostirng (). In this way, the database cannot convert the string to the datetime type.

I solved this problem with the help of a colleague who has been in the company for several years.

By the way, the character type must be enclosed in single quotation marks, which may cause errors.

Csdn is not powerful. It is troublesome to write a blog, and the Code cannot be displayed. I am so confused that I will go to the blog park later.