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