增删改查SQL拼接方法

1，查询select拼接

static public string MakeSelectSQL(string table, IList<string> select,
            IList<string> where, IList<object> values)
        {
            StringBuilder sql = new StringBuilder();

            if (select == null || select.Count == 0)
            {
                sql.AppendFormat("SELECT * FROM {0}", table);
            }
            else
            {
                sql.Append("SELECT ");

                for (int i = 0; i < select.Count; i++)
                {
                    sql.Append(select[i]);
                    if(i == select.Count - 1)
                        break;

                    sql.Append(", ");
                }

                sql.AppendFormat(" FROM {0}", table);
            }

            if (where != null)
            {
                if (where.Count == 1 && values.Count == 0)
                {
                    sql.Append(" WHERE ");
                    sql.Append(where[0]);
                }
                else
                {
                    for (int i = 0; i < where.Count; i++)
                    {
                        if (i == 0)
                            sql.Append(" WHERE ");

                        sql.Append(where[i] + (values.ToString().StartsWith("%") && values.ToString().EndsWith("%") ? " Like " : " = "));
                        sql.Append(GetSQLValueString(values[i]));

                        if (i == where.Count - 1)
                            break;

                        sql.Append(" AND ");
                    }
                }
            }

            return sql.ToString();
        }

2，插入Insert拼接

static public string MakeInsertSQL(
            string table, IList<string> fields, IList<object> values)
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendFormat("INSERT INTO {0}(", table);

            for (int i = 0; i < fields.Count; i++)
            {
                sql.Append(fields[i]);
                if (i == fields.Count - 1)
                    break;

                sql.Append(", ");
            }

            sql.Append(") VALUES (");

            for (int i = 0; i < values.Count; i++)
            {
                sql.Append(GetSQLValueString(values[i]));

                if (i == values.Count - 1)
                    break;

                sql.Append(", ");
            }

            sql.Append(")");
            sql.Append(";SELECT SCOPE_IDENTITY()");

            return sql.ToString();
        }

3，更新Update拼接

static public string MakeUpdateSQL(
            string TableName, IList<string> npkFields, IList<object> npkValues, IList<string> pkFields, 
            IList<object> pkValues)
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendFormat("UPDATE {0} SET ", TableName);

            for (int i = 0; i < npkFields.Count; i++)
            {
                sql.Append(npkFields[i] + " = ");
                sql.Append(GetSQLValueString(npkValues[i]));

                if (i == npkFields.Count - 1)
                    break;

                sql.Append(", ");
            }

            for (int i = 0; i < pkFields.Count; i++)
            {
                if(i == 0)
                    sql.Append(" WHERE ");

                sql.Append(pkFields[i] + " = ");
                sql.Append(GetSQLValueString(pkValues[i]));

                if (i == pkFields.Count - 1)
                    break;

                sql.Append(" AND ");
            }

            return sql.ToString();
        }

4，删除Delete拼接

static public string MakeDeleteSQL(string TableName, IList<string> fields, IList<object> values)
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendFormat("DELETE FROM {0}", TableName);

            for (int i = 0; i < fields.Count; i++)
            {
                if(i == 0)
                    sql.Append(" WHERE ");

                sql.Append(fields[i] + " = ");
                sql.Append(GetSQLValueString(values[i]));

                if (i == fields.Count - 1)
                    break;

                sql.Append(" AND ");
            }

            return sql.ToString();
        }