DataTable生成SQL

最新推荐文章于 2026-06-25 15:55:19 发布

原创最新推荐文章于 2026-06-25 15:55:19 发布 · 622 阅读

19 ·

本内容遵循CC 4.0 BY-SA版权协议

GEO检测

标签

#sql #oracle #数据库

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace AddRowsTest
{
public class DataTableUpdateSQL
{
/// <summary>
/// 根据DataTable生成删除和更新SQL，在GridView里新增行的同时在数据库中插入新行
/// </summary>
/// <param name="dt"></param>
/// <param name="TableName"></param>
/// <param name="KeyID"></param>
/// <returns></returns>
public static System.Collections.ArrayList Get_ArrSQL(DataTable dt, string TableName, string KeyID)
{
System.Collections.ArrayList arr = new System.Collections.ArrayList();
#region 处理已经删除的行
if (dt.GetChanges(DataRowState.Deleted) != null)
{
for (int r = 0; r < dt.GetChanges(DataRowState.Deleted).Rows.Count; r++)
{
arr.Add("Delete " + TableName + " Where " + KeyID + "= " + dt.GetChanges(DataRowState.Deleted).Rows[r][KeyID, DataRowVersion.Original].ToString());
}
}
#endregion
for (int i = 0; i < dt.Rows.Count; i++)
{
if (dt.Rows[i].RowState == DataRowState.Deleted)
{
continue;
}
#region 只要行状态有编辑就更新
if (dt.Rows[i].RowState != DataRowState.Unchanged)
{
string UpdateSql = string.Empty;
for (int c = 0; c < dt.Columns.Count; c++)
{
if (dt.Columns[c].ColumnName != KeyID && dt.Columns[c].ReadOnly == false)
{
UpdateSql = UpdateSql + "[" + dt.Columns[c].ColumnName + "]=" + GetValuesText(dt.Columns[c].DataType.ToString(), dt.Rows[i][dt.Columns[c].ColumnName].ToString()) + ",";
}
}
if (UpdateSql.Length > 0)
{
UpdateSql = UpdateSql.Substring(0, UpdateSql.Length - 1);
}
arr.Add("Update " + TableName + " Set " + UpdateSql + " Where " + KeyID + "= " + dt.Rows[i][KeyID].ToString());
}
#endregion
//没有新增状态是因为在GridView新增行的同时在数据库里已经新增了ID，因此只有更新没有新增
}
return arr;
}
public static System.Collections.ArrayList Get_ArrSQL(DataTable dt, string TableName, string KeyID,int MaxID = 1)
{
System.Collections.ArrayList arr = new System.Collections.ArrayList();
#region 处理已经删除的行
if (dt.GetChanges(DataRowState.Deleted) != null)
{
for (int r = 0; r < dt.GetChanges(DataRowState.Deleted).Rows.Count; r++)
{
arr.Add("Delete " + TableName + " Where " + KeyID + "= " + dt.GetChanges(DataRowState.Deleted).Rows[r][KeyID, DataRowVersion.Original].ToString());
}
}
#endregion
for (int i = 0; i < dt.Rows.Count; i++)
{
if (dt.Rows[i].RowState == DataRowState.Deleted)
{
continue;
}
#region 只要行状态有编辑就更新
if (dt.Rows[i].RowState == DataRowState.Modified)
{
string UpdateSql = string.Empty;
for (int c = 0; c < dt.Columns.Count; c++)
{
if (dt.Columns[c].ColumnName != KeyID && dt.Columns[c].ReadOnly == false)
{
UpdateSql = UpdateSql + "[" + dt.Columns[c].ColumnName + "]=" + GetValuesText(dt.Columns[c].DataType.ToString(), dt.Rows[i][dt.Columns[c].ColumnName].ToString()) + ",";
}
}
if (UpdateSql.Length > 0)
{
UpdateSql = UpdateSql.Substring(0, UpdateSql.Length - 1);
}
arr.Add("Update " + TableName + " Set " + UpdateSql + " Where " + KeyID + "= " + dt.Rows[i][KeyID].ToString());
}
if (dt.Rows[i].RowState == DataRowState.Added)
{
string InserSql = string.Empty;
string ValuesSql = string.Empty;
for (int c = 0; c < dt.Columns.Count; c++)
{
if (dt.Columns[c].ReadOnly == false)
{
InserSql = InserSql + "[" + dt.Columns[c].ColumnName + "]" + ",";
}
}
for (int c = 0; c < dt.Columns.Count; c++)
{
if (dt.Columns[c].ReadOnly == false)
{
if (dt.Columns[c].ColumnName != KeyID)
ValuesSql = ValuesSql + GetValuesText(dt.Columns[c].DataType.ToString(), dt.Rows[i][dt.Columns[c].ColumnName].ToString()) + ",";
else
{ ValuesSql = ValuesSql + GetValuesText(dt.Columns[c].DataType.ToString(), MaxID.ToString()) + ","; }
}
}
if (InserSql.Length > 0)
{
InserSql = InserSql.Substring(0, InserSql.Length - 1);
}
if (ValuesSql.Length > 0)
{
ValuesSql = ValuesSql.Substring(0, ValuesSql.Length - 1);
}
InserSql = "Insert " + TableName + "(" + InserSql + ")";
ValuesSql = "Values (" + ValuesSql + ")";
arr.Add(InserSql + " " + ValuesSql);
MaxID++;
}
#endregion

}
return arr;
}
/// <summary>
/// 新增行的方法，可以增加到Get_ArrSQL方法中
/// </summary>
/// <param name="dt"></param>
/// <param name="TableName"></param>
/// <param name="KeyID">主键int型非自增</param>
/// <param name="MaxID"></param>
/// <returns></returns>
public static System.Collections.ArrayList GetInsertSql(DataTable dt, string TableName, string KeyID,int MaxID=1)
{
System.Collections.ArrayList arr = new System.Collections.ArrayList();
for (int i = 0; i < dt.Rows.Count; i++)
{
string InserSql = string.Empty;
string ValuesSql = string.Empty;
for (int c = 0; c < dt.Columns.Count; c++)
{
if (dt.Columns[c].ReadOnly == false)
{
InserSql = InserSql + "[" + dt.Columns[c].ColumnName + "]" + ",";
}
}
for (int c = 0; c < dt.Columns.Count; c++)
{
if (dt.Columns[c].ReadOnly == false)
{
if (dt.Columns[c].ColumnName != KeyID)
ValuesSql = ValuesSql + GetValuesText(dt.Columns[c].DataType.ToString(), dt.Rows[i][dt.Columns[c].ColumnName].ToString()) + ",";
else
{ ValuesSql = ValuesSql + GetValuesText(dt.Columns[c].DataType.ToString(), MaxID.ToString()) + ","; }
}
}
if (InserSql.Length > 0)
{
InserSql = InserSql.Substring(0, InserSql.Length - 1);
}
if (ValuesSql.Length > 0)
{
ValuesSql = ValuesSql.Substring(0, ValuesSql.Length - 1);
}
InserSql = "Insert " + TableName + "(" + InserSql + ")";
ValuesSql = "Values (" + ValuesSql + ")";
arr.Add(InserSql + " "+ValuesSql);
MaxID++;
}
return arr;
}

/// <summary>
/// 获取表的最大ID的SQL
/// </summary>
/// <param name="TableName">表名</param>
/// <param name="FieldName">关键字ID</param>
/// <returns></returns>
public static string GetMaxID(string TableName, string FieldName)
{
return "DECLARE @TableName varchar(50)='" + TableName + "',@FieldName varchar(50)='" + FieldName + @"'
DECLARE @ID int
Declare @Str varchar(Max)
Set @Str = 'IF exists(select id from tempdb..sysobjects where id=object_id(''' + 'tempdb..##T' + '''))
begin
drop table tempdb..##T
end
IF exists(select id from tempdb..sysobjects where id = object_id('''+'tempdb..##A'''+'))
begin
drop table tempdb..##A
end
IF exists(select id from tempdb..sysobjects where id = object_id('''+'tempdb..##B'''+'))
begin
drop table tempdb..##B
end
declare @i int
Set @i = isnull((select max('+@FieldName+') from '+@TableName+'), 1)
exec('''+'select top '+''' + @i + '+''' '+@FieldName+' = identity(int, 1, 1) into tempdb..##T from syscolumns a,syscolumns b'''+')
select t.'+@FieldName+' into tempdb..##A
from tempdb..##T t
where not exists(select 1 from '+@TableName+' where '+@FieldName+' = t.'+@FieldName+')
Declare @'+@FieldName+' int
Set @'+@FieldName+' = (Select MIN('+@FieldName+') From tempdb..##A)
if (ISNULL(@'+@FieldName+', 0) = 0)
begin
Set @'+@FieldName+' = (Select isnull((Select MAX('+@FieldName+') From '+@TableName+'),0))+1
end
Select @'+@FieldName+ ' ID into tempdb..##B'+'
drop table tempdb..##T drop table tempdb..##A'
Exec(@Str)
Set @ID = (
Select MAX(ID) From tempdb..##B)
drop table tempdb..##B
Select @ID";
}
private static string GetValuesText(string type, string Values)
{
string reval = string.Empty;

switch (type)
{

case "System.Int16":
reval = GetStrigToInt(Values).ToString();
break;
case "System.Int32":
reval = GetStrigToInt(Values).ToString();
break;
case "System.Int64":
reval = GetStrigToInt(Values).ToString();
break;
case "System.String":
if (!string.IsNullOrEmpty(Values))
{ reval = "'" + Values + "'"; }
else
{ reval = "null"; }
break;
case "System.Decimal":
reval = GetStringTodecimal(Values).ToString();
break;
case "System.Char":
reval = "'" + Values + "'";
break;
case "System.Double":
reval = GetStringToDouble(Values).ToString();
break;
case "System.DateTime":
string v_Values = GetStringToDateTime(Values);
if (v_Values != "null")
{ reval = "'" + GetStringToDateTime(Values) + "'"; }
else
{ reval = "null"; }

break;
case "System.Boolean":
reval = "'" + GetStrigToBool(Values) + "'";
break;
default:
if (!string.IsNullOrEmpty(Values))
{ reval = "'" + Values + "'"; }
else
{ reval = "null"; }
break;
}
return reval;

}
private static string GetStrigToInt(string Values)
{
try
{
int i = 0;
if (int.TryParse(Values, out i))
{ return i.ToString(); }
else
{ return "null"; }
}
catch { return "null"; }
}
private static bool GetStrigToBool(string values)
{
if (values == "0")
{ values = "False"; }
if (values == "1")
{ values = "True"; }
if (values == "是")
{ values = "True"; }
if (values == "否")
{ values = "False"; }
if (values.ToLower() == "yes")
{ values = "True"; }
if (values.ToLower() == "no")
{ values = "False"; }
if (values.ToLower() == "y")
{ values = "True"; }
if (values.ToLower() == "n")
{ values = "False"; }
bool i = false;
if (bool.TryParse(values, out i))
{
return i;
}
else
{ return false; }
}
private static string GetStringTodecimal(string Values)
{
decimal d = 0;
try
{
if (decimal.TryParse(Values, out d))
{
return d.ToString();
}
else { return "null"; }
}
catch { return "null"; }
}
private static string GetStringToDouble(string Values)
{
double d = 0;
try
{
if (double.TryParse(Values, out d))
{
return d.ToString();
}
else { return "null"; }
}
catch { return "null"; }
}
private static string GetStringToFloat(string Values)
{
float d = 0;
try
{
if (float.TryParse(Values, out d))
{
return d.ToString();
}
else { return "null"; }
}
catch { return "null"; }
}
private static string GetStringToDateTime(string Values)
{
try
{
DateTime d = DateTime.Now;
if (DateTime.TryParse(Values, out d))
{ return DateToLongDateString(d); }
else { return "null"; }
}
catch { return "null"; }
}
private static string DateTimeToString(DateTime d)
{
return d.Year.ToString().PadLeft(4, '0') + "-" + d.Month.ToString().PadLeft(2, '0') + "-" + d.Day.ToString().PadLeft(2, '0');
}
private static string DateToLongDateString(DateTime d)
{
return d.Year.ToString().PadLeft(4, '0') + "-" + d.Month.ToString().PadLeft(2, '0') + "-" + d.Day.ToString().PadLeft(2, '0') + " " + d.Hour.ToString().PadLeft(2, '0') + ":" + d.Minute.ToString().PadLeft(2, '0') + ":" + d.Second.ToString().PadLeft(2, '0') + ":" + d.Millisecond.ToString().PadLeft(3, '0');
}
}
}