数据库操作

1.数据库操作

查找：

static void Main(string[] args)
        {
            string sqlsercon = "Data Source=IDEA-PC;Initial Catalog=BookDB;User ID=sa;Password=abcdef";
            SqlConnection conn = new SqlConnection(sqlsercon);
            conn.Open();
            SqlCommand cmd = conn.CreateCommand();//数据库的实例化，将数据库的东西传送
            cmd.CommandText = "select * from T_User";

            SqlDataAdapter adapter = new SqlDataAdapter(cmd);

            DataTable dt = new DataTable();
            adapter.Fill(dt);
            conn.Close();//断开连接
            conn.Dispose();//清除连接
           cmd.Dispose();

           // Console.WriteLine(dt.Rows.Count);
              string realname = "";
            string username = "";
            string mobile = "";
            string address = "";

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                realname = dt.Rows[i][3].ToString();
                username = dt.Rows[i][1].ToString();
                mobile = dt.Rows[i][4].ToString();
                address = dt.Rows[i][5].ToString();
                Console.WriteLine("姓名为：{0}，用户名为：{1}，手机为：{2}，地址为：{3}",realname,username,mobile,address);
            }
             Console.ReadKey();
        }

删除：

 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 = "delete from T_User where Id=8";
            cmd.ExecuteNonQuery();

            cmd.Dispose();
            con.Close();
            con.Dispose();
            Console.WriteLine("删除成功");

            Console.ReadKey();
        }

修改：

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();
        }

增加：

 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();
            //定义了要向数据库发送的sql语句
            cmd.CommandText = "insert into T_User(UserName,Password,RealName,Mobile,Address) values(@username,@password,@realname,@mobile,@address)";
            //将sql语句中的占位符替换成实际的值
            #region 另一种方式，天才的想法
            /*cmd.Parameters.AddWithValue("UserName", "xingxin");
            cmd.Parameters.AddWithValue("Password", "777777");
            cmd.Parameters.AddWithValue("RealName", "邢鑫");
            cmd.Parameters.AddWithValue("Mobile", "13888888888");
            cmd.Parameters.AddWithValue("Address", "河北秦皇岛北戴河边");*/
            #endregion
            #region MyRegion
            cmd.Parameters.AddWithValue("@username", "xingxin");
            cmd.Parameters.AddWithValue("@password", "777777");
            cmd.Parameters.AddWithValue("@realname", "邢鑫");
            cmd.Parameters.AddWithValue("@mobile", "13888888888");
            cmd.Parameters.AddWithValue("@address", "河北秦皇岛北戴河边");
            #endregion
            //执行代码，向数据库发送cmd.CommandText指定的sql语句
            cmd.ExecuteNonQuery();
            cmd.Dispose();
            con.Close();
            con.Dispose();
            Console.WriteLine("成功插入一行");
            Console.ReadKey();
        }