C # form connection to SQL database

Create a database

      CREATE DATABASE data1220--Creating databases Go                            --go represents the following statement as a use data1220 on the basis of the preceding statement execution-                  -to determine the operational database object Gocreate table Users            --add a table to the database (code int identity (primary) key,   --Define a self-growing column, and set the primary key     name varchar () NOT NULL,         -- Name column, not empty pwd varchar (() NOT null           --Password column, not empty) insert into users values (' Zhangsan ', 111)  --to the Users table in the database, Add data insert into users values (' Lisi ', 222) insert to users values (' Wangwu ', 333) insert into users values (' Zhaoliu ', 444) Select *from users    --querying data delete from users

Accessing database data through the C#winform program

Namespaces and variable types used:

Using System.Data.SqlClient;

SqlConnection; Database Connection class

SqlCommand; Database Operations class

SqlDataReader: Read

Steps:

1. Build a login form

Trigger Event on 2.login button: Connect database function

//Press the Sign-in button to check the data entered private void Button1_Click (object sender, EventArgs e) {//after spell check            The message that the query is to use for the form string user = TextBox1.Text;            string pwd = TextBox2.Text; Create the object for the database connection class SqlConnection con = new SqlConnection ("server=.;            Database=data1220;user=sa;pwd=123 "); Turn the connection on con.            Open (); The function that executes the Con object returns an object of type SqlCommand SqlCommand cmd = con.            CreateCommand (); The input data is stitched into an SQL statement and handed to the Cmd object cmd.            CommandText = "Select*from users where Name= '" +user+ "' and pwd= '" +pwd+ "'"; Executing the statement with the CMD function, returning the SqlDataReader object DR,DR is the returned result set (that is, the table data queried in the database) SqlDataReader dr = cmd.            ExecuteReader (); With Dr's read function, each time it is executed, it returns a collection of the next row of data Dr, before executing the Read function, the DR is not a set if (Dr. Read ()) {//dr[] can be filled with a column name or index, showing the obtained data MessageBox.Show (Dr[1].            ToString ()); }//Close the connection after use to avoid affecting other programs accessing con.        Close (); }

Adding data to a database

Add data        private void button2_click (object sender, EventArgs e)        {            string user = TextBox1.Text;            string pwd = TextBox2.Text;            Create the object for the database connection class            SqlConnection con = new SqlConnection ("server=.; Database=data1220;user=sa;pwd=123 ");            Con. Open ();            The function that executes the Con object returns an object of type SqlCommand            SqlCommand cmd = con. CreateCommand ();            The spelling statement            cmd.commandtext = "INSERT into users values ('" +user+ "', '" +pwd+ "')";            Additions and deletions instead of ExecuteNonQuery, an integer number            int count = cmd is returned. ExecuteNonQuery ();            if (Count > 0)            {                MessageBox.Show ("add Success");            }            else            {                MessageBox.Show ("Add failed");            }        }

modifying data

Modify data by number        private void Button4_Click (object sender, EventArgs e)        {            string user = TextBox1.Text;            string pwd = TextBox2.Text;            string Code=textbox3.text;            Create the object for the database connection class            SqlConnection con = new SqlConnection ("server=.; Database=data1220;user=sa;pwd=123 ");            Con. Open ();            The function that executes the Con object returns an object of type SqlCommand            SqlCommand cmd = con. CreateCommand ();            Spelling statement            cmd.commandtext = "Update users set Name= '" +user+ "', pwd= '" +pwd+ "' where code= ' +code;            Additions and deletions instead of ExecuteNonQuery, an integer number            int count = cmd is returned. ExecuteNonQuery ();            if (Count > 0)            {                MessageBox.Show ("modified successfully");            }            else            {                MessageBox.Show ("modification Failed");            }            Con. Close ();        }

Delete data

private void Button3_Click (object sender, EventArgs e)        {            string code = TextBox3.Text;            Create the object for the database connection class            SqlConnection con = new SqlConnection ("server=.; Database=data1220;user=sa;pwd=123 ");            Con. Open ();            The function that executes the Con object returns an object of type SqlCommand            SqlCommand cmd = con. CreateCommand ();            Spelling statement            cmd.commandtext = "Delete from users where code=" + code;            Additions and deletions instead of ExecuteNonQuery, an integer number            int count = cmd is returned. ExecuteNonQuery ();            if (Count > 0)            {                MessageBox.Show ("delete succeeded");            }            else            {                MessageBox.Show ("delete failed");            }            Con. Close ();        }

C # form connection to SQL database