一个ASP.net新手的一些疑问。

Question

在做一个简单的电子商务后台。
需要做很多的新建、编辑、删除的页面。比如产品的、类别的、人员权限的。
就是在不停的拖from，然后写sql。
有没有什么简洁点的方法？
最好请高手给个例子我研究一下。谢谢。

Answer 1

直接写好一个类库,运用sql的时候,直接传进去参数,就免得来回写麻烦!

Answer 2

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;namespace Gree.Common
{
    public class SqlExec
    {
        #region ExecCommand 直接运行sql命令        public static int ExecCommand(string CommandText, string CommandConnString)
        {
            int returnValue = 0;            SqlConnection myconn = new SqlConnection(CommandConnString);
            SqlCommand mycmd = new SqlCommand(CommandText, myconn);
            try
            {
                myconn.Open();
                returnValue = mycmd.ExecuteNonQuery();
                myconn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return returnValue;
        }        #endregion        #region ReturnDataTable 填充数据表        public static void FillDataTable(string CommandText, DataTable returnTable, string CommandConnString)
        {            SqlConnection myconn = new SqlConnection(CommandConnString);
            SqlDataAdapter mydp = new SqlDataAdapter(CommandText, myconn);
            try
            {
                myconn.Open();
                mydp.Fill(returnTable);
                myconn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }        #endregion        #region returnDataTable 返回数据表        public static DataTable returnDataTable(string CommandText, string CommandConnString)
        {
            DataTable mytb = new DataTable();
            SqlConnection myconn = new SqlConnection(CommandConnString);
            SqlDataAdapter mydp = new SqlDataAdapter(CommandText, myconn);
            try
            {
                myconn.Open();
                mydp.Fill(mytb);
                myconn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return mytb;
        }        #endregion        #region returnDataSet 返回数据集        public static DataSet returnDataSet(string CommandText, string CommandConnString)
        {
            DataSet mytb = new DataSet();
            SqlConnection myconn = new SqlConnection(CommandConnString);
            SqlDataAdapter mydp = new SqlDataAdapter(CommandText, myconn);
            try
            {
                myconn.Open();
                mydp.Fill(mytb);
                myconn.Close();
            }
            catch (Exception ex)
            {
                //ex.ToString();
                throw ex;
            }
            return mytb;
        }        #endregion        #region ReturnDataSet 填充数据集        public static void FillDataTable(string CommandText, DataSet ReturnDataSet, string CommandConnString)
        {
            SqlConnection myconn = new SqlConnection(CommandConnString);
            SqlDataAdapter mydp = new SqlDataAdapter(CommandText, myconn);
            try
            {
                myconn.Open();
                mydp.Fill(ReturnDataSet);
                myconn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }        #endregion        #region 运行存储过程返回数据表        public static DataTable ExecProcRetutnDt(string ProcName, string ConnString, params SqlParameter[] commandParameters)
        {
            DataTable returnValue = new DataTable();
            SqlConnection myconn = new SqlConnection(ConnString);
            SqlCommand myCmd = new SqlCommand(ProcName, myconn);
            myCmd.CommandType = CommandType.StoredProcedure;
            if ((commandParameters.Length > 0) && (commandParameters != null))
            {
                for (int i = 0; i < commandParameters.Length; i++)
                {
                    myCmd.Parameters.Add(commandParameters[i]);
                }
            }
            SqlDataAdapter mydp = new SqlDataAdapter(myCmd);
            try
            {
                myconn.Open();
                mydp.Fill(returnValue);
                myconn.Close();
            }
            catch (SqlException ex)
            {
                ex.ToString();
            }
            return returnValue;
        }        #endregion        #region 运行存储过程返回影响行数        public static int ExecProcRetrunInt(string ProcName, string ConnString, params SqlParameter[] commandParameters)
        {
            int returnValue = 0;
            SqlConnection myconn = new SqlConnection(ConnString);
            SqlCommand myCmd = new SqlCommand(ProcName, myconn);
            myCmd.CommandType = CommandType.StoredProcedure;
            if ((commandParameters.Length > 0) && (commandParameters != null))
            {
                for (int i = 0; i < commandParameters.Length; i++)
                {
                    myCmd.Parameters.Add(commandParameters[i]);
                }
            }
            try
            {
                myconn.Open();
                returnValue = myCmd.ExecuteNonQuery();
                myconn.Close();
            }
            catch (SqlException ex)
            {
                ex.ToString();
            }
            return returnValue;
        }        #endregion        #region 运行存储过程,返回输出参数
        public static string ExecProcReturnStr(string ProcName, string ConnString, SqlParameter outPutParameter, params SqlParameter[] commandParameters)
        {
            string returnValue = "";
            SqlConnection myconn = new SqlConnection(ConnString);
            SqlCommand myCmd = new SqlCommand(ProcName, myconn);            myCmd.CommandType = CommandType.StoredProcedure;
            myCmd.Parameters.Add(outPutParameter);            if ((commandParameters.Length > 0) && (commandParameters != null))
            {
                for (int i = 0; i < commandParameters.Length; i++)
                {
                    myCmd.Parameters.Add(commandParameters[i]);
                }
            }
            try
            {
                myconn.Open();
                myCmd.ExecuteNonQuery();
                returnValue = myCmd.Parameters[outPutParameter.ParameterName].Value.ToString();
                myconn.Close();
            }
            catch (SqlException ex)
            {
                ex.ToString();
            }
            return returnValue;
        }
        #endregion    }
}

Answer 3

这个是不是网上说的sqlhelp？
能发给我一份完整的吗？
[email protected]，谢谢

Answer 4

在需求中,除了界面,其它都是通用的,再考虑到扩展性,建议如下做:1.采用嵌套页来定义整体页面布局和样式
2.所有基于数据库数的界面统一采用用户控件,这样一来,你只需反复制作用户控件即可3.用户控件的加载:因为重复的较多,你可以采用一个配置文件,来记录要加载哪些用户控件,最好是基于XML的,扩展性较强4.对用户控件的逻辑处理形成单独的一个项目
5.建立一个统一的基接口,定义数据插入\删除\更新\查询方法,形成单独的项目
6.建立一个统一的抽象类,实现基接口,如果有其它需求,可以继承基接口和抽象类(注意,可分开继承)
7.对数据库的操作:从前台传入DATASET,其结构与数据一致,这样,可利用DATASET中数据行的行状态,采用循环方式生成SQL语句(主要针对文本,数值和日期)

Answer 5

/// <summary>
        /// 根据表名、主键列和数据集，产生多个INSERT语句
        /// </summary>
        /// <param name="dsSave"></param>
        /// <param name="tsTableName"></param>
        /// <param name="tsKayName"></param>
        /// <param name="tsSequence"></param>
        /// <returns></returns>
        protected string GenerateOracleInsertSql(DataSet dsSave, string tsTableName, string tsKeyName, string tsSequence,bool ReturnValueFlag)
        {
            try
            {
                StringBuilder SqlBuilder = new StringBuilder();
                SqlBuilder.Append("BEGIN ");
                foreach (DataRow row in dsSave.Tables[tsTableName].Rows)
                {
                    if (row.RowState == DataRowState.Added)
                    {
                        string lsKeyPara = ":NEWID";
                        StringBuilder loSqlBuilder = new StringBuilder();
                        StringBuilder loColumns = new StringBuilder();
                        StringBuilder loValues = new StringBuilder();
                        foreach (DataColumn column in dsSave.Tables[tsTableName].Columns)
                        {
                            if (column.ColumnName.ToLower() != tsKeyName.ToLower())
                            {
                                loColumns.Append(",");
                                loColumns.Append(column.ColumnName);
                                if (row[column] != null && row[column] != DBNull.Value)//有数据
                                {
                                    if (column.DataType == typeof(System.DateTime))
                                    {
                                        loValues.Append(",");
                                        loValues.Append("TIMESTAMP'" + row[column].ToString() + "'");
                                    }
                                    else
                                    {
                                        loValues.Append(",");
                                        loValues.Append("'" + row[column].ToString() + "'");
                                    }
                                }
                                else//无数据
                                {
                                    loValues.Append(",null");
                                }
                            }
                        }
                        loSqlBuilder.Append("INSERT INTO " + tsTableName + " (");
                        loSqlBuilder.Append(tsKeyName);
                        loSqlBuilder.Append(loColumns.ToString());
                        loSqlBuilder.Append(") VALUES(" + tsSequence + ".NEXTVAL");
                        loSqlBuilder.Append(loValues.ToString());
                        if (ReturnValueFlag)
                        {
                            loSqlBuilder.Append(") RETURNING " + tsKeyName + " INTO " + lsKeyPara);
                        }
                        else
                        {
                            loSqlBuilder.Append(")");
                        }
                        loSqlBuilder.Append(";");                        SqlBuilder.Append(loSqlBuilder.ToString());
                    }                }
                if (SqlBuilder.ToString() == "BEGIN ")//无插入数据
                {
                    return "";
                }
                else
                {
                    SqlBuilder.Append(" END;");
                    return SqlBuilder.ToString();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

Answer 6

/// <summary>
        /// 生成ORACLE UPDATE语句批
        /// </summary>
        /// <param name="dsSave">要修改的语句批</param>
        /// <param name="tsTableName">表名</param>
        /// <param name="tsKeyName">主键列名</param>
        /// <param name="tbValidate">验证修改时间标志</param>
        /// <returns></returns>
        protected string GenerateOracleUpdateSql(DataSet dsSave, string tsTableName, string tsKeyName, bool tbValidate)
        {
            try
            {
                StringBuilder loSqlBuilder = new StringBuilder();
                loSqlBuilder.Append("BEGIN ");
                foreach (DataRow row in dsSave.Tables[tsTableName].Rows)
                {
                    if (row.RowState == DataRowState.Modified)
                    {
                        if (tbValidate)//验证修改时间是否一致
                        {
                            string lsSql = "SELECT MODIFYTIME FROM " + tsTableName + " WHERE " + tsKeyName + "=" + row[tsKeyName].ToString();
                            Database loDb = new Database();
                            DbCommand loCommand = loDb.GetDbCommand();
                            loCommand.CommandText = lsSql;
                            DataSet loResDs = loDb.ExecuteDataSet(loCommand);
                            DataRow loRow = loResDs.Tables[0].Rows[0];
                            if (loRow["MODIFYTIME"].ToString() != row["MODIFYTIMEOLD"].ToString())
                            {
                                return "ValidateWrong";
                            }
                        }
                        loSqlBuilder.Append(String.Format("UPDATE {0} SET ", tsTableName));
                        foreach (DataColumn column in dsSave.Tables[tsTableName].Columns)
                        {
                            if (column.ColumnName.ToLower() != tsKeyName.ToLower())
                            {
                                if (column.ColumnName.ToLower() != "modifytimeold")
                                {
                                    if (row[column] != null && row[column] != DBNull.Value)//有数据
                                    {
                                        if (column.DataType == typeof(System.DateTime))
                                        {
                                            loSqlBuilder.Append(column.ColumnName + "=TIMESTAMP'" + row[column.ColumnName].ToString() + "',");
                                        }
                                        else
                                        {
                                            loSqlBuilder.Append(column.ColumnName + "='" + row[column.ColumnName].ToString() + "',");
                                        }
                                    }
                                    else //NULL数据
                                    {
                                        loSqlBuilder.Append(column.ColumnName + "=null,");
                                    }
                                }
                            }                        }
                        //去掉最后一个逗号
                        loSqlBuilder.Remove(loSqlBuilder.Length - 1, 1);
                        //加上WHERE条件
                        loSqlBuilder.Append(string.Format(" where {0}={1};", tsKeyName, row[tsKeyName].ToString()));
                    }
                }
                if (loSqlBuilder.ToString() == "BEGIN ")//无更新数据
                {
                    return "";
                }
                else
                {
                    loSqlBuilder.Append(" END;");
                    return loSqlBuilder.ToString();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }