C # Operations SQL Server

C # Operating SQL Server is not much different from manipulating other data, but there are some details to note.

Do not select the default instance when installing SQL Server, if it is necessary to change the settings, there are remote connections to go to the connection service settings, such as port 1433, and so on, these Baidu a bit can. Here are some of the most basic database operations to begin with.

(i) Establishment of a database

        Static voidSqldbcreate (stringdbname) {            //connect to SQL Server by connecting to the DB master that itself exists            stringConnStr ="Server = localhost; User Id=sa; password=******; Database = Master"; SqlConnection Conn=NewSqlConnection (CONNSTR); Conn.            Open (); //If the current database is in a state that cannot be deleted, set master to the current state and delete the rebuild if the database exists//The problem here generally is that the data is installed as the default instance, changed to a specific instance, and then started the service center, changed the IP in IP3 to native, and changed the TCP port to 1433//other changes to Yes, while restart in the service can            stringSql="Use master;"+"IF db_id (N ' {0} ') is not NULL"+"DROP DATABASE {0};"+"CREATE DATABASE {0}"; SQL=string.            Format (SQL, dbname); Try{SqlCommand cmd=NewSqlCommand (SQL, conn); Cmd.                ExecuteNonQuery (); Console.WriteLine ("createdb_success"); }            Catch(Exception ex) {Console.WriteLine ("createdb_failed:"+Ex.            Message); }            finally{Conn.            Close (); }        }

(ii) Establishment of tables in established databases

        Static voidTablecreate (stringdbnamestringtablename) {            stringConnStr ="Server = localhost; User Id=sa; password=********; Database = {0}"; ConnStr=string.            Format (ConnStr, dbname); stringSql="IF object_id (N ' {1}: {0} ', N ' U ') is not NULL"+"DROP TABLE {0};"+"CREATE TABLE {0}"+"(ID [int] not NULL Primary Key,"+"Name [Char] (max) NULL,"+"Price [Char] (max) NULL,"+"Dprice [Char] (+) NULL)"; SQL=string.            Format (SQL, TableName, dbname); SqlConnection Conn=NewSqlConnection (CONNSTR); Conn.            Open (); Try{SqlCommand cmd=NewSqlCommand (SQL, conn); Cmd.                ExecuteNonQuery (); Console.WriteLine ("createtable_success"); }            Catch(Exception ex) {Console.WriteLine ("createtable_failed:"+Ex.            Message); }            finally{Conn.            Close (); }        }

(iii) Want to add data to the table

        Static voidFilltable (stringdbnamestringtablename) {            stringConnStr ="Server = localhost; User Id=sa; password=********; Database = {0}"; ConnStr=string.            Format (ConnStr, dbname); SqlConnection Conn=NewSqlConnection (CONNSTR); Conn.            Open (); stringSql="INSERT into {0} (Id,name,price,dprice)"+"values (@d,@a,@b,@c)"; SQL=string.            Format (SQL, tablename); Try            {                 for(intI=0;i< -; i++) {SqlCommand cmd=NewSqlCommand (SQL, conn); Cmd. Parameters.addwithvalue ("@d", i); Cmd. Parameters.addwithvalue ("@a", Guid.NewGuid ()); Cmd. Parameters.addwithvalue ("@b", i.ToString ()); Cmd. Parameters.addwithvalue ("@c", i.ToString ()); Cmd.                                    ExecuteNonQuery (); } Console.WriteLine ("filltable_success"); }            Catch(Exception ex) {Console.WriteLine ("filltable_failed:"+Ex.            Message); }            finally{Conn.            Close (); }        }

(iv) Deletion of forms

        Static voidDeletetable (stringdbnamestringtablename) {            stringConnStr ="Server = localhost; User Id=sa; password=*********; Database = {0}"; ConnStr=string.            Format (ConnStr, dbname); SqlConnection Conn=NewSqlConnection (CONNSTR); Conn.            Open (); stringSql="IF object_id (N ' {0}: {1} ', N ' U ') is not NULL"+"DROP TABLE {1};"; SQL=string.            Format (SQL, dbname, tablename); Try{SqlCommand cmd=NewSqlCommand (SQL, conn); Cmd.                ExecuteNonQuery (); Console.WriteLine ("deletetable_success"); }            Catch(Exception ex) {Console.WriteLine ("deletetable_failed:"+Ex.            Message); }            finally{Conn.            Close (); }        }

(v) reading data from the database

        Static voidTranstods (stringdbnamestringtablename) {            stringConnStr ="Server =.; User Id=sa; password=*******; Database = {0}"; ConnStr=string.            Format (ConnStr, dbname); SqlConnection Conn=NewSqlConnection (CONNSTR); Conn.            Open (); stringsql ="select *from {0}"; SQL=string.            Format (SQL, tablename); SqlCommand cmd=NewSqlCommand (SQL, conn); SqlDataAdapter apt=NewSqlDataAdapter (CMD); SqlCommandBuilder SB=NewSqlCommandBuilder (APT); DataSet DS=NewDataSet (); Try{apt.                Fill (DS); Console.WriteLine ("transtods_success"); }            Catch(Exception ex) {Console.WriteLine ("transtods_failed:"+Ex.            Message); }            finally{Conn.            Close (); } DataTable DT= ds. tables[0]; //int i = 0; //foreach (DataRow Dr in DS. Tables[0]. Rows)//{            //if (i>50)//    {            //ds. Tables[0].            Rows.removeat (i); //    }            //i++; //}            intCount =dt.            Rows.Count;  for(intI=0; i<count;i++)            {                if(i> -)                {                    //dt. Rows.removeat (Wuyi);dt. Rows[i].                Delete (); }} apt.                       Update (DS); intj =0; foreach(DataRow DrinchDs. tables[0]. Rows) {Console.WriteLine (dr[" Price"]+" "+j+" "+Dr.                RowState); J++; }                 }

About data operation in this point, you can use SqlDataReader to read, insert modified data, etc. can also be processed using insert, such as the use of a dataset to cache the data table in the local table to operate, if only necessary. It is convenient to use datasets, including updating data tables, and so on, you just need to use SqlDataAdapter to update.

Attention:

1. Do not use foreach when traversing a DataTable and deleting operations, it does not allow additions and deletions

2, if the data table needs to update, then the data table is to add and delete, do not use import and remove, because when the update is performed according to the RowState property of the DataRow operation. For example, delete, not delete row, but the corresponding row of the RowState has been modified, the update is based on this property for processing, the local data table only when the use of Acceptchange in accordance with the RowState property of the corresponding deletion of the data table. In the case of an import or remove, there are problems with the RowState property, such as remove, the corresponding RowState does not exist when the whole row is removed, and when update, because the corresponding row does not have RowState property value, Therefore, the remote database is not deleted. Import RowState status is unknown, so the remote database can not be updated data.

In summary, instead of comparing the local DataTable with the SQL Server datasheet and updating it with update, SQL Server data table is modified directly based on the RowState property of the DataRow in the DataTable.

C # Operations SQL Server