public string getInsertScript(string[] Columns)
{
var cols = new List <string>();
//if (!isIdentity) cols.Add(PrimaryKey);
cols.AddRange(Columns);
StringBuilder sb1 = new StringBuilder();
sb1.AppendFormat("INSERT INTO {0} \n", TableName);
sb1.AppendLine("(");
int i = 0;
foreach (string s in cols)
{
if (isIdentity && s.ToLower() == PrimaryKeyField.ToLower())
{
continue;
}
if (i > 0)
{
sb1.Append(",");
}
sb1.Append(s);
sb1.AppendLine();
i++;
}
sb1.AppendLine(")");
sb1.AppendLine(" VALUES( ");
i = 0;
foreach (var s in cols)
{
if (isIdentity && s.ToLower() == PrimaryKeyField.ToLower())
{
continue;
}
if (i > 0)
{
sb1.Append(",");
}
sb1.AppendFormat("@{0}", s);
sb1.AppendLine();
i++;
}
sb1.AppendLine(" )");
if (isIdentity)
{
sb1.AppendLine("select Scope_identity()");
}
return(sb1.ToString());
}
public string getUpdateScript(params string[] columns)
{
StringBuilder sb1 = new StringBuilder();
sb1.AppendFormat("update {0} SET\n", TableName);
int i = 0;
foreach (var s in columns)
{
if (s.ToLower() != PrimaryKeyField.ToLower())
{
if (i > 0)
{
sb1.Append(",");
}
sb1.AppendFormat("{0} = @{0}", s);
sb1.AppendLine();
i++;
}
}
sb1.AppendFormat("\n WHERE {0} = @{0}", PrimaryKeyField);
return(sb1.ToString());
}
public clsCmd getInsertCommand(DataTable t
, clsCmd cmd)
{
clsCmd cmd2 = new clsCmd();
int i = 0;
StringBuilder sb1 = new StringBuilder();
if (isIdentity == false && databaseType == "mssql")
{
sb1.AppendLine("declare @id int ");
sb1.AppendFormat("set @id = (select isnull(max({0}),0) + 1 from {1}) \n\r ", PrimaryKeyField, TableName);
}
sb1.AppendFormat("insert into {0} \n\r (", TableName);
if (isIdentity == false)
{
sb1.AppendFormat(" {0} ", PrimaryKeyField);
i++;
}
//simple column selection
foreach (DataColumn col in t.Columns)
{
if (cmd.ContainFields(col.ColumnName) && col.ColumnName.ToLower() != PrimaryKeyField.ToLower())
{
if (i > 0)
{
sb1.Append(",");
}
sb1.AppendFormat(" {0} ", col.ColumnName);
i++;
cmd2.setValue(col.ColumnName, cmd[col.ColumnName].Value);
}
}
//simeple column selection for filedata
foreach (FileData file in cmd.Files)
{
if (t.Columns.Contains(file.FieldName) && file.FieldName.ToLower() != PrimaryKeyField.ToLower())
{
if (i > 0)
{
sb1.Append(",");
}
sb1.AppendFormat(" {0} ", file.FieldName);
i++;
cmd2.setValue(file.FieldName, file.Data);
if (t.Columns.Contains(file.FieldName + "_contenttype"))
{
sb1.AppendFormat(", {0} ", file.FieldName + "_ContentType");
cmd2.setValue(file.FieldName + "_ContentType", file.ContentType);
}
}
}
i = 0;
sb1.AppendLine(") \n\r values(");
if (isIdentity == false)
{
sb1.AppendFormat(" @id ", PrimaryKeyField);
i++;
}
//simple value selection
foreach (DataColumn col in t.Columns)
{
if (cmd.ContainFields(col.ColumnName) && col.ColumnName.ToLower() != PrimaryKeyField.ToLower())
{
if (i > 0)
{
sb1.Append(",");
}
sb1.AppendFormat(" @{0} ", col.ColumnName);
i++;
}
}
//simple value selection for file.
foreach (FileData file in cmd.Files)
{
if (t.Columns.Contains(file.FieldName) && file.FieldName.ToLower() != PrimaryKeyField.ToLower())
{
if (i > 0)
{
sb1.Append(",");
}
sb1.AppendFormat(" @{0} ", file.FieldName);
i++;
if (t.Columns.Contains(file.FieldName + "_contenttype"))
{
sb1.AppendFormat(", @{0} ", file.FieldName + "_ContentType");
}
}
}
sb1.Append(" )");
if (databaseType == "mssql")
{
if (isIdentity)
{
sb1.AppendLine("select SCOPE_IDENTITY()");
}
else
{
sb1.AppendLine("select @id");
}
}
cmd2.SQL = sb1.ToString();
return(cmd2);
}
public clsCmd getUpdateCommand(DataTable t
, clsCmd cmd)
{
int iID = cmd.getIntValue(PrimaryKeyField);
if (iID == 0)
{
throw new Exception("Id value is 0, you can't update record !");
}
StringBuilder sb1 = new StringBuilder();
clsCmd cmd2 = new clsCmd();
int i = 0;
sb1.AppendFormat("update {0} set \r\n", TableName);
foreach (DataColumn col in t.Columns)
{
if (cmd.ContainFields(col.ColumnName) && col.ColumnName.ToLower() != PrimaryKeyField.ToLower())
{
if (i > 0)
{
sb1.Append(",");
}
sb1.AppendFormat(" {0} = @{0} ", col.ColumnName);
i++;
cmd2.setValue(col.ColumnName, cmd[col.ColumnName].Value);
}
}
foreach (FileData file in cmd.Files)
{
if (t.Columns.Contains(file.FieldName) && file.FieldName.ToLower() != PrimaryKeyField.ToLower())
{
if (i > 0)
{
sb1.Append(",");
}
sb1.AppendFormat(" {0} = @{0} ", file.FieldName);
i++;
cmd2.setValue(file.FieldName, file.Data);
if (t.Columns.Contains(file.FieldName + "_contenttype"))
{
sb1.AppendFormat(", {0} = @{0} ", file.FieldName + "_ContentType");
cmd2.setValue(file.FieldName + "_ContentType", file.ContentType);
}
}
}
sb1.AppendFormat(" where {0} = {1} \n\r", PrimaryKeyField, iID);
if (databaseType == "mssql")
{
sb1.AppendFormat("select {0}", iID);
}
cmd2.SQL = sb1.ToString();
return(cmd2);
}