VS2010连接SQL Server 2008并执行查询操作
先在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 =