VS2010连接SQL Server 2008并执行查询操作

来源：互联网发布：unity3d 保存texture 编辑：程序博客网时间：2024/06/05 10:15

先在SQL Server 2008中建一个Student数据库，含有一个表student，4个字段，分别为姓名(varchar)学号(varchar)性别(varchar)年龄(int)，并指定一个用户登录该数据库，用户名为cam，密码为123456，注意要修改cam用户的权限

新建控制台应用程序，连接数据库，输出student表中的所有字段，并执行插入删除操作

using System;using System.Data;using System.Data.SqlClient;using System.Collections.Generic;using System.Linq;using System.Text;namespace 连接数据库{    class Program    {        public static int Insert(string name, string pwd,string sex,int age)        {            SqlConnection conn = new SqlConnection("Data Source=(local);Initial Catalog=Student;Integrated Security=True");//Initial Catalog后面跟你数据库的名字            conn.Open();            string sql = "insert into student(姓名,学号,性别,年龄) values(@name,@pwd,@sex,@age)";            SqlCommand cmd = new SqlCommand(sql, conn);            SqlParameter parn1 = new SqlParameter("@name", name);            cmd.Parameters.Add(parn1);            SqlParameter parn2 = new SqlParameter("@pwd", pwd);            cmd.Parameters.Add(parn2);            SqlParameter parn3 = new SqlParameter("@sex", sex);            cmd.Parameters.Add(parn3);            SqlParameter parn4 = new SqlParameter("@age", age);            cmd.Parameters.Add(parn4);            int result = cmd.ExecuteNonQuery();//result接收受影响行数，也就是说result大于0的话表示添加成功            conn.Close();            cmd.Dispose();            return result;        }        public static int Update(string name)        {            SqlConnection conn = new SqlConnection("Data Source=(local);Initial Catalog=Student;Integrated Security=True");//Initial Catalog后面跟你数据库的名字            conn.Open();            string sql = "delete from student where 姓名=@name";            SqlCommand cmd = new SqlCommand(sql, conn);            SqlParameter parn = new SqlParameter("@name",name);            cmd.Parameters.Add(parn);            int result = cmd.ExecuteNonQuery();//result接收受影响行数，也就是说result大于0的话表示删除成功            conn.Close();            cmd.Dispose();            return result;        }        static void Main(string[] args)        {            //指定Sql Server提供者的连接字符串            string connString = "server=CAMBRIDGE-PC;database =Student;uid=cam;pwd=123456";            //建立连接对象            SqlConnection Sqlconn = new SqlConnection(connString);            //打开连接            Sqlconn.Open();            //为上面的连接指定Command对象            SqlCommand thiscommand = Sqlconn.CreateCommand();            thiscommand.CommandText = "select 姓名,学号,性别,年龄 from student";            //为指定的command对象执行DataReader            SqlDataReader thisSqlDataReader = thiscommand.ExecuteReader();            while (thisSqlDataReader.Read())            {                Console.WriteLine("{0} {1} {2} {3}", thisSqlDataReader["姓名"], thisSqlDataReader["学号"], thisSqlDataReader["性别"], thisSqlDataReader["年龄"]);            }            //关闭读取            thisSqlDataReader.Close();            int result = Insert("关羽", "E01014307", "男", 25);            Console.WriteLine("影响的行数为:{0}", result);            result = Update("关羽");            Console.WriteLine("影响的行数为:{0}", result);            //关闭连接            Sqlconn.Close();            Console.ReadLine();        }    }}

建Windows窗体应用程序也可以,在Form窗体中拖一个DataGridView控件，插入一个学生的信息，在DataGridView控件中显示所有学生的信息

using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Data.SqlClient;using System.Drawing;using System.Linq;using System.Text;using System.Windows.Forms;namespace cam{    public partial class Form1 : Form    {        public Form1()        {            InitializeComponent();        }        public static int Insert(string name, string pwd, string sex, int age)        {            SqlConnection conn = new SqlConnection("Data Source=(local);Initial Catalog=Student;Integrated Security=True");//Initial Catalog后面跟你数据库的名字            conn.Open();            string sql = "insert into student(姓名,学号,性别,年龄) values(@name,@pwd,@sex,@age)";            SqlCommand cmd = new SqlCommand(sql, conn);            SqlParameter parn1 = new SqlParameter("@name", name);            cmd.Parameters.Add(parn1);            SqlParameter parn2 = new SqlParameter("@pwd", pwd);            cmd.Parameters.Add(parn2);            SqlParameter parn3 = new SqlParameter("@sex", sex);            cmd.Parameters.Add(parn3);            SqlParameter parn4 = new SqlParameter("@age", age);            cmd.Parameters.Add(parn4);            int result = cmd.ExecuteNonQuery();//result接收受影响行数，也就是说result大于0的话表示添加成功            conn.Close();            cmd.Dispose();            return result;        }        public static int Update(string name)        {            SqlConnection conn = new SqlConnection("Data Source=(local);Initial Catalog=Student;Integrated Security=True");//Initial Catalog后面跟你数据库的名字            conn.Open();            string sql = "delete from student where 姓名=@name";            SqlCommand cmd = new SqlCommand(sql, conn);            SqlParameter parn = new SqlParameter("@name", name);            cmd.Parameters.Add(parn);            int result = cmd.ExecuteNonQuery();//result接收受影响行数，也就是说result大于0的话表示删除成功            conn.Close();            cmd.Dispose();            return result;        }        public DataTable sel()        {            SqlConnection conn = new SqlConnection("Data Source=(local);Initial Catalog=Student;Integrated Security=True");//Initial Catalog后面跟你数据库的名字,如果你的SqlServer服务器名称后面不带SQLEXPRESS，那么Data Source=.            conn.Open();            string sql = "select * from student";            SqlCommand cmd = new SqlCommand(sql, conn);            SqlDataAdapter sda = new SqlDataAdapter(cmd);            DataTable dt = new DataTable();            sda.Fill(dt);            conn.Close();            cmd.Dispose();            return dt;        }         private void Form1_Load(object sender, EventArgs e)        {            Insert("关羽", "E01014307", "男", 25);            dataGridView1.DataSource = sel();        }    }}