C # Operating SQL Server databases

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

[Csharp]
Using System. Data. SqlClient;

This namespace needs to be introduced.

How to connect to the database and create a table

[Csharp]
String connString = "server = 192.168.1.85; database = Bridge Monitoring; uid = sa; pwd = 123456 ";
SqlConnection 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.

[Csharp]
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, select double.
Float X1 = 0, Y1 = 0;
Float Height1 = 0;
Foreach (string strtest in str)
{

If (strtest. Trim ()! = "") // Trim () removes spaces at the header and tail of the string.
{
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.

 

[Csharp]
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 )";
// 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.

Author: guoyilongedu