VS 2015连接SQL server数据库方法

时间:2022-12-21 04:34:42

vs新建一个Windows窗口应用程序,界面布局如下:

VS 2015连接SQL server数据库方法

Form1.cs中代码如下:
 using System;
 using System.Collections.Generic;
 using System.ComponentModel;
 using System.Data;
 using System.Drawing;
 using System.Linq;
 using System.Text;
 using System.Threading.Tasks;
 using System.Windows.Forms;

 using System.Data.SqlClient;

 namespace smSQLServerTest
 {
     public partial class MainForm : Form
     {
         public MainForm()
         {
             InitializeComponent();
         }

         //定义一个数据库连接对象
         SqlConnection myconnection;

         //连接按钮
         private void btConnect_Click(object sender, EventArgs e)
         {
             try
             {
                 myconnection = new SqlConnection("Integrated Security=SSPI;Initial Catalog=yinming_ceshi;Data Source=PC-20170607UIQI;User ID=PC-20170607UIQI\\Administrator;");
                 myconnection.Open();     //打开数据库
                 label1.Text = "数据库连接成功!";
             }
             catch (Exception ee)
             {
                 MessageBox.Show("数据库连接失败!" + ee.ToString());
             }
         }

         //查询按钮
         private void btQueryAll_Click(object sender, EventArgs e)
         {
             try
             {
                 string SQL = "select * From Table_3_ceshi";
                 SqlDataAdapter objDataAdpter = new SqlDataAdapter();
                 objDataAdpter.SelectCommand = new SqlCommand(SQL, myconnection);
                 DataSet ds = new DataSet();
                 objDataAdpter.Fill(ds, "Table_3_ceshi");
                 dataGridView1.DataSource = ds.Tables[];
             }

             catch (Exception ee)
             {
                 MessageBox.Show("查询失败!" + ee.ToString());
             }
         }

         //插入按钮
         private void btInsertToDatabase_Click(object sender, EventArgs e)
         {
             try
             {
                 //插入数据
                 string strSQL1 = "insert into Table_3_ceshi(name_ceshi,age_ceshi) values('剑姬',28)";
                 SqlDataAdapter objDataAdpter = new SqlDataAdapter();
                 SqlCommand thisCommand = new SqlCommand(strSQL1, myconnection);
                 thisCommand.ExecuteNonQuery();

                 //以下代码为显示数据表
                 string strSQL2 = "select * From Table_3_ceshi";
                 SqlDataAdapter objDataAdpter1 = new SqlDataAdapter();
                 objDataAdpter1.SelectCommand = new SqlCommand(strSQL2, myconnection);
                 DataSet ds = new DataSet();
                 objDataAdpter1.Fill(ds, "Table_3_ceshi");
                 dataGridView1.DataSource = ds.Tables[];
             }

             catch (Exception ee)
             {
                 MessageBox.Show("插入数据失败!" + ee.ToString());
             }
         }

         //修改按钮
         private void btModifiFeildValue_Click(object sender, EventArgs e)
         {
             try
             {
                 //修改数据表内容
                 string strSQL1 = "update Table_3_ceshi set age_ceshi='29' where name_ceshi='剑姬'";
                 SqlCommand thisCommand = new SqlCommand(strSQL1, myconnection);
                 thisCommand.ExecuteNonQuery();

                 //显示数据表
                 string strSQL2 = "select * From Table_3_ceshi";
                 SqlDataAdapter objDataAdpter1 = new SqlDataAdapter();
                 objDataAdpter1.SelectCommand = new SqlCommand(strSQL2, myconnection);
                 DataSet ds = new DataSet();
                 objDataAdpter1.Fill(ds, "Table_3_ceshi");
                 dataGridView1.DataSource = ds.Tables[];
             }

             catch (Exception ee)
             {
                 MessageBox.Show("更新数据失败!" + ee.ToString());
             }
         }

         //删除按钮
         private void btDeleteFeildValue_Click(object sender, EventArgs e)
         {
             try
             {
                 //删除数据表某条记录
                 string strSQL1 = "delete from Table_3_ceshi where name_ceshi='剑姬'";
                 SqlCommand thisCommand = new SqlCommand(strSQL1, myconnection);
                 thisCommand.ExecuteNonQuery();

                 //显示数据表
                 string strSQL2 = "select * From Table_3_ceshi";
                 SqlDataAdapter objDataAdpter1 = new SqlDataAdapter();
                 objDataAdpter1.SelectCommand = new SqlCommand(strSQL2, myconnection);
                 DataSet ds = new DataSet();
                 objDataAdpter1.Fill(ds, "Table_3_ceshi");
                 dataGridView1.DataSource = ds.Tables[];
             }

             catch (Exception ee)
             {
                 MessageBox.Show("删除数据失败!" + ee.ToString());
             }
         }

         //关闭连接函数
         public string DisConnect()
         {
             string Result;
             try
             {
                 myconnection.Close();
                 Result = "数据连接已断开!";
             }
             catch (Exception e)
             {
                 MessageBox.Show("数据库断开失败!" + e.ToString());
                 Result = "连接成功!";
             }
             return Result;
         }

         private void btDisConnect_Click(object sender, EventArgs e)
         {
             label1.Text = DisConnect();
         }
     }
 }
注意点:33行中,Integrated Security 是身份验证方式(一定要写),Data Source 是数据源(服务器名称);Initial Catalog是数据库的名称;User ID是登录数据的用户名;我的数据库密码由于没有,所以不需写