public DataSource(string table_name, string connection_string, DBEngineType tip, bool s_auto, bool u_auto, bool d_auto, bool i_auto, byte r) { select = " "; insert = " "; update = " "; delete = " "; StringBuilder str = new StringBuilder(); StringBuilder str_param = new StringBuilder(); identity = new StringBuilder(); identities = new ArrayList(); identities.Clear(); dbtype = tip; con_string = connection_string; var db = new Database(tip); db.con_string = connection_string; if (tip == DBEngineType.MsSql) db.text = "select syscolumns.name as [Column] from sysobjects, syscolumns where sysobjects.id = syscolumns.id and lower(sysobjects.xtype) = 'u'and sysobjects.name = @t_name order by syscolumns.name"; else if (tip == DBEngineType.MySql) db.text = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = @t_name"; else if (tip == DBEngineType.DB2) db.text = "select colname from syscat.columns where tabname= @t_name"; db.add_parameter("@t_name", table_name); var db2 = new Database(tip); db2.con_string = connection_string; if (tip == DBEngineType.MsSql) db2.text = "select syscolumns.name as [Column] from sysobjects, syscolumns where sysobjects.id = syscolumns.id and syscolumns.colstat=1 and lower(sysobjects.xtype) = 'u' and sysobjects.name = @t_name order by syscolumns.name"; else if (tip == DBEngineType.MySql) db2.text = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = @t_name and COlUMN_KEY='PRI'"; else if (tip == DBEngineType.DB2) db2.text = "select colname from syscat.columns where tabname= @t_name and (KEYSEQ is not null)"; db2.add_parameter("@t_name", table_name); foreach (DataRow dare in db2.table.Rows) { identities.Add(dare[0].ToString()); if (identity.Length == 0) identity.Append(String.Format("{0}=@{0}", dare[0].ToString())); else identity.Append(String.Format(" and {0}=@{0} ", dare[0].ToString())); } if (identity.Length > 0) delete = String.Format("delete from {0} where {1} ", table_name, identity.ToString()); else { foreach (DataRow dare in db.table.Rows) { identities.Add(dare[0].ToString()); if (identity.Length == 0) identity.Append(String.Format("{0}=@{0}", dare[0].ToString())); else identity.Append(String.Format(" and {0}=@{0} ", dare[0].ToString())); } } delete = String.Format("delete from {0} where {1} ", table_name, identity.ToString()); foreach (DataRow dr in db.table.Rows) { if (str.Length == 0) { str.Append(dr[0]); } else str.Append("," + dr[0]); } select = String.Format("select {0} from {1}", str.ToString(), table_name); str_param = new StringBuilder(); str = new StringBuilder(); foreach (DataRow dr in db.table.Rows) { if (!identities.Contains(dr[0].ToString())) { if (str.Length == 0) { str.Append(dr[0]); str_param.Append(String.Format("@{0}", dr[0])); } else { str.Append("," + dr[0]); str_param.Append(String.Format(",@{0}", dr[0])); } } } columns = str.ToString(); insert = (String.Format("insert into {1} ({0}) values({2})", str.ToString(), table_name, str_param.ToString())); str_param = new StringBuilder(); str = new StringBuilder(); foreach (DataRow dr in db.table.Rows) { if (!identities.Contains(dr[0].ToString())) { if (str_param.Length == 0) { str_param.Append(String.Format("{0}=@{0}", dr[0])); } else { str_param.Append(String.Format(",{0}=@{0}", dr[0])); } } } update = (String.Format("update {1} set {2} where {0}", identity, table_name, str_param.ToString())); //else // update = (String.Format("update {1} set {2} ", table_name, str_param.ToString())); }
public DataSource(string table_name, string cstr_name, DBEngineType tip, bool s_auto, bool u_auto, bool d_auto, bool i_auto) { select = " "; insert = " "; update = " "; delete = " "; StringBuilder str = new StringBuilder(); StringBuilder str_param = new StringBuilder(); identity = new StringBuilder(); identities = new ArrayList(); var db = new Database(DBEngineType.MsSql); identities.Clear(); db.con_name = cstr_name; db.text = "select syscolumns.name as [Column] from sysobjects, syscolumns where sysobjects.id = syscolumns.id and lower(sysobjects.xtype) = 'u'and sysobjects.name = @t_name order by syscolumns.name"; db.add_parameter("@t_name", table_name); var db2 = new Database(DBEngineType.MsSql); db2.con_name = cstr_name; db2.text = "select syscolumns.name as [Column] from sysobjects, syscolumns where sysobjects.id = syscolumns.id and syscolumns.colstat=1 and lower(sysobjects.xtype) = 'u' and sysobjects.name = @t_name order by syscolumns.name"; db2.add_parameter("@t_name", table_name); foreach (DataRow dare in db2.table.Rows) { identities.Add(dare[0].ToString()); if (identity.Length == 0) identity.Append(String.Format("{0}=@{0}", dare[0].ToString())); else identity.Append(String.Format(" and {0}=@{0} ", dare[0].ToString())); } if (identity.Length > 0) delete = String.Format("delete from {0} where {1} ", table_name, identity.ToString()); else { foreach (DataRow dare in db.table.Rows) { identities.Add(dare[0].ToString()); if (identity.Length == 0) identity.Append(String.Format("{0}=@{0}", dare[0].ToString())); else identity.Append(String.Format(" and {0}=@{0} ", dare[0].ToString())); } } delete = String.Format("delete from {0} where {1} ", table_name, identity.ToString()); foreach (DataRow dr in db.table.Rows) { if (str.Length == 0) { str.Append(dr[0]); } else str.Append("," + dr[0]); } select = String.Format("select {0} from {1}", str.ToString(), table_name); str_param = new StringBuilder(); str = new StringBuilder(); foreach (DataRow dr in db.table.Rows) { if (!identities.Contains(dr[0].ToString())) { if (str.Length == 0) { str.Append(dr[0]); str_param.Append(String.Format("@{0}", dr[0])); } else { str.Append("," + dr[0]); str_param.Append(String.Format(",@{0}", dr[0])); } } } columns = str.ToString(); insert = (String.Format("insert into {1} ({0}) values({2})", str.ToString(), table_name, str_param.ToString())); str_param = new StringBuilder(); str = new StringBuilder(); foreach (DataRow dr in db.table.Rows) { if (!identities.Contains(dr[0].ToString())) { if (str_param.Length == 0) { str_param.Append(String.Format("{0}=@{0}", dr[0])); } else { str_param.Append(String.Format(",{0}=@{0}", dr[0])); } } } update = (String.Format("update {1} set {2} where {0}", identity, table_name, str_param.ToString())); //else // update = (String.Format("update {1} set {2} ", table_name, str_param.ToString())); }