VS2010(C#)連接配接SQL Server 2008并執行查詢操作

VS2010連接配接SQL Server 2008并執行查詢操作

先在SQL Server 2008中建一個Student資料庫，含有一個表student，4個字段，分别為姓名(varchar)學号(varchar)性别(varchar)年齡(int)，并指定一個使用者登入該資料庫，使用者名為cam，密碼為123456，注意要修改cam使用者的權限

建立控制台應用程式，連接配接資料庫，輸出student表中的所有字段，并執行插入删除操作

[csharp] view plaincopyprint?

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 姓名[email protected]";
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();
}
}
}

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 姓名[email protected]";
            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();
        }
    }
}

VS2010(C#)連接配接SQL Server 2008并執行查詢操作

建Windows窗體應用程式也可以,在Form窗體中拖一個DataGridView控件，插入一個學生的資訊，在DataGridView控件中顯示所有學生的資訊

[csharp] view plaincopyprint?

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 姓名[email protected]";
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();
}
}
}

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 姓名[email protected]";
            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();
        }
    }
}

VS2010(C#)連接配接SQL Server 2008并執行查詢操作

VS2010(C#)連接配接SQL Server 2008并執行查詢操作

繼續閱讀

Unity3D中背景音樂和相關音效的添加，背景音樂暫停、停止和播放的設定

Unity遊戲開發背景知識

vs編譯完提示不支援嘗試的執行操作

【C# MySQL】第一次使用C#對MySQL進行操作的心得

C#基礎——與C#的第一次邂逅聲明：IDE：第一個C#果實：Console淺析：總結：

英文月日年日期格式的實作

C# 位元組數組 byte[] 快速比較算法

C# 位元組數組、字元串轉化位元組數組轉字元串字元串轉位元組數組位元組數組轉整數整數轉位元組數組字元串轉位元組字元串轉數值數值轉字元串

C#用Zlib壓縮或解壓縮位元組數組

C# 比較兩個位元組數組是否相等

對于VS中scanf報錯永久解決，建立源檔案自動添加#define _CRT_SECURE_NO_WARNINGS 1

《C#進階程式設計》讀書筆記 -索引

CQ V1.0分詞bates(基于雙數組tire樹)—應該是目前最快的中文分詞算法

C#多線程——前台線程和背景線程

C#+ArcEngine代碼檢測ArcEngine Runtime是否安裝以及是否授權

七牛雲-C#SDK-上傳-前期準備