C# Microsoft SQL Server 操作

1.连接到数据库

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

2，将数据库导入到dataGridView中

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

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

3，执行数据库命令

// <summary>
        /// 执行数据库命令
        /// </summary>
        /// <param name="sql"></param>
        private bool ExecuteSQLStmt(string DBName, string sql)
        {
            // 打开数据库连接
            if (conn.State == ConnectionState.Open)
                conn.Close();
            ConnectionString = "Integrated Security = SSPI;" + "Initial Catalog = " + DBName + ";" + "Data Source =.\SQLEXPRESS;";
            conn.ConnectionString = ConnectionString;
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql, conn);
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (SqlException ae)
            {
                MessageBox.Show(ae.Message.ToString());
                return false;
            }
            return true;
        }

4，添加列

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

5，添加主键

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

6，绑定默认值

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

 7，关闭数据库

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