数据库的连接以及数据的增、删、查、改

一数据库的连接

*在c#中主要用到的类是SqlConnection类、Sqlcommand类

第一步：在c#编写的工具中，点击视图

第二步：点击服务资源管理器

第三步：右击鼠标点击数据库，添加连接，根据界面的要求，填写相关的需要的内容，点击确定

第四步：子啊服务资源管理器中右击鼠标点击数据库，点击属性，找到找到需要的字符串。

第五步：进行数据库连接的代码编写

             //将连接到的字符串赋予一个字符串变量进行存储，第一个分号之前的是计算机，第二个分号之前是连接的数据库，第三个分号之前的是集成安全。
            string sqlconn = "Data Source=(local);Initial Catalog=shijian;Integrated Security=True";
            SqlConnection conn = new SqlConnection(sqlconn);
            conn.Open();//数据库的打开
            conn.Close();//数据库的关闭
            conn.Dispose();//进行释放
            Console.Read();

二数据的插入

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace tianjia
{
    class Program
    {
        static void Main(string[] args)
        {
            string sqlconn = "Data Source=(local);Initial Catalog=shijian;Integrated Security=True";
            SqlConnection conn = new SqlConnection(sqlconn);
            conn.Open();
            SqlCommand comm = conn.CreateCommand();
            comm.CommandText = "insert into T_user(UserName,Password, RealName,Mobile,Address,Card) values(@UserName,@Password,@RealName,@Mobile,@Address,@Card)";
            comm.Parameters.AddWithValue("@UserName","wangyu");
            comm.Parameters.AddWithValue("@Password","66666666");
            comm.Parameters.AddWithValue("@RealName","王宇");
            comm.Parameters.AddWithValue("@Mobile","13055555555");
            comm.Parameters.AddWithValue("@Address", "河北");
            comm.Parameters.AddWithValue("@Card","13052319930527666x");
            comm.ExecuteNonQuery();//检测但不进行查询
            conn.Close();
            conn.Dispose();
            comm.Dispose();
            Console.WriteLine("插入成功");
            Console.Read();

        }
    }
}
三数据查询

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace lianjie
{
    class Program
    {
        static void Main(string[] args)
        {
            string sqlconn="Data Source=(local);Initial Catalog=shijian;Integrated Security=True";
            SqlConnection sql = new SqlConnection(sqlconn);
            sql.Open();
            SqlCommand conn = sql.CreateCommand();
            conn.CommandText = "select * from T_User;";
            SqlDataAdapter adapter = new SqlDataAdapter(conn);
            DataTable data = new DataTable();
            Console.WriteLine(data.Rows.Count);
            adapter.Fill(data);
            sql.Close();
            sql.Dispose();
            conn.Dispose();
            string UserName = "";
            string Password = "";
            string RealName = "";
            string Mobile = "";
            string Card = "";
            string Address = "";
            for (int i = 0; i < data.Rows.Count; i++)
            {
                UserName = data.Rows[i][1].ToString();
                Password = data.Rows[i][2].ToString();
                RealName = data.Rows[i][3].ToString();
                Mobile = data.Rows[i][4].ToString();
                Address = data.Rows[i][5].ToString();
                Card = data.Rows[i][5].ToString();

                Console.WriteLine("用户名是{0}，密码是{1}，真实的姓名是{2}，手机号码是{3}，地址是{4},身份证号是{5}", UserName, Password, RealName, Mobile,  Address, Card);
            }
            Console.Read();
        }
    }
}
四数据删除

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace shanchu
{
    class Program
    {
        static void Main(string[] args)
        {
            string sqlconn = "Data Source=(local);Initial Catalog=shijian;Integrated Security=True";
            SqlConnection conn = new SqlConnection(sqlconn);
            conn.Open();
            SqlCommand comm = conn.CreateCommand();
            comm.CommandText = "delete from T_user where UserName=@UserName";
            comm.Parameters.AddWithValue("@UserName", "wangyu");
            comm.ExecuteNonQuery();
            conn.Close();
            conn.Dispose();
            comm.Dispose();
            Console.WriteLine("删除成功");
            Console.Read();

        }
    }
}
五数据的更改

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace 修改
{
    class Program
    {
        static void Main(string[] args)
        {
            string sqlsercon = "Data Source=PC-20130723IUDE;Initial Catalog=MyTest;User ID=sa;Password=yhb@163";
            SqlConnection con = new SqlConnection(sqlsercon);
            con.Open();
            SqlCommand cmd = con.CreateCommand();
            cmd.CommandText = "update T_User set Card=@card where Id=6" ;
            cmd.Parameters.AddWithValue("@card","130988777788886666");
            cmd.ExecuteNonQuery();
            cmd.Dispose();
            con.Close();
            con.Dispose();
            Console.WriteLine("修改成功");
            Console.ReadKey();
        }
    }
}
六知识的拓展

*在数据的增，删，查，改时，进行实例的对比

方法一： SqlCommand cmd = new SqlCommand("delete from T_User where UserName=@UserName);

               comm.Parameters.AddWithValue("@UserName", "wangyu");

方法二： comm.CommandText = "delete from T_user where UserName=@UserName";
               comm.Parameters.AddWithValue("@UserName", "wangyu");