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