/// <summary>
/// 编写SCADA中SQL的Delete语句
/// </summary>
/// <param name="Name">列名集合</param>
/// <param name="TableName">表名</param>
/// <param name="Status">备注集合</param>
/// <returns>SCADA中SQL的Delete语句</returns>
public static string SCADA_Delete(string[] Name, string TableName, string[] Status)
{
int num = 1;
int i = 0;
int j = 0;
string SCADA_Str = @"string InsertStr = ""DELETE FROM [" + TableName + @"] WHERE """;
foreach (string name in Name)
{
for (i = 0; i < Name.Length; i++)
{
if (CFunctions.IsInclude(Status[i], "[PK]"))
{
j++;
}
}
if (CFunctions.IsInclude(Status[num - 1], "[PK]"))
{
if (num != j)
{
SCADA_Str += @"
+ """ + name + " = '\" + " + "((TextBox)MyController[\"" + name + "\"]).Text.Trim() + \"',\"";
}
else
{
SCADA_Str += @"
+ """ + name + " = '\" + " + "((TextBox)MyController[\"" + name + "\"]).Text.Trim() + \"'\";";
}
num++;
}
}
return(SCADA_Str);
}
/// <summary>
/// 生成SQL数据库Insert语句
/// </summary>
/// <param name="Name">列名集合</param>
/// <param name="TableName">表名</param>
/// <returns>Insert语句</returns>
static string GetSqlInsert(string[] Name, string TableName, string[] Status)
{
string InsertStr = @"public bool Add()
{
string StrSql = """";
StrSql += ""INSERT INTO [" + TableName + "](";
int num = 1;
int i = 0;
int j = 0;
foreach (string name in Name)
{
if (!CFunctions.IsInclude(Status[i++], "[AK]"))
{
if (num++ != Name.Length)
{
InsertStr += name + ",";
}
else
{
InsertStr += name;
}
}
else
{
num++;
}
}
InsertStr += @")"";
StrSql += "" VALUES(""";
num = 1;
foreach (string name in Name)
{
if (!CFunctions.IsInclude(Status[j++], "[AK]"))
{
if (num++ != Name.Length)
{
InsertStr += "+" + @"
CSql.FormatInsert(_" + name + ", false)";
}
else
{
InsertStr += "+" + @"
CSql.FormatInsert(_" + name + ", true)";
}
}
else
{
num++;
}
}
InsertStr += @";
return CSql.ExecuteSql(StrSql);
}";
return(InsertStr);
}
/// <summary>
/// SQL表生成语句的列名部分
/// </summary>
/// <param name="ColumnName">列名</param>
/// <param name="DataType">数据类型</param>
/// <param name="IsNull">是否允许NULL值</param>
/// <param name="IsFinal">是否为最后一列</param>
/// <returns></returns>
public static string GetDataTableColumnString(string ColumnName, string DataType, string Status, bool IsFinal)
{
string ColumnStr = "[" + ColumnName + "] " + DataType + " ";
if (IsFinal)
{
if (CFunctions.IsInclude(Status, "[AK]"))
{
ColumnStr += "IDENTITY(1,1) ";
}
if (CFunctions.IsInclude(Status, "[PK]"))
{
ColumnStr += "PRIMARY KEY)";
}
else
{
if (CFunctions.IsInclude(Status, "[NN]"))
{
ColumnStr += "Null)";
}
else
{
ColumnStr += "NOT NULL)";
}
}
}
else
{
if (CFunctions.IsInclude(Status, "[AK]"))
{
ColumnStr += "IDENTITY(1,1) ";
}
if (CFunctions.IsInclude(Status, "[PK]"))
{
ColumnStr += "PRIMARY KEY,";
}
else
{
if (CFunctions.IsInclude(Status, "[NN]"))
{
ColumnStr += "Null,";
}
else
{
ColumnStr += "NOT NULL,";
}
}
}
return(ColumnStr);
}
/// <summary>
/// 编写SCADA中SQL的Insert语句
/// </summary>
/// <param name="Name">列名集合</param>
/// <param name="TableName">表名</param>
/// <param name="Status">备注集合</param>
/// <returns>SCADA中SQL的Insert语句</returns>
public static string SCADA_Insert(string[] Name, string TableName, string[] Status)
{
string SCADA_Str = @"string InsertStr = ""INSERT INTO [" + TableName + @"](";
int num = 1;
int i = 0;
int j = 0;
foreach (string name in Name)
{
if (!CFunctions.IsInclude(Status[i++], "[AK]"))
{
if (num++ != Name.Length)
{
SCADA_Str += name + ",";
}
else
{
SCADA_Str += name;
}
}
else
{
num++;
}
}
SCADA_Str += ") VALUES('\"";
num = 1;
foreach (string name in Name)
{
if (!CFunctions.IsInclude(Status[j++], "[AK]"))
{
if (num++ != Name.Length)
{
SCADA_Str += "+" + @"
((TextBox)MyController[""" + name + @"""]).Text.Trim() + ""','""";
}
else
{
SCADA_Str += "+" + @"
((TextBox)MyController[""" + name + @"""]).Text.Trim() + ""')"";";
}
}
else
{
num++;
}
}
return(SCADA_Str);
}
/// <summary>
/// 写入数据库前判断值是否为空，为空则无法写入
/// </summary>
/// <param name="Name">列名集合</param>
/// <param name="Status">备注集合</param>
/// <returns>判断语句</returns>
public static string SCADA_NotNull(string[] Name, string[] Status)
{
string SCADA_Str = "";
int i = 0;
foreach (string name in Name)
{
if (CFunctions.IsInclude(Status[i++], "[NN]"))
{
SCADA_Str += @"
if (((TextBox)MyController[""" + name + @"""]).Text.Trim().Equals(""""))
{
MessageBox.Show(""不能为空！"", ""提示信息"", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
";
}
}
return(SCADA_Str);
}
/// <summary>
/// 生成判断是否存在该记录的语句
/// </summary>
/// <param name="Name">列名集合</param>
/// <param name="TableName">表名</param>
/// <returns>判断是否存在该记录的语句</returns>
static string GetSqlExists(string[] Name, string TableName, string[] Status)
{
string ExistsStr = @"public bool Exists(";
int num = 1;
foreach (string name in Name)
{
int j = 0;
for (int i = 0; i < Name.Length; i++)
{
if (CFunctions.IsInclude(Status[i], "[PK]"))
{
j++;
}
}
if (CFunctions.IsInclude(Status[num - 1], "[PK]"))
{
if (num != j)
{
ExistsStr += "string " + name + ",";
}
else
{
ExistsStr += "string " + name;
}
num++;
}
}
ExistsStr += @")
{
string StrSql = """";
StrSql += ""SELECT COUNT(1) FROM [" + TableName + @"]"";
StrSql += "" WHERE """;
num = 1;
foreach (string name in Name)
{
int j = 0;
for (int i = 0; i < Name.Length; i++)
{
if (CFunctions.IsInclude(Status[i], "[PK]"))
{
j++;
}
}
if (CFunctions.IsInclude(Status[num - 1], "[PK]"))
{
if (num != j)
{
ExistsStr += "+" + @"
CSql.FormatWhere(""" + name + "\"," + name + ", false)";
}
else
{
ExistsStr += "+" + @"
CSql.FormatWhere(""" + name + "\"," + name + ", true);";
}
num++;
}
}
ExistsStr += @"
return CSql.Exists(StrSql);
}";
return(ExistsStr);
}
/// <summary>
/// 生成获取实体对象的语句
/// </summary>
/// <param name="Name">列名集合</param>
/// <param name="TableName">表名</param>
/// <returns>获取实体对象的语句</returns>
static string GetSqlModel(string[] Name, string TableName, string[] Status)
{
string ModelStr = @"public void GetModel(";
int num = 1;
foreach (string name in Name)
{
int j = 0;
for (int i = 0; i < Name.Length; i++)
{
if (CFunctions.IsInclude(Status[i], "[PK]"))
{
j++;
}
}
if (CFunctions.IsInclude(Status[num - 1], "[PK]"))
{
if (num != j)
{
ModelStr += "string " + name + ",";
}
else
{
ModelStr += "string " + name;
}
num++;
}
}
ModelStr += @")
{
string StrSql = """";
StrSql += ""SELECT "";
StrSql += """;
num = 1;
foreach (string name in Name)
{
if (num++ != Name.Length)
{
ModelStr += name + ",";
}
else
{
ModelStr += name;
}
}
ModelStr += @""";
StrSql += "" FROM [" + TableName + @"]"";
StrSql += "" WHERE """;
num = 1;
foreach (string name in Name)
{
int j = 0;
for (int i = 0; i < Name.Length; i++)
{
if (CFunctions.IsInclude(Status[i], "[PK]"))
{
j++;
}
}
if (CFunctions.IsInclude(Status[num - 1], "[PK]"))
{
if (num != j)
{
ModelStr += "+" + @"
CSql.FormatWhere(""" + name + "\"," + name + ", false)";
}
else
{
ModelStr += "+" + @"
CSql.FormatWhere(""" + name + "\"," + name + ", true);";
}
num++;
}
}
ModelStr += @"
DataSet ds = CSql.CreateDataSet(StrSql);
if(ds.Tables[0].Rows.Count > 0)
{";
num = 1;
foreach (string name in Name)
{
ModelStr += @"
_" + name + @" = ds.Tables[0].Rows[0][""" + name + @"""].ToString();";
}
ModelStr += @"
}
}";
return(ModelStr);
}
/// <summary>
/// 生成SQL数据库Delete语句
/// </summary>
/// <param name="Name">列名集合</param>
/// <param name="TableName">表名</param>
/// <returns>Delete语句</returns>
static string GetSqlDelete(string[] Name, string TableName, string[] Status)
{
string DeleteStr = @"public bool Delete(";
int num = 1;
foreach (string name in Name)
{
int j = 0;
for (int i = 0; i < Name.Length; i++)
{
if (CFunctions.IsInclude(Status[i], "[PK]"))
{
j++;
}
}
if (CFunctions.IsInclude(Status[num - 1], "[PK]"))
{
if (num != j)
{
DeleteStr += "string " + name + ",";
}
else
{
DeleteStr += "string " + name;
}
num++;
}
}
DeleteStr += @")
{
string StrSql = """";
StrSql += ""DELETE FROM [" + TableName + @"]"";";
num = 1;
DeleteStr += @"
StrSql += "" WHERE """;
foreach (string name in Name)
{
int j = 0;
for (int i = 0; i < Name.Length; i++)
{
if (CFunctions.IsInclude(Status[i], "[PK]"))
{
j++;
}
}
if (CFunctions.IsInclude(Status[num - 1], "[PK]"))
{
if (num != j)
{
DeleteStr += "+" + @"
CSql.FormatWhere(""" + name + "\"," + name + ", false)";
}
else
{
DeleteStr += "+" + @"
CSql.FormatWhere(""" + name + "\"," + name + ", true);";
}
num++;
}
}
DeleteStr += @"
return CSql.ExecuteSql(StrSql);
}";
return(DeleteStr);
}
/// <summary>
/// 生成SQL数据库Update语句
/// </summary>
/// <param name="Name">列名集合</param>
/// <param name="TableName">表名</param>
/// <returns>Update语句</returns>
static string GetSqlUpdate(string[] Name, string TableName, string[] Status)
{
string UpdateStr = @"public bool Update(";
int num = 1;
foreach (string name in Name)
{
int j = 0;
for (int i = 0; i < Name.Length; i++)
{
if (CFunctions.IsInclude(Status[i], "[PK]"))
{
j++;
}
}
if (CFunctions.IsInclude(Status[num - 1], "[PK]"))
{
if (num != j)
{
UpdateStr += "string " + name + ",";
}
else
{
UpdateStr += "string " + name;
}
num++;
}
}
UpdateStr += @")
{
string StrSql = """";
StrSql += ""UPDATE [" + TableName + @"]"";
StrSql += "" SET """;
num = 1;
int m = 0;
foreach (string name in Name)
{
if (!CFunctions.IsInclude(Status[m++], "[AK]"))
{
if (num++ != Name.Length)
{
UpdateStr += "+" + @"
CSql.FormatUpdate(""" + name + "\",_" + name + ", false)";
}
else
{
UpdateStr += "+" + @"
CSql.FormatUpdate(""" + name + "\",_" + name + ", true);";
}
}
else
{
num++;
}
}
UpdateStr += @"
StrSql += "" WHERE """;
num = 1;
foreach (string name in Name)
{
int j = 0;
for (int i = 0; i < Name.Length; i++)
{
if (CFunctions.IsInclude(Status[i], "[PK]"))
{
j++;
}
}
if (CFunctions.IsInclude(Status[num - 1], "[PK]"))
{
if (num != j)
{
UpdateStr += "+" + @"
CSql.FormatWhere(""" + name + "\"," + name + ", false)";
}
else
{
UpdateStr += "+" + @"
CSql.FormatWhere(""" + name + "\"," + name + ", true)";
}
num++;
}
}
UpdateStr += @";
return CSql.ExecuteSql(StrSql);
}";
return(UpdateStr);
}
