創(chuàng)建數(shù)據(jù)庫連接并執(zhí)行sql語句？

以users表為例，有三個字段，自增長的編號id，int類型；名稱name，nvarchar類型，密碼pwd，nvarchar類型

首先在vs2005中引入using System.Data.sqlClient;命名空間

/// <summary>

/// 增加

/// </summary>

/// <param name="name">姓名</param>

/// <param name="pwd">密碼</param>

/// <returns></returns>

public int Insert(string name,string pwd)

{

SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=Test;Integrated Security=True");//Initial Catalog后面跟你數(shù)據(jù)庫的名字,如果你的SqlServer服務(wù)器名稱后面不帶SQLEXPRESS，那么Data Source=.

conn.Open();

string sql = "insert into users(name,pwd) values(@name,@pwd)";

SqlCommand cmd = new SqlCommand(sql,conn);

SqlParameter parn = new SqlParameter("@name",name);

cmd.Parameters.Add(parn);

SqlParameter parp = new SqlParameter("@pwd", pwd);

cmd.Parameters.Add(parn);

int result = cmd.ExecuteNonQuery();//result接收受影響行數(shù)，也就是說result大于0的話表示添加成功

conn.Close();

cmd.Dispose();

return result;

}

/// <summary>

/// 刪除

/// </summary>

/// <param name="name">姓名</param>

/// <param name="pwd">密碼</param>

/// <returns></returns>

public int Update(int id)

{

SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=Test;Integrated Security=True");//Initial Catalog后面跟你數(shù)據(jù)庫的名字,如果你的SqlServer服務(wù)器名稱后面不帶SQLEXPRESS，那么Data Source=.

conn.Open();

string sql = "delete from users where id=@id";

SqlCommand cmd = new SqlCommand(sql, conn);

SqlParameter parn = new SqlParameter("@id", id);

cmd.Parameters.Add(parn);

int result = cmd.ExecuteNonQuery();//result接收受影響行數(shù)，也就是說result大于0的話表示刪除成功

conn.Close();

cmd.Dispose();

return result;

}

/// <summary>

/// 修改

/// </summary>

/// <param name="name">姓名</param>

/// <param name="pwd">密碼</param>

/// <returns></returns>

public int Insert(string name, string pwd,int id)

{

SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=Test;Integrated Security=True");//Initial Catalog后面跟你數(shù)據(jù)庫的名字,如果你的SqlServer服務(wù)器名稱后面不帶SQLEXPRESS，那么Data Source=.

conn.Open();

string sql = "update users set name=@name,pwd=@pwd where id=@id";

SqlCommand cmd = new SqlCommand(sql, conn);

SqlParameter parn = new SqlParameter("@name", name);

cmd.Parameters.Add(parn);

SqlParameter parp = new SqlParameter("@pwd", pwd);

cmd.Parameters.Add(parn);

SqlParameter pari = new SqlParameter("@id", id);

cmd.Parameters.Add(pari);

int result = cmd.ExecuteNonQuery();//result接收受影響行數(shù)，也就是說result大于0的話表示修改成功

conn.Close();

cmd.Dispose();

return result;

}

/// <summary>

/// 查詢

/// </summary>

/// <returns></returns>

public DataTable Select()

{

SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=Test;Integrated Security=True");//Initial Catalog后面跟你數(shù)據(jù)庫的名字,如果你的SqlServer服務(wù)器名稱后面不帶SQLEXPRESS，那么Data Source=.

conn.Open();

string sql = "select * from users";

SqlCommand cmd = new SqlCommand(sql, conn);

SqlDataAdapter sda = new SqlDataAdapter(cmd);

DataTable dt = new DataTable();

sda.Fill(dt);

conn.Close();

cmd.Dispose();

return dt;

}

方法寫好后，下面舉一個查詢的例子，在form窗體中拖一個DataGridView,然后在Load方法中

private void Form1_Load(object sender, EventArgs e)

{

dataGridView1.DataSource = Select();

}

這樣一運行，DataGridView中就會顯示數(shù)據(jù)了