public static List <GetType> GetOralcalTableDataTypeInfo(string tableName, ConnectionData connectionData)
{
var entityDataTypeList = new List <GetType>();
try
{
var connection = new OracleConnection("Data Source=" + connectionData.Server + ";user id=" + connectionData.UserName + ";password="******"SELECT col.COLUMN_NAME,col.Data_type FROM INFORMATION_SCHEMA.COLUMNS col";
mySelectDataType += " WHERE col.TABLE_NAME = '" + tableName + "'" + " and col.COLUMN_NAME = ";
mySelectDataType += " (select c.COLUMN_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,INFORMATION_SCHEMA.KEY_COLUMN_USAGE c ";
mySelectDataType += "where pk.TABLE_NAME = '" + tableName + "'" + "and CONSTRAINT_TYPE = 'PRIMARY KEY'" + " and c.TABLE_NAME = pk.TABLE_NAME";
mySelectDataType += " and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME)";
string mySelectQuery = "select COLUMN_NAME, DATA_TYPE,CHARACTER_OCTET_LENGTH from USER_TAB_COLUMNS";
mySelectQuery += " where table_name = '" + tableName + "'";
var myCommand = new OracleCommand(mySelectQuery, connection);
OracleDataReader myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
var type = new GetType
{
NameField = myReader.GetValue(0),
TypeField = myReader.GetValue(1)
};
entityDataTypeList.Add(type);
}
myReader.Close();
connection.Close();
connection.Dispose();
}
catch (Exception exception)
{
// MessageBox.Show(exception.Message);
}
return(entityDataTypeList);
}
public static List <GetType> GetMySqlTableDataTypeInfo(string tableName, ConnectionData connectionData)
{
var entityDataTypeList = new List <GetType>();
try
{
var connection = new MySqlConnection("server=" + connectionData.Server + ";database=" + connectionData.DataBaseName + ";port=" + connectionData.DataBasePort + ";uid=" + connectionData.UserName + ";password="******"SELECT col.COLUMN_NAME,col.Data_type FROM INFORMATION_SCHEMA.COLUMNS col";
mySelectDataType += " WHERE col.TABLE_NAME = '" + tableName + "'" + " and col.COLUMN_NAME = ";
mySelectDataType += " (select c.COLUMN_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,INFORMATION_SCHEMA.KEY_COLUMN_USAGE c ";
mySelectDataType += "where pk.TABLE_NAME = '" + tableName + "'" + "and CONSTRAINT_TYPE = 'PRIMARY KEY'" + " and c.TABLE_NAME = pk.TABLE_NAME";
mySelectDataType += " and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME)";
var myCommand = new MySqlCommand(mySelectDataType, connection);
MySqlDataReader myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
var type = new GetType
{
NameField = myReader.GetValue(0),
TypeField = myReader.GetValue(1)
};
entityDataTypeList.Add(type);
}
myReader.Close();
connection.Close();
connection.Dispose();
}
catch (Exception exception)
{
// MessageBox.Show(exception.Message);
}
return(entityDataTypeList);
}
public static List <GetType> GetMsSqlTableDataTypeInfo(string tableName, ConnectionData connectionData, bool windowsAuthentification)
{
var entityDataTypeList = new List <GetType>();
try
{
SqlConnection connection;
if (windowsAuthentification)
{
connection = new SqlConnection("Data Source=" + connectionData.Server + ";Initial Catalog=" + connectionData.DataBaseName + ";Trusted_Connection=Yes;");
}
else
{
connection = new SqlConnection("Data Source=" + connectionData.Server + ";Initial Catalog=" + connectionData.DataBaseName + ";User id=" + connectionData.UserName + ";Password="******"if((select Count(c.COLUMN_NAME) as 'len'"+ Environment.NewLine;
mySelectDataType += " from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,INFORMATION_SCHEMA.KEY_COLUMN_USAGE c where pk.TABLE_NAME = '"+ tableName + "'" + " and" + Environment.NewLine;
mySelectDataType += "CONSTRAINT_TYPE = 'PRIMARY KEY' and c.TABLE_NAME = pk.TABLE_NAME and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME) > 1)"+ Environment.NewLine;
mySelectDataType += "begin" + Environment.NewLine;
mySelectDataType += "SELECT col.COLUMN_NAME,col.Data_type,col.CHARACTER_OCTET_LENGTH FROM INFORMATION_SCHEMA.COLUMNS col" + Environment.NewLine;
mySelectDataType += " WHERE col.TABLE_NAME = '" + tableName + "'" + " and col.COLUMN_NAME in " + Environment.NewLine;
mySelectDataType += " (select c.COLUMN_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,INFORMATION_SCHEMA.KEY_COLUMN_USAGE c "+ Environment.NewLine;
mySelectDataType += " where pk.TABLE_NAME = '" + tableName + "'" + "and CONSTRAINT_TYPE = 'PRIMARY KEY'" + " and c.TABLE_NAME = pk.TABLE_NAME"+ Environment.NewLine;
mySelectDataType += " and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME)"+ Environment.NewLine;
mySelectDataType += " end" + Environment.NewLine;
mySelectDataType += "else" + Environment.NewLine;
mySelectDataType += " begin" + Environment.NewLine;
mySelectDataType += " SELECT col.COLUMN_NAME,col.Data_type,col.CHARACTER_OCTET_LENGTH FROM INFORMATION_SCHEMA.COLUMNS col" + Environment.NewLine;
mySelectDataType += " WHERE col.TABLE_NAME = '" + tableName + "'" + " and col.COLUMN_NAME = " + Environment.NewLine;
mySelectDataType += " ( select c.COLUMN_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,INFORMATION_SCHEMA.KEY_COLUMN_USAGE c " + Environment.NewLine;
mySelectDataType += "where pk.TABLE_NAME = '" + tableName + "'" + "and CONSTRAINT_TYPE = 'PRIMARY KEY'" + " and c.TABLE_NAME = pk.TABLE_NAME"+ Environment.NewLine;
mySelectDataType += " and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME)";
mySelectDataType += "end" + Environment.NewLine;
var myCommandDataType = new SqlCommand(mySelectDataType, connection);
SqlDataReader myReaderDataType = myCommandDataType.ExecuteReader();
if (myReaderDataType != null)
{
while (myReaderDataType.Read())
{
var type = new GetType
{
NameField = myReaderDataType.GetValue(0),
TypeField = myReaderDataType.GetValue(1),
Length = myReaderDataType.GetValue(2)
};
entityDataTypeList.Add(type);
}
myReaderDataType.Close();
}
connection.Close();
connection.Dispose();
}
catch (Exception exception)
{
// MessageBox.Show(exception.Message);
}
return(entityDataTypeList);
}