/// <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); }