Datatable转为Sql语句，这里是转为Mysql，如果需要转为其他Sql，小小的修改即可

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114

public static string DataTableToSQL(DataTable dataTable, DataTableToSQLType tpye, string SQLWhere = null)        {            if (dataTable.TableName == "" || dataTable.TableName == null)            {                return "";            }              StringBuilder sb = new StringBuilder();            int i = 0;            int j = 0;            if (tpye == DataTableToSQLType.INSERT)            {                for (i = 0; i < dataTable.Rows.Count; i++)                {                    sb.AppendFormat("INSERT INTO {0}(", dataTable.TableName);                      //拼前半段INSERT                    for (j = 0; j < dataTable.Columns.Count; j++)                    {                        if (j == dataTable.Columns.Count - 1)                        {                            sb.AppendFormat("{0})", dataTable.Columns[j].Caption);                        }                        else                        {                            sb.AppendFormat("{0},", dataTable.Columns[j].Caption);                        }                    }                    //拼后半段VALUES                    sb.Append(" VALUES(");                    for (j = 0; j < dataTable.Columns.Count; j++)                    {                        var value = GetValue(dataTable.Columns[j].DataType, dataTable.Rows[i][j]);                        if (j == dataTable.Columns.Count - 1)                        {                            if (string.IsNullOrEmpty(value + ""))                                sb.AppendFormat("{0});", "NULL");                            else                                sb.AppendFormat("{0});", value);                        }                        else                        {                            if (string.IsNullOrEmpty(value + ""))                                sb.AppendFormat("{0},", "NULL");                            else                                sb.AppendFormat("'{0}',", value);                        }                    }                    sb.AppendLine();                }            }            else if (tpye == DataTableToSQLType.UPDATE)            {                for (i = 0; i < dataTable.Rows.Count; i++)                {                    //判断行是否被修改,修改才生成sql                    //if (dataTable.Rows[i].RowState == DataRowState.Modified)                    //{                    sb.AppendFormat("UPDATE {0} SET ", dataTable.TableName);                    for (j = 0; j < dataTable.Columns.Count; j++)                    {                        var value = GetValue(dataTable.Columns[j].DataType, dataTable.Rows[i][j]);                        if (dataTable.Columns[j].ColumnName == "update_time" && dataTable.Rows[i][j] != null)                        {                            value = Convert.ToDateTime(dataTable.Rows[i][j]).ToString("yyyy-MM-dd HH:mm:ss");                        }                        if (j == dataTable.Columns.Count - 1)                        {                            if (string.IsNullOrEmpty(value + ""))                                sb.AppendFormat(" {0}={1}", dataTable.Columns[j].Caption, "NULL");                            else                                sb.AppendFormat(" {0}='{1}'", dataTable.Columns[j].Caption, value + "");                        }                        else                        {                            if (string.IsNullOrEmpty(value + ""))                                sb.AppendFormat(" {0}={1},", dataTable.Columns[j].Caption, "NULL");                            else                                sb.AppendFormat(" {0}='{1}',", dataTable.Columns[j].Caption, value + "");                        }                    }                    if (!string.IsNullOrEmpty(SQLWhere))                        sb.AppendFormat(" WHERE ", SQLWhere);                    else                        sb.AppendFormat(" WHERE id='{0}';", dataTable.Rows[i]["id"]);                    sb.AppendLine();                }                //}            }            else if (tpye == DataTableToSQLType.DELETE)            {                //判断用户是否录入自定义条件                if (!string.IsNullOrEmpty(SQLWhere))                    sb.AppendFormat("DELETE {0} WHERE {1} ", dataTable.TableName, SQLWhere);                else                {                    sb.AppendFormat("DELETE {0} WHERE {1} IN (", dataTable.TableName, dataTable.Columns[0].Caption);                    for (i = 0; i < dataTable.Rows.Count; i++)                    {                          if (i < dataTable.Rows.Count - 1)                        {                            sb.AppendFormat("'{0}',", dataTable.Rows[i][0]);                        }                        else                        {                            sb.AppendFormat("'{0}');", dataTable.Rows[i][0]);                        }                    }                }              }            return sb.ToString();        }

　　判断时间类型

?

1 2 3 4 5 6 7 8 9 10 11

private static object GetValue(Type type, object value)        {            if (type == typeof(DateTime))            {                if (value != null&&value!=System.DBNull.Value)                {                    return Convert.ToDateTime(value).ToString("yyyy-MM-dd HH:mm:ss");                }            }            return value;        }

　　Sql操作模式

?

1 2 3 4 5 6

public enum DataTableToSQLType        {            INSERT,            UPDATE,            DELETE        }