Select,Add,Update,Delete

//查询
public static DataSet select(string tableName,string whereStr)
{
    string sql = "select * from "+tableName+whereStr;
    try
    {
        return DbHelp.ExecSql(sql);
    }
    catch (Exception e1)
    {
        return null;
    }    
}

//删除单条
public static int delete (string tableName,string whereStr)
{
    string sql = "delete from "+tableName+whereStr;
    try
    {
        return DbHelp.ExecSql(sql);
    }
    catch (Exception e1)
    {
        return 0;
    }    
}

//删除多条
public static string deltes(DataTable dt)
{
    int i = 0;
    if (dt != null && dt.Rows.Count > 0)
    {
        foreach (DataRow row in dt.Rows) //遍历行
        {
            string tableName = dt.TableName;
            string pk = dt.Columns[0].ColumnName.ToString(); //获取第一列名（主键列）
            string pkValue = row[0].ToString(); //获取第一行的（主键值）
            string sql="delete from "+tableNmae+" where "+pk+" = '"+pkValue+"'";

            try
            {
                i = i + DbHelp.ExecSql(sql);
            }
            catch (Exception e1)
            {
                return e1.Massage.ToString()+sql;
            }
            
        }
        return "删除"+i.ToString()+"条信息";
    }
    else
    {
        return "删除0行";
    }

}

//遍历dataTable新增

        public static string add(DataTable dt)
        {
            int k = 0;
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow row in dt.Rows) //遍历行
                {
                    string tableName = dt.TableName;
                    string pk = dt.Columns[0].ColumnName.ToString(); //获取第一列名（主键列）
                    string pkValue = row[0].ToString(); //获取第一行的（主键值）
                    string sql = "insert into " + tableName + "(";
                    string sql1 = pk;
                    string sql2 = "'" + pkValue + "'";
                    for (int i = 1; i < dt.Columns.Count; i++) //从1开始，因为第一列为主键值
                    {
                        sql1 = sql1 + "," + dt.Columns[i].ColumnName.ToString();  //拼列明
                        sql2 = sql2 + ",'" + row[i].ToString() + "'";  //拼值
                        //sql1 = sql1 + dt.Columns[i].ColumnName + "= '" + row[i].ToString() + "',";//列名+列值
                    }
                    sql = sql + sql1 + ") values(" + sql2 + ")";
                    try
                    {
                        DB.DataHelper dtHelper = new DB.DataHelper();
                        k = k + dtHelper.ExecuteSql(sql);
                    }
                    catch (Exception e1)
                    {
                        return e1.Message.ToString() + sql;
                    }
                }
                return "新增" + k.ToString() + "条信息";
            }
            return "新增0条信息";
        }

 

//遍历dataTable修改

public static string upd(DataTable dt)
        {
            int k = 0;
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow row in dt.Rows) //遍历行
                {
                    string tableName = dt.TableName;
                    string pk = dt.Columns[0].ColumnName.ToString(); //获取第一列名（主键列）
                    string pkValue = row[0].ToString(); //获取第一行的（主键值）
                    string sql = "update " + tableName + " set ";
                    string sql1 = "";
                    for (int i = 1; i < dt.Columns.Count; i++) //从1开始，因为第一列为主键值
                    {
                        sql1 = sql1 + dt.Columns[i].ColumnName + "= '" + row[i].ToString() + "',";//列名+列值
                    }
　　　　　　　　　　　　sql = sql + sql1.Substring(0, sql1.Length - 1) + " where " + pk + " = '" + pkValue + "'";
                    try
                    {
                        DB.DataHelper dtHelper = new DB.DataHelper();
                        k = k + dtHelper.ExecuteSql(sql);
                    }
                    catch (Exception e1)
                    {
                        return e1.Message.ToString() + sql;
                    }
                }
                return "修改" + k.ToString() + "条信息";
            }
            return "修改0条信息";
        }