C#处理SQL的两种小方法

方法一:

public DataTable GetDptLst()
{
    using (DataAccesser da = DataAccesser.GetInstance())
    {
        Database database = da.InnerDatabase;
       

        StringBuilder strSql = new StringBuilder();
        strSql.Append("SELECT   DPT_CD /r/n");
        strSql.Append("        ,DPT_N  /r/n");
        strSql.Append("        ,DPT_DIFF     /r/n");
        strSql.Append("FROM " + importDbName + ".dbo.MyTable  /r/n");
        strSql.Append("WHERE    Some Condition");

        DbCommand command = database.GetSqlStringCommand(strSql.ToString());
        DataTable tbl = new DataTable();
        tbl.Columns.Add("DPT_CD", Type.GetType("System.String"));
        tbl.Columns.Add("DPT_N", Type.GetType("System.String"));
        tbl.Columns.Add("DPT_DIFF", Type.GetType("System.String"));

        da.FillDataTable(command, tbl);

        return tbl;
    }
}

 

方法二:

public MyDataBase.P0101_VIEW_01DataTable FillGridView(string year, string period)
{
    using (DataAccesser da = DataAccesser.GetInstance())
    {
        MyDataBase.P0101_VIEW_01DataTable tbl = new MyDataBase.P0101_VIEW_01DataTable();

        try
        {
            Database database = da.InnerDatabase;
            da.BeginTransaction();

            StringBuilder strSql = new StringBuilder();

            strSql.Append("SELECT *  /r/n");
            strSql.Append("FROM   P0101_VIEW_01 /r/n");
            strSql.Append("WHERE  YEAR = ");
            strSql.Append("       @YEAR AND /r/n");
            strSql.Append("       SEASON = ");
            strSql.Append("       @SEASON /r/n");

            DbCommand command = database.GetSqlStringCommand(strSql.ToString());

            SqlParameter[] prams = { new SqlParameter("@YEAR", SqlDbType.VarChar),
                                     new SqlParameter("@SEASON", SqlDbType.VarChar) };
            prams[0].Value = year;
            prams[1].Value = period;
            foreach (SqlParameter parameter in prams)
            {
                command.Parameters.Add(parameter);
            }

            da.FillDataTable(command, tbl);
        }
        catch (Exception ex)
        {
            throw ex;
        }

        return tbl;
    }
}

 

二者的主要区别是对新定义的变量tbl的处理不同,方法一是定义为DataTable类型,方法二是定义为MyDataBase.P0101_VIEW_01DataTable类型.

通过方法二查找,要求查找P0101_VIEW_01DataTable的各个字段,而方法二则是查找什么字段,就再往tbl中添加什么字段(tbl.Columns.Add).

个人认为方法一比较好.