sqlhelper.cs

commandBuilder应用数据库的使用

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace student.DB
{
    public class SqlHelper
    {
        private String connectionString = "server=.;database=stuDB;uid=sa;pwd=sa";
        private bool flag = false;

        /// <summary>
        /// 执行sql查询语句
        /// </summary>
        /// <param name="sql">sql查询语句</param>
        /// <returns>DataSet</returns>
        public DataSet getDataSet(String sql)
        {
            DataSet ds = new DataSet();
            using (SqlConnection con=new SqlConnection(connectionString))
            {
                using (SqlDataAdapter da = new SqlDataAdapter(sql, con))
                {
                    try
                    {
                        da.Fill(ds);
                    }
                    catch (SqlException e)
                    {
                        throw new Exception(e.Message);
                    }
                }
            }
            return ds;
        }

        /// <summary>
        /// 更新数据
        /// </summary>
        /// <param name="ds">DataSet</param>
        /// <param name="sql">sql语句</param>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        public bool doUpdate(DataSet ds,String sql,String tableName)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                using (SqlDataAdapter da = new SqlDataAdapter(sql, con))
                {
                    //数据库表一定要有主键列 否则此处通不过
                    SqlCommandBuilder builder = new SqlCommandBuilder(da);
                    try
                    {
                        lock (this)
                        {
                            da.Update(ds, tableName);
                            flag = true;
                       }
                    }
                    catch (SqlException e)
                    {
                        throw new Exception(e.Message);
                    }
                }
            }
            return flag;
        }
    }
}