/// <summary>
/// Gets Primary_Keys schema information about a MySQL table
/// </summary>
/// <param name="tableName">Name of the table</param>
/// <returns>DataTable with schema information</returns>
public override Epi.DataSets.TableKeysSchema.Primary_KeysDataTable GetTableKeysSchema(string tableName)
{
DataSets.TableKeysSchema schema = new Epi.DataSets.TableKeysSchema();
NpgsqlConnection conn = this.GetNativeConnection();
bool alreadyOpen = (conn.State != ConnectionState.Closed);
try
{
if (!alreadyOpen)
{
OpenConnection(conn);
}
string query = "select KU.TABLE_CATALOG, KU.TABLE_SCHEMA, KU.TABLE_NAME, COLUMN_NAME, " +
"0 as COLUMN_PROPID, ORDINAL_POSITION as ORDINAL, KU.CONSTRAINT_NAME as PK_NAME " +
"from INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC " +
"inner join " +
"INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU " +
"on TC.CONSTRAINT_TYPE = 'primary key' and " +
"TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME " +
"where KU.TABLE_NAME = '" + tableName +
"' order by KU.ORDINAL_POSITION;";
NpgsqlDataAdapter da = new NpgsqlDataAdapter(query, conn);
//Logger.Log(query);
da.Fill(schema.Primary_Keys);
da.Dispose();
}
catch (Exception ex)
{
throw new GeneralException("Unable to obtain primary keys schema.", ex);
}
finally
{
if (!alreadyOpen && conn.State != ConnectionState.Closed)
{
CloseConnection(conn);
}
}
return schema.Primary_Keys;
}
/// <summary>
/// �����������Ƿ����
/// </summary>
/// <param name="tablename">������</param>
/// <param name="indexname">��������</param>
/// <returns></returns>
public bool IsIndexExist(string tablename, string indexname)
{
string sqlstr = "SELECT * from pg_indexes where tablename= '" + tablename + "'";
DataTable tmpdt = new DataTable();
NpgsqlDataAdapter nda = new NpgsqlDataAdapter(sqlstr, m_pDbConnection);
nda.Fill(tmpdt);
for (int i = 0; i <= tmpdt.Rows.Count - 1; i++)
{
//��������index name
string tmpnamestr = tmpdt.Rows[i][2].ToString();
if (tmpnamestr.ToLower() == indexname.ToLower())
{
nda.Dispose();
return true;
}
}
nda.Dispose();
return false;
}
/// <summary>
/// �����Ƿ����
/// </summary>
/// <param name="layername">������</param>
/// <returns></returns>
public bool IsExist(string layername)
{
string sqlstr = "select count(*) from pg_class where relname ='" + layername + "'";
DataTable tmpdt = new DataTable();
NpgsqlDataAdapter nda = new NpgsqlDataAdapter(sqlstr, m_pDbConnection);
nda.Fill(tmpdt);
string tmpCount = tmpdt.Rows[0][0].ToString();
int num = Convert.ToInt32(tmpCount);
nda.Dispose();
if (num > 0)
{
return true;
}
else
{
return false;
}
}
/// <summary>
/// ������ijһ���Ƿ����
/// </summary>
/// <param name="tablename">������</param>
/// <param name="columnname">������</param>
/// <returns></returns>
public bool IsColumnExist(string tablename, string columnname)
{
string sqlstr = "SELECT a.attname,pg_catalog.format_type(a.atttypid, a.atttypmod) AS data_type" + " "
+ "FROM pg_catalog.pg_attribute a,(SELECT c.oid FROM pg_catalog.pg_class c " + " "
+ "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE (c.relname) =lower(' " + tablename + "')" + " "
+ "AND (n.nspname) = lower('public')) b WHERE a.attrelid = b.oid AND a.attnum > 0 AND NOT a.attisdropped ORDER BY a.attnum";
DataTable tmpdt = new DataTable();
NpgsqlDataAdapter nda = new NpgsqlDataAdapter(sqlstr, m_pDbConnection);
nda.Fill(tmpdt);
for (int i = 0; i <= tmpdt.Rows.Count - 1; i++)
{
string column = tmpdt.Rows[i][0].ToString();
if (column.ToLower() == columnname.ToLower())
{
nda.Dispose();
return true;
}
}
nda.Dispose();
return false;
}
/// <summary>
/// ������ѯ
/// </summary>
/// <param name="sQuery"></param>
/// <returns></returns>
public DataTable DoQueryEx(string sQuery)
{
DataTable dt = null;//�������ݱ����������Ϊ��
try
{
dt = new DataTable();
//����oracle���������������ڲ�ѯ���ġ�ɾ������
NpgsqlDataAdapter nda = new NpgsqlDataAdapter(sQuery, m_pDbConnection);
//fill���Բ���Ϊdataset��datatable
nda.Fill(dt);
//�ͷ�ndaʹ�õķ��й�����Դ
nda.Dispose();
return dt;
}
catch (Exception ex)
{
//MessageBox.Show(ex.Message + "\r\n" + ex.StackTrace);
return null;
}
}
/// <summary>
/// Executes command.
/// </summary>
/// <returns></returns>
public DataSet Execute()
{
DataSet dsRetVal = null;
using(NpgsqlConnection con = new NpgsqlConnection(m_connStr)){
con.Open();
m_SqlCmd.Connection = con;
dsRetVal = new DataSet();
NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(m_SqlCmd);
adapter.Fill(dsRetVal);
adapter.Dispose();
}
return dsRetVal;
}
