VS.NET(C#)提升-2.32_数据库接口:C#创建SqlDataAdapter对象常用方法

web.config 定义连接字符串

<connectionStrings>
    <add name="VocationConnectionString" connectionString="Data Source=127.0.0.1;Initial Catalog=GSHRLeaveDB;User ID=ebiz_web;Password=ebiz@#1234" providerName="System.Data.SqlClient"/>
 </connectionStrings>

类初始化取得连接字符串

 public static void Initialize()
        {
            // Initialize data source. Use connection string from configuration.
            string v_ConnectionStringsType = "VocationConnectionString";

            if (ConfigurationManager.ConnectionStrings[v_ConnectionStringsType] == null ||
                ConfigurationManager.ConnectionStrings[v_ConnectionStringsType].ConnectionString.Trim() == "")
            {
                throw new Exception("A connection string named 'ConnectionStringType' with a valid connection string " +
                                    "must exist in the <connectionStrings> configuration section for the application.");
            }

            v_strConn =
              ConfigurationManager.ConnectionStrings[v_ConnectionStringsType].ConnectionString;
        }

方法一：通过连接字符串和查询语句

通过部门ID，查询部门主管信息

 public static DataSet QueryManagerInfo(string v_deptid, string v_managerid, string v_enableflag)
        {
            string sql = "Select * from account_Dept_Manager_Depart ";
            string v_where = " where ";
            if (v_deptid != "ALL")
            {
                sql = sql + v_where + " DeptID='" + v_deptid + "'";
                v_where = " AND ";
            }
            if (v_managerid != "ALL")
            {
                sql = sql + v_where + " Managerid='" + v_managerid + "'";
                v_where = " AND ";
            }
            if (v_enableflag != "ALL")
            {
                sql = sql + v_where + " enableflag='" + v_enableflag + "'";
            }
            sql += " order by deptid";
			
			SqlDataAdapter da=new SqlDataAdapter(sql,v_strConn);
			
			DataSet ds=new DataSet();//创建DataSet实例
			
			da.Fill(ds,"Dept_Manager");//使用DataAdapter的Fill方法(填充)
			
            return ds;
        }

注意：在方法一中，创建每个SqlDataAdapter时，都同时创建一个新的SqlConnection对象。

方法二：查询语句和SqlConnection对象

 public static DataSet QueryManagerInfo(string v_deptid, string v_managerid, string v_enableflag)
        {
            string sql = "Select * from account_Dept_Manager_Depart ";
            string v_where = " where ";
            if (v_deptid != "ALL")
            {
                sql = sql + v_where + " DeptID='" + v_deptid + "'";
                v_where = " AND ";
            }
            if (v_managerid != "ALL")
            {
                sql = sql + v_where + " Managerid='" + v_managerid + "'";
                v_where = " AND ";
            }
            if (v_enableflag != "ALL")
            {
                sql = sql + v_where + " enableflag='" + v_enableflag + "'";
            }
            sql += " order by deptid";
			
			SqlConnection conn=new SqlConnection(v_strConn); 
			
			SqlDataAdapter da=new SqlDataAdapter(Sql,conn);
			
			DataSet ds=new DataSet();//创建DataSet实例
			
			da.Fill(ds,"Dept_Manager");//使用DataAdapter的Fill方法(填充)
			
            return ds;
        }

方法三：SqlCommand对象来创建

public static DataSet QueryManagerInfo(string v_deptid, string v_managerid, string v_enableflag)
        {
            string sql = "Select * from account_Dept_Manager_Depart ";
            string v_where = " where ";
            if (v_deptid != "ALL")
            {
                sql = sql + v_where + " DeptID='" + v_deptid + "'";
                v_where = " AND ";
            }
            if (v_managerid != "ALL")
            {
                sql = sql + v_where + " Managerid='" + v_managerid + "'";
                v_where = " AND ";
            }
            if (v_enableflag != "ALL")
            {
                sql = sql + v_where + " enableflag='" + v_enableflag + "'";
            }
            sql += " order by deptid";
			
			SqlConnection conn=new SqlConnection(v_strConn); 
			
			conn.Open();
			
			SqlCommand cmd=new SqlCommand(sql,conn);
			
			SqlDataAdapter da=new SqlDataAdapter(cmd);
			
			DataSet ds=new DataSet();//创建DataSet实例
			
			da.Fill(ds,"Dept_Manager");//使用DataAdapter的Fill方法(填充)
			
            return ds;
        }

注意事项：

1)一般只需要执行SQL语句或SP，就没必要用到DataAdapter ，直接用SqlCommand的Execute系列方法。比如将对DataSet的修改更新到数据库，一般就用sqlDataadapter对象。

2）在使用Fill方式时，可以指定DataTable

SqlDataAdapter da=new SqlDataAdapter(cmd) 
DataTable tbl=new DataTable();

da.Fill(tbl);

3)多次调用Fill方法需要注意SqlConnection对象l连接与关闭处理

建议手动open close,如下：
SqlDataAdapter da1,da2;
da1 = new SqlDataAdapter(cmd1);
da2 = new SqlDataAdapter(cmd2);
cn.Open();
da1.Fill(ds,"Customers");
da2.Fill(ds,"Orders");
cn.Close();