C# Microsoft SQL Server 操作

1.连接到数据库

1             public static SqlConnection conn;
2             //本地数据库 .\SQLEXPRESS
3             conn = new SqlConnection("Initial Catalog=student;Data Source=XS-YGD\SQLEXPRESS;User ID='cam';Password='xs123';Connect Timeout=30");
4             //conn = new SqlConnection("Data Source = .\SQLEXPRESS;Initial Catalog=Student;Integrated Security=True");//Initial Catalog后面跟你数据库的名字
5             conn.Open();

2，将数据库导入到dataGridView中

1 // 打开数据库连接
2             if (conn.State == ConnectionState.Open)
3                 conn.Close();
4             ConnectionString = "Integrated Security=SSPI;" +
5              "Initial Catalog = " + DBName + ";" +
6              "Data Source=.\SQLEXPRESS;";
7             conn.ConnectionString = ConnectionString;
8             conn.Open();

1  // 创建数据适配器
2             SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM " + TableName , conn);
3             // 创建一个数据集对象并填充数据，然后将数据显示在DataGrid控件中
4             DataSet ds = new DataSet(TableName);
5             da.Fill(ds, TableName);
6             dataGridView1.DataSource = ds.Tables[TableName].DefaultView;

3，执行数据库命令

 1 // <summary>
 2         /// 执行数据库命令
 3         /// </summary>
 4         /// <param name="sql"></param>
 5         private bool ExecuteSQLStmt(string DBName, string sql)
 6         {
 7             // 打开数据库连接
 8             if (conn.State == ConnectionState.Open)
 9                 conn.Close();
10             ConnectionString = "Integrated Security = SSPI;" + "Initial Catalog = " + DBName + ";" + "Data Source =.\SQLEXPRESS;";
11             conn.ConnectionString = ConnectionString;
12             conn.Open();
13             SqlCommand cmd = new SqlCommand(sql, conn);
14             try
15             {
16                 cmd.ExecuteNonQuery();
17             }
18             catch (SqlException ae)
19             {
20                 MessageBox.Show(ae.Message.ToString());
21                 return false;
22             }
23             return true;
24         }

4，添加列

1  string sql = "alter table " + TableName + " ADD " + colName + " " + Type + " not null default ('" + defaultdate + "') with values";
2             ExecuteSQLStmt(DBName, sql);

5，添加主键

1 string sql = "alter table " + TableName + " add CONSTRAINT " + PrimaryKeyName + " primary key(" + ColName + ")";
2             return ExecuteSQLStmt(DBName, sql);

6，绑定默认值

1 //绑定默认值
2         public bool BindDefault(string DBName, string DefaultName, string TableName, string colName)
3         {
4             string sql = "EXEC sp_bindefault '" + DefaultName + "', '" + TableName + "." + colName + "'";
5             return ExecuteSQLStmt(DBName, sql);
6         }

7，关闭数据库

1  if (conn.State == ConnectionState.Open)
2                 conn.Close();