VisualStudio2013 built-in SQL Server Getting Started (ii)--Adding and deleting changes

Previous article http://www.cnblogs.com/qixi233/p/4766451.html

This is the operation of SQL Server

Interface comparison walking heart hahaha, will look, the main is the following additions and deletions to check four BTN

There are several basic operations for SQL Server:

• Switch SqlConnection
• SqlCommand "SQL command" and "SQLConnection" connection
• Executes the command and feeds the result of the action to the user based on the return value

Now start the operation: (the code is relatively simple, if there is a lack of standard writing, please inform thank you)

Code structure:

　　

Database view:

　　

0. Switch database connection

takes precedence over the existence of four operations, which is turned on before each user operation, and closes after each user action

        /// <summary>        ///Open Database/// </summary>        Private voidOpencon () {stringStr_con ="server= (LocalDB) \\v11.0;database=master;integrated security=true"; Con=NewSqlConnection (Str_con); if(Con. state = =connectionstate.closed) {con.                Open (); return; }            if(Con. state==Connectionstate.broken) {con.                Close (); Con.            Open (); }        }        /// <summary>        ///Close the database/// </summary>        Private voidClosecon () {if(Con. State! =connectionstate.closed) {con.                Close (); Con.            Dispose (); }        }

2. Check
2. Query the database for corresponding data based on ID, or return, no prompt

3.         #regionBtn_ QueryPrivate voidBtn_check_click (Objectsender, RoutedEventArgs e)            {Opencon (); //whether the query has an ID            if(tb_id. Text.trim ()! ="")            {                stringSql_cmd ="Select Username,cardtype from Tb_card where cardid= '"+ tb_id. Text.trim () +"'"; //you can try open to return the SqlConnection object directly, and then query temporarily not encapsulate                using(cmd =NewSqlCommand (Sql_cmd, con)) {                    using(SqlDataReader reader =cmd. ExecuteReader ()) {if(reader. Read ()) {tb_name. Text= Reader. GetString (0); Tb_type. Text= Reader. GetString (1); }                        Else{MessageBox.Show ("Query failed"); }                    }                }            }            Else{MessageBox.Show ("The ID input box must not be empty");        } Closecon (); }         #endregion

3. Increase
   2. Check if ID exists, add if not present

   3.         #regionBtn_ addPrivate voidBtn_add_click (Objectsender, RoutedEventArgs e)            {Opencon (); if(tb_id. Text.trim ()! =""&& Tb_name. Text.trim ()! =""&& Tb_type. Text.trim ()! ="")            {                Try                {                    //whether the query has an ID                    stringCmd_check ="Select Username,cardtype from Tb_card where cardid= '"+ tb_id. Text.trim () +"'"; using(cmd =NewSqlCommand (Cmd_check, con)) {                        using(SqlDataReader reader =cmd. ExecuteReader ()) {if(reader. Read ()) {MessageBox.Show ("This card information is already in the database and cannot be added"); return;//There's a repetition, quit, no processing .                            }                        }                    }                    //Insert                    stringCmd_add = String.Format ("INSERT INTO Tb_card (Cardid,username,cardtype) VALUES ({0},{1},{2})",                        "\ '"+ tb_id. Text.trim () +"\ '","n\ '"+ Tb_name. Text.trim () +"\ '","\ '"+ Tb_type. Text.trim () +"\ '"); using(cmd =NewSqlCommand (Cmd_add, con)) {                        if(CMD. ExecuteNonQuery ()! =-1) {MessageBox.Show ("Add Success"); }                    }                }                Catch(Exception ex) {MessageBox.Show ("add failure, details:"+Ex.                ToString ()); }            }            Else{MessageBox.Show ("the information must not be empty");        } Closecon (); }         #endregion

4. Change
   2. Detects if an ID exists, modifies it if it exists, notifies the user if it does not exist

   3.         #regionBtn_ UpdatePrivate voidBtn_update_click (Objectsender, RoutedEventArgs e)            {Opencon (); if(tb_id. Text.trim ()! =""&& Tb_name. Text.trim ()! =""&& Tb_type. Text.trim ()! ="")            {                Try                {                    //whether the query ID exists                    stringCmd_check ="Select Username,cardtype from Tb_card where cardid= '"+ tb_id. Text.trim () +"'"; using(cmd =NewSqlCommand (Cmd_check, con)) {                        using(SqlDataReader reader =cmd. ExecuteReader ()) {if(!Reader. Read ()) {MessageBox.Show ("The card information is not available in the database and cannot be modified"); return; }                        }                    }                    stringCmd_update = String.Format ("update tb_card set Username={0},cardtype={1} where cardid={2}",                            "n\ '"+ Tb_name. Text.trim () +"\ '","\ '"+ Tb_type. Text.trim () +"\ '","\ '"+ tb_id. Text.trim () +"\ '"); using(cmd =NewSqlCommand (Cmd_update, con)) {                        if(CMD. ExecuteNonQuery ()!=-1) {MessageBox.Show ("Update Successful"); }                    }                }                Catch(Exception ex) {MessageBox.Show ("update failed with more information:"+Ex.                ToString ()); }            }            Else{MessageBox.Show ("the information must not be empty");        } Closecon (); }         #endregion

5. By deleting
   2. Detects if the ID exists, deletes the row if it exists, and informs the user that it does not exist

   3.         #regionBtn_ DeletePrivate voidBtn_delete_click (Objectsender, RoutedEventArgs e)            {Opencon (); if(tb_id. Text.trim (). Length! =0)            {                Try                {                    stringCmd_check ="SELECT * from Tb_card where cardid= '"+ tb_id. Text.trim () +"'"; using(cmd =NewSqlCommand (Cmd_check, con)) {                        using(SqlDataReader reader =cmd. ExecuteReader ()) {if(!Reader. Read ()) {MessageBox.Show ("The card information is not available in the database and cannot be deleted"); return; }                        }                    }                    stringCmd_delete ="Delete from Tb_card where cardid= '"+ tb_id. Text.trim () +"'"; using(cmd =NewSqlCommand (Cmd_delete, con)) {                        if(CMD. ExecuteNonQuery ()! =-1) {MessageBox.Show ("Delete succeeded"); }                    }                }                Catch(Exception ex) {MessageBox.Show ("Delete failure, details:"+Ex.                ToString ()); }            }            Else{MessageBox.Show ("ID must not be empty");        } Closecon (); }         #endregion

Everyone's favorite direct on the code is over, say I met a few pits:

The first is the beginning do not know why can only add a number, even the English alphabet can not be added, and then through the View Code of the SQL command found in the English language where there is no "surround it;"

The second is every time the Chinese added after the garbled, and then know that there is nvarchar this thing, and then add a place in Chinese before adding a N, for example: Seven XI-"N ' seven Xi ', so it's done.

The third is the execution of the cmd command, I start regardless of additions and deletions are used ExecuteReader, but feel strange, the last to see the stackoverflow of the great God's answer and official documents only know the general additions and deletions are used ExecuteNonQuery

Finally, a few links in the learning process are referenced:

http://www.cnblogs.com/mr-wid/archive/2013/05/09/3068229.html 21 minutes MySQL Getting Started tutorial

Https://msdn.microsoft.com/en-us/library/ms187752.aspx Microsoft's database type reference

Http://www.w3school.com.cn/sql/sql_datatypes.asp various database type reference

Http://stackoverflow.com/questions/5349114/executenonquery StackOverflow about ExecuteNonQuery use

God horse place is wrong or God horse opinion Welcome to offer thank you.

VisualStudio2013 built-in SQL Server Getting Started (ii)--Adding and deleting changes