/// <summary>
/// 查询表信息
/// </summary>
/// <param name="conStr"></param>
/// <param name="sql"></param>
/// <returns></returns>
internal async static Task <DataTable> QueryTableInfo(string conStr, string sql)
{
return(await Task.Run(() =>
{
DataTable dt = new DataTable();
PostgreSqlHelper.con = PostgreSqlHelper.NewConnectionMethod(conStr);
var cmd = new NpgsqlCommand(sql, PostgreSqlHelper.con);
try
{
PostgreSqlHelper.con.Open();
NpgsqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.KeyInfo);
dt = sdr.GetSchemaTable(); //获得表的结构
sdr.Close();
PostgreSqlHelper.con.Close();
PostgreSqlHelper.con.Dispose();
}
catch (Exception e)
{
PostgreSqlHelper.con.Close();
PostgreSqlHelper.con.Dispose();
throw e;
}
return dt;
}));
}
public DataTable ConvertDataTable(NpgsqlDataReader dataReader)
{
if (!(dataReader is null))
{
DataTable dtSchema = dataReader.GetSchemaTable();
DataTable dataTable = new DataTable();
List <DataColumn> listCols = new List <DataColumn>();
if (!(dtSchema is null))
{
foreach (DataRow drow in dtSchema.Rows)
{
string columnName = Convert.ToString(drow["ColumnName"]);
DataColumn column = new DataColumn(columnName, (Type)(drow["DataType"]));
listCols.Add(column);
}
dataTable.Columns.AddRange(listCols.ToArray());
}
while (dataReader.Read())
{
DataRow dataRow = dataTable.NewRow();
for (int i = 0; i < listCols.Count; i++)
{
dataRow[listCols[i]] = dataReader[i];
}
dataTable.Rows.Add(dataRow);
}
return(dataTable);
}
public void PrimaryKeyFieldsMetadataSupport()
{
NpgsqlCommand command = new NpgsqlCommand("select * from metadatatest1", TheConnection);
NpgsqlDataReader dr = command.ExecuteReader(CommandBehavior.KeyInfo);
DataTable metadata = dr.GetSchemaTable();
Boolean keyfound = false;
foreach (DataRow r in metadata.Rows)
{
if ((Boolean)r["IsKey"])
{
Assert.AreEqual("field_pk", r["ColumnName"]);
keyfound = true;
}
}
if (!keyfound)
{
Assert.Fail("No primary key found!");
}
dr.Close();
}
public virtual void DoIsIdentityMetadataSupport()
{
NpgsqlCommand command = new NpgsqlCommand("select * from metadatatest1", TheConnection);
NpgsqlDataReader dr = command.ExecuteReader(CommandBehavior.KeyInfo);
DataTable metadata = dr.GetSchemaTable();
Boolean identityfound = false;
foreach (DataRow r in metadata.Rows)
{
if ((Boolean)r["IsAutoIncrement"])
{
Assert.AreEqual("field_serial", r["ColumnName"]);
identityfound = true;
}
}
if (!identityfound)
{
Assert.Fail("No identity column found!");
}
dr.Close();
}
/// <summary>
/// 执行查询SQL语句
/// </summary>
/// <param name="StrConn">连接字符串</param>
/// <param name="strConn">连接字符串</param>
/// <param name="TransID">事务ID</param>
/// <param name="SqlText">Sql 文本</param>
/// <param name="SqlParams">Sql 参数，参数值所在位置必须与参数名一致</param>
/// <returns>返回数据集</returns>
public byte[] Query(string TransID, string StrConn, string SqlText, object[] SqlParams)
{
NpgsqlConnection oConn = GetNpgsqlConnection(TransID, StrConn);
try
{
using (NpgsqlCommand oCmd = new NpgsqlCommand(SqlText, oConn))
{
CreateSqlParam(oCmd, SqlParams);
using (NpgsqlDataReader odr = oCmd.ExecuteReader(CommandBehavior.CloseConnection))
{
using (DataTable dtSchema = odr.GetSchemaTable())
{
using (CJia.Net.Serialization.SerializationWriter sw = new Net.Serialization.SerializationWriter())
{
SerializationSchema(sw, dtSchema);
object[] aryValues = new object[odr.FieldCount];
while (odr.Read())
{
odr.GetValues(aryValues);
sw.WriteOptimized(aryValues);
}
aryValues = null;
return(sw.ToArray());
}
}
}
}
}
finally
{
CloseConnection(TransID, oConn);
}
}
/// <summary>
/// 执行查询SQL语句
/// </summary>
/// <param name="StrConn">连接字符串</param>
/// <param name="strConn">连接字符串</param>
/// <param name="TransID">事务ID</param>
/// <param name="SqlText">Sql 文本</param>
/// <param name="SqlParams">Sql 参数，参数值所在位置必须与参数名一致</param>
/// <returns>返回数据集</returns>
public DataTable QueryTable(string TransID, string StrConn, string SqlText, object[] SqlParams)
{
NpgsqlConnection oConn = GetNpgsqlConnection(TransID, StrConn);
try
{
using (NpgsqlCommand oCmd = new NpgsqlCommand(SqlText, oConn))
{
CreateSqlParam(oCmd, SqlParams);
using (NpgsqlDataReader odr = oCmd.ExecuteReader(CommandBehavior.CloseConnection))
{
using (DataTable dtSchema = odr.GetSchemaTable())
{
DataTable dtResult = BuildDataTable(dtSchema);
object[] aryValues = new object[odr.FieldCount];
dtResult.BeginLoadData();
while (odr.Read())
{
odr.GetValues(aryValues);
dtResult.LoadDataRow(aryValues, true);
}
dtResult.EndLoadData();
aryValues = null;
return(dtResult);
}
}
}
}
finally
{
CloseConnection(TransID, oConn);
}
}
public static void Main(String[] args)
{
NpgsqlConnection conn = null;
try
{
conn = new NpgsqlConnection(NpgsqlTests.getConnectionString());
conn.Open();
Console.WriteLine("Connection completed");
NpgsqlCommand command = new NpgsqlCommand();
command.CommandText = "select * from tablea;";
command.Connection = conn;
NpgsqlDataReader dr = command.ExecuteReader();
Int32 j;
do
{
j = dr.FieldCount;
Console.WriteLine(j);
DataTable dt = dr.GetSchemaTable();
DataRowCollection schemarows = dt.Rows;
Int32 i;
for (i = 0; i < j; i++)
{
Console.Write("{0} \t", schemarows[i][0]);
}
Console.WriteLine();
Console.WriteLine("============================================");
while (dr.Read())
{
for (i = 0; i < j; i++)
{
Console.Write("{0} \t", dr[i]);
}
Console.WriteLine();
}
} while(dr.NextResult());
dr.Close();
}
catch (NpgsqlException e)
{
Console.WriteLine(e.ToString());
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
/// <summary>
/// Converts a MySqlDataReader to a DataSet
/// <param name='reader'>
/// MySqlDataReader to convert.</param>
/// <returns>
/// DataSet filled with the contents of the reader.</returns>
/// </summary>
public DataSet DataReaderToDataSet(NpgsqlDataReader reader)
{
lock (obj_lock)
{
DataSet dataSet = new DataSet();
do
{
// Create new data table
DataTable schemaTable = reader.GetSchemaTable();
DataTable dataTable = new DataTable();
if (schemaTable != null)
{
// A query returning records was executed
for (int i = 0; i < schemaTable.Rows.Count; i++)
{
DataRow dataRow = schemaTable.Rows[i];
// Create a column name that is unique in the data table
string columnName = (string)dataRow["ColumnName"]; //+ "<C" + i + "/>";
// Add the column definition to the data table
DataColumn column = new DataColumn(columnName, (Type)dataRow["DataType"]);
dataTable.Columns.Add(column);
}
dataSet.Tables.Add(dataTable);
// Fill the data table we just created
while (reader.Read())
{
DataRow dataRow = dataTable.NewRow();
for (int i = 0; i < reader.FieldCount; i++)
{
dataRow[i] = reader.GetValue(i);
}
dataTable.Rows.Add(dataRow);
}
}
else
{
// No records were returned
DataColumn column = new DataColumn("RowsAffected");
dataTable.Columns.Add(column);
dataSet.Tables.Add(dataTable);
DataRow dataRow = dataTable.NewRow();
dataRow[0] = reader.RecordsAffected;
dataTable.Rows.Add(dataRow);
}
}while (reader.NextResult());
return(dataSet);
}
}
private clsResponse _ReaderRead()
{
clsResponse response = new clsResponse();
string msg = "";
DataTable dt = null;
try
{
if (myReader_.Read())
{
if (myTable_ == null)
{
myTable_ = new DataTable();
dt = myReader_.GetSchemaTable();
DataColumn column = null;
for (int i = 0; i < dt.Rows.Count; i++)
{
column = new DataColumn();
column.ColumnName = dt.Rows[i]["ColumnName"].ToString();
column.DataType = Type.GetType(dt.Rows[i]["DataType"].ToString());
myTable_.Columns.Add(column);
}
}
dt = myTable_.Clone();
DataRow row = dt.NewRow();
for (int i = 0; i < myReader_.FieldCount; i++)
{
row[i] = myReader_[i];
}
dt.Rows.Add(row);
response.xAddDataTable(dt);
}
else
{
response.xSetReturn("EOF", "True");
_ReaderClose();
return response;
}
}
catch (Exception ex)
{
msg = "DataReaderからの読み込みに失敗しました。";
msg += ex.Message + Environment.NewLine;
response.xSetError(msg);
msg += ex.StackTrace;
////////clsTextLogger.xWriteTextLog(
//////// MethodBase.GetCurrentMethod().DeclaringType.FullName + "." +
//////// MethodBase.GetCurrentMethod().Name, msg);
_ReaderClose();
return response;
}
return response;
}
private static int FindField(NpgsqlDataReader reader, string fieldName)
{
var schema = reader.GetSchemaTable();
var rows = schema.Columns["ColumnName"].Table.Rows;
foreach (var row in rows)
{
var columnName = (string)((DataRow)row).ItemArray[0];
if (columnName == fieldName)
{
return((int)((DataRow)row).ItemArray[1]);
}
}
return(0);
}
// ------------------------------ Database info ------------------------------ //
public DataTable GetFirstRowWithSchemaInfo(string tablename, string database)
{
DataTable dt = new DataTable();
try
{
if (_model.ConnType == ConnectionTypes.MySQL)
{
string query = $"SELECT * FROM {database}.{tablename} limit 1";
MySqlConnection mySqlConnection = new MySqlConnection(_model.ConnString);
MySqlCommand mySqlCommand = new MySqlCommand(query, mySqlConnection);
mySqlConnection.Open();
MySqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader(CommandBehavior.KeyInfo);
dt = mySqlDataReader.GetSchemaTable();
mySqlConnection.Close();
}
else if (_model.ConnType == ConnectionTypes.SQL_CE || _model.ConnType == ConnectionTypes.SQL_Server)
{
string query = $"USE {database}; SELECT TOP 1 * FROM {tablename}; ";
SqlConnection sqlConnection = new SqlConnection(_model.ConnString);
SqlCommand SqlCommand = new SqlCommand(query, sqlConnection);
sqlConnection.Open();
SqlDataReader SqlDataAdapter = SqlCommand.ExecuteReader(CommandBehavior.KeyInfo);
dt = SqlDataAdapter.GetSchemaTable();
sqlConnection.Close();
}
else if (_model.ConnType == ConnectionTypes.PostgreSQL)
{
string query = $"SELECT * FROM {database}.{tablename} limit 1";
NpgsqlConnection npgSqlConnection = new NpgsqlConnection(_model.ConnString);
NpgsqlCommand npgSqlCommand = new NpgsqlCommand(query, npgSqlConnection);
npgSqlConnection.Open();
NpgsqlDataReader SqlDataAdapter = npgSqlCommand.ExecuteReader(CommandBehavior.KeyInfo);
dt = SqlDataAdapter.GetSchemaTable();
npgSqlConnection.Close();
}
}
catch (Exception ex)
{
Errors.Add(ex);
}
dt.TableName = tablename;
return(dt);
}
public AuthenticationData Get(UUID principalID)
{
AuthenticationData ret = new AuthenticationData();
ret.Data = new Dictionary <string, object>();
string sql = string.Format("select * from {0} where uuid = :principalID", m_Realm);
using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString))
using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn))
{
cmd.Parameters.Add(m_database.CreateParameter("principalID", principalID));
conn.Open();
using (NpgsqlDataReader result = cmd.ExecuteReader())
{
if (result.Read())
{
ret.PrincipalID = principalID;
if (m_ColumnNames == null)
{
m_ColumnNames = new List <string>();
DataTable schemaTable = result.GetSchemaTable();
foreach (DataRow row in schemaTable.Rows)
{
m_ColumnNames.Add(row["ColumnName"].ToString());
}
}
foreach (string s in m_ColumnNames)
{
if (s == "UUID" || s == "uuid")
{
continue;
}
ret.Data[s] = result[s].ToString();
}
return(ret);
}
}
}
return(null);
}
static public void Main()
{
// TODO: change to your real values
string connString = "Server=127.0.0.1; Port=9999; Database=LOCALDB; User Id=sa; Password="******"Established connection to PG2LucidDB");
// grab non existing values:
NpgsqlCommand comm = conn.CreateCommand();
comm.CommandText = "select * from PG2LUCIDDBTEST.LOCATION";
NpgsqlDataReader dr = comm.ExecuteReader(CommandBehavior.SchemaOnly);
// get schema:
DataTable dt = dr.GetSchemaTable();
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
Console.WriteLine(dt.Columns[j].ColumnName + " = " + dt.Rows[i][j]);
}
Console.WriteLine("");
}
dr.Close();
}
catch (Exception ex)
{
Console.WriteLine("Exception occured: {0}", ex);
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
private DataTable ExecuteQuery(string query)
{
DataTable dt = new DataTable();
try
{
if (_model.ConnType == ConnectionTypes.MySQL)
{
MySqlConnection mySqlConnection = new MySqlConnection(_model.ConnString);
MySqlCommand mySqlCommand = new MySqlCommand(query, mySqlConnection);
mySqlConnection.Open();
MySqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader(CommandBehavior.KeyInfo);
dt = mySqlDataReader.GetSchemaTable();
mySqlConnection.Close();
}
else if (_model.ConnType == ConnectionTypes.SQL_CE || _model.ConnType == ConnectionTypes.SQL_Server)
{
SqlConnection sqlConnection = new SqlConnection(_model.ConnString);
SqlCommand SqlCommand = new SqlCommand(query, sqlConnection);
sqlConnection.Open();
SqlDataReader SqlDataAdapter = SqlCommand.ExecuteReader(CommandBehavior.KeyInfo);
dt = SqlDataAdapter.GetSchemaTable();
sqlConnection.Close();
}
else if (_model.ConnType == ConnectionTypes.PostgreSQL)
{
NpgsqlConnection npgSqlConnection = new NpgsqlConnection(_model.ConnString);
NpgsqlCommand npgSqlCommand = new NpgsqlCommand(query, npgSqlConnection);
npgSqlConnection.Open();
NpgsqlDataReader SqlDataAdapter = npgSqlCommand.ExecuteReader(CommandBehavior.KeyInfo);
dt = SqlDataAdapter.GetSchemaTable();
npgSqlConnection.Close();
}
}
catch (Exception ex)
{
Errors.Add(ex);
}
dt.TableName = "QueryToEntity";
return(dt);
}
private void CheckColumnNames(NpgsqlDataReader reader)
{
if (m_ColumnNames != null)
{
return;
}
m_ColumnNames = new List <string>();
DataTable schemaTable = reader.GetSchemaTable();
foreach (DataRow row in schemaTable.Rows)
{
if (row["ColumnName"] != null &&
(!m_Fields.ContainsKey(row["ColumnName"].ToString())))
{
m_ColumnNames.Add(row["ColumnName"].ToString());
}
}
}
/// <summary>
/// 执行查询存储过程返回结果集
/// </summary>
/// <param name="TransID">连接字符串</param>
/// <param name="StrConn">连接字符串</param>
/// <param name="ProcedureName">存储过程名</param>
/// <param name="Params">Sql 参数，参数值所在位置必须与参数名一致</param>
/// <returns>返回数据集</returns>
public byte[] QueryProcedure(string TransID, string StrConn, string ProcedureName, Dictionary <string, object> Params)
{
NpgsqlConnection oConn = GetNpgsqlConnection(TransID, StrConn);
if (string.IsNullOrWhiteSpace(TransID))//没有事务需要设一单独事务
{
NpgsqlTransaction trans = oConn.BeginTransaction(IsolationLevel.ReadCommitted);
}
try
{
using (NpgsqlCommand oCmd = new NpgsqlCommand(ProcedureName, oConn))
{
oCmd.CommandType = CommandType.StoredProcedure;
CreateSqlProcedureParam(oCmd, Params);
using (NpgsqlDataReader odr = oCmd.ExecuteReader(CommandBehavior.CloseConnection))
{
using (DataTable dtSchema = odr.GetSchemaTable())
{
using (CJia.Net.Serialization.SerializationWriter sw = new Net.Serialization.SerializationWriter())
{
SerializationSchema(sw, dtSchema);
object[] aryValues = new object[odr.FieldCount];
while (odr.Read())
{
odr.GetValues(aryValues);
sw.WriteOptimized(aryValues);
}
aryValues = null;
return(sw.ToArray());
}
}
}
}
}
finally
{
CloseConnection(TransID, oConn);
}
}
/// <summary>
/// 执行查询存储过程返回结果集
/// </summary>
/// <param name="TransID">连接字符串</param>
/// <param name="StrConn">连接字符串</param>
/// <param name="ProcedureName">存储过程名</param>
/// <param name="Params">Sql 参数，参数值所在位置必须与参数名一致</param>
/// <returns>返回数据集</returns>
public DataTable QueryProcedureTable(string TransID, string StrConn, string ProcedureName, Dictionary <string, object> Params)
{
NpgsqlConnection oConn = GetNpgsqlConnection(TransID, StrConn);
if (string.IsNullOrWhiteSpace(TransID))//没有事务需要设一单独事务
{
NpgsqlTransaction trans = oConn.BeginTransaction(IsolationLevel.ReadCommitted);
}
try
{
using (NpgsqlCommand oCmd = new NpgsqlCommand(ProcedureName, oConn))
{
oCmd.CommandType = CommandType.StoredProcedure;
CreateSqlProcedureParam(oCmd, Params);
using (NpgsqlDataReader odr = oCmd.ExecuteReader(CommandBehavior.CloseConnection))
{
using (DataTable dtSchema = odr.GetSchemaTable())
{
DataTable dtResult = BuildDataTable(dtSchema);
object[] aryValues = new object[odr.FieldCount];
dtResult.BeginLoadData();
while (odr.Read())
{
odr.GetValues(aryValues);
dtResult.LoadDataRow(aryValues, true);
}
dtResult.EndLoadData();
aryValues = null;
return(dtResult);
}
}
}
}
finally
{
CloseConnection(TransID, oConn);
}
}
public static DataSet GetSchemaFromQuery(dbOrigen source, string sSQL, object[] aParam)
{
int i = 1;
DataSet oDs = new DataSet();
switch (source)
{
case dbOrigen.Ninguno:
FillExceptionDataSet(oDs, "No se ha especificado origen de datos.");
break;
case dbOrigen.ORA:
OracleConnection oraCn = new OracleConnection(GetORACnString());
try
{
oraCn.Open();
OracleCommand oraCmd = new OracleCommand(RemoveSemiColon(sSQL), oraCn);
if (aParam != null)
{
foreach (object param in aParam)
{
oraCmd.Parameters.Add(new OracleParameter(string.Format("param{0}", i.ToString()), param));
i += 1;
}
}
OracleDataReader oraReader = oraCmd.ExecuteReader(CommandBehavior.SchemaOnly);
oDs.Tables.Add(oraReader.GetSchemaTable());
oraCmd.Dispose();
oraCn.Close();
}
catch (OracleException e)
{
FillExceptionDataSet(oDs, e.ToString());
}
finally
{
if (oraCn.State != 0)
{
oraCn.Close();
}
}
break;
case dbOrigen.PGSQL:
NpgsqlConnection pgCn = new NpgsqlConnection(GetPGSQLCnString());
try
{
pgCn.Open();
NpgsqlCommand pgCmd = new NpgsqlCommand(sSQL, pgCn);
if (aParam != null)
{
foreach (object param in aParam)
{
pgCmd.Parameters.Add(new NpgsqlParameter(string.Format("param{0}", i.ToString()), param));
i += 1;
}
}
NpgsqlDataReader pgReader = pgCmd.ExecuteReader(CommandBehavior.SchemaOnly);
oDs.Tables.Add(pgReader.GetSchemaTable());
pgCmd.Dispose();
pgCn.Close();
}
catch (NpgsqlException e)
{
FillExceptionDataSet(oDs, e.ToString());
}
finally
{
if (pgCn.State != 0)
{
pgCn.Close();
}
}
break;
}
return(oDs);
}
public List <RegionData> RunCommand(NpgsqlCommand cmd)
{
List <RegionData> retList = new List <RegionData>();
NpgsqlDataReader result = cmd.ExecuteReader();
while (result.Read())
{
RegionData ret = new RegionData();
ret.Data = new Dictionary <string, object>();
UUID regionID;
UUID.TryParse(result["uuid"].ToString(), out regionID);
ret.RegionID = regionID;
UUID scope;
UUID.TryParse(result["ScopeID"].ToString(), out scope);
ret.ScopeID = scope;
ret.RegionName = result["regionName"].ToString();
ret.posX = Convert.ToInt32(result["locX"]);
ret.posY = Convert.ToInt32(result["locY"]);
ret.sizeX = Convert.ToInt32(result["sizeX"]);
ret.sizeY = Convert.ToInt32(result["sizeY"]);
if (m_ColumnNames == null)
{
m_ColumnNames = new List <string>();
DataTable schemaTable = result.GetSchemaTable();
foreach (DataRow row in schemaTable.Rows)
{
m_ColumnNames.Add(row["ColumnName"].ToString());
}
}
foreach (string s in m_ColumnNames)
{
if (s == "uuid")
{
continue;
}
if (s == "ScopeID")
{
continue;
}
if (s == "regionName")
{
continue;
}
if (s == "locX")
{
continue;
}
if (s == "locY")
{
continue;
}
ret.Data[s] = result[s].ToString();
}
retList.Add(ret);
}
return(retList);
}
public void GetPostGreSQLSchema()
{
try
{
//get connect to postgresql
//String connstring = String.Format("Data Source=localhost;User ID=vams3203; Password= 123456; Initial Catalog=NEWDB;Persist Security Info=True;");
string connstring = String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4};", "localhost", "5432", "vams3203", "123456", "NEWDB");
NpgsqlConnection conn = new NpgsqlConnection(connstring);
conn.Open();
string sql = "SELECT * FROM PRODUCTS where 1 = 0";
NpgsqlCommand cmd = new NpgsqlCommand(sql, conn);
NpgsqlDataReader reader = cmd.ExecuteReader();
DataTable schema = reader.GetSchemaTable();
//get schema of a table (example)
var sqlselect = new StringBuilder();
sqlselect.Append("CREATE TABLE ");
sqlselect.Append("PRODUCTS");
sqlselect.Append(" (");
int i = 0;
foreach (DataRow row in schema.Rows)
{
i = i + 1;
if (i != schema.Rows.Count)
{
sqlselect.Append(row["COLUMNNAME"].ToString() + " ");
sqlselect.Append(row["DATATYPE"].ToString() + ","); //pass the system datatype to a function and get sql datatype
}
else
{
sqlselect.Append(row["COLUMNNAME"].ToString() + " ");
sqlselect.Append(row["DATATYPE"].ToString() + ")"); //pass the system datatype to a function and get sql datatype
}
//string ColumnName = row["COLUMNNAME"].ToString();
//string DataType = row["DATATYPE"].ToString();
//string Lenghth = row["NumericPrecision"].ToString();
}
// Create table in destination sql database to hold file data
var connection = new SqlConnection(string.Format("Data Source={0};Initial Catalog={1};Integrated Security=TRUE;", "EBI-ETL-DEV-01", "LDAP"));
var command = new SqlCommand(sql.ToString(), connection);
// Create table in destination sql database to hold file data
connection.Open();
command.ExecuteNonQuery();
connection.Close();
//build sql schema equivalent to postgresql schema
//build the table in SQL Server destination database
}
catch (Exception ex)
{
}
}
/// <summary>
/// Returns a DataTable which contains metadata about the current row.
/// </summary>
/// <returns></returns>
public DataTable GetSchemaTable()
{
return(_rdr.GetSchemaTable());
}
public override DataTable GetSchemaTable()
{
return(source.GetSchemaTable());
}
/// <summary> /// Returns a DataTable which contains metadata about the current row. /// </summary> public DataTable GetSchemaTable() => _rdr.GetSchemaTable();
async Task <DataTable?> GetSchemaTable(NpgsqlDataReader dr) => IsAsync ? await dr.GetSchemaTableAsync() : dr.GetSchemaTable();
internal static async Task <List <OnScreenClick> > GetGridDataAsync(TeamHttpContext httpContext, OnScreenClick details)
{
//string connString = Utility.GetConnectionString("PgAdmin4ConnectionString");
string connString = string.Empty;
string userid = httpContext.ContextUserId;
//string userid = "Admin";
List <OnScreenClick> returnValue = new List <OnScreenClick>();
PostgresService postgresService = new PostgresService();
List <UserDashboard> objUserDashboard = new List <UserDashboard>();
string querylevel = string.Empty;
List <string[]> ResultSet = new List <string[]>();
MatchCollection allMatchResults = null;
Regex regex = new Regex(@"@\w*@");
using (TeamDbContext dbContext = new TeamDbContext())
{
//objUserDashboard = dbContext.UserDashboards.Where(x => x.DashboardUserId == userid && x.Deleted == false && x.Id == details.ClickedWidgetId).ToList(); // For specific user
objUserDashboard = dbContext.UserDashboards.Where(x => x.Deleted == false && x.Id == details.ClickedWidgetId).ToList(); // for all users
}
if (objUserDashboard.Count != 0)
{
foreach (var item in objUserDashboard)
{
switch (details.ClickLevel)
{
case "L1":
connString = Utility.GetConnectionString(item.Level1ConnectionString);
querylevel = item.DashbaordQueryL1;
allMatchResults = regex.Matches(querylevel);
if (allMatchResults.Count > 0)
{
querylevel = querylevel.Replace(allMatchResults[0].Value, details.ClickedOnValue);
}
break;
case "L2":
connString = Utility.GetConnectionString(item.Level2ConnectionString);
querylevel = item.DashbaordQueryL2;
break;
case "L3":
connString = Utility.GetConnectionString(item.Level3ConnectionString);
querylevel = item.DashbaordQueryL3;
break;
case "L4":
connString = Utility.GetConnectionString(item.Level4ConnectionString);
querylevel = item.DashbaordQueryL4;
break;
}
if (details.ClickLevel != "L1")
{
var dict = details.GridInput.ToDictionary(m => m.Name, m => m.Value);
allMatchResults = regex.Matches(querylevel);
if (allMatchResults.Count > 0)
{
foreach (var match in allMatchResults)
{
dict.TryGetValue(((System.Text.RegularExpressions.Capture)match).Value.Substring(1, ((System.Text.RegularExpressions.Capture)match).Value.Length - 2), out string result);
querylevel = querylevel.Replace(match.ToString(), result);
}
}
}
NpgsqlDataReader dr = postgresService.ExecuteSqlReturnReader(connString, querylevel);
DataTable dtSchema = dr.GetSchemaTable();
DataTable dt = new DataTable();
List <DataColumn> listCols = new List <DataColumn>();
if (dtSchema != null)
{
foreach (DataRow drow in dtSchema.Rows)
{
string columnName = Convert.ToString(drow["ColumnName"]);
DataColumn column = new DataColumn(columnName, (Type)(drow["DataType"]));
column.Unique = (bool)drow["IsUnique"];
column.AutoIncrement = (bool)drow["IsAutoIncrement"];
listCols.Add(column);
dt.Columns.Add(column);
}
}
// Read rows from DataReader and populate the DataTable
while (dr.Read())
{
DataRow dataRow = dt.NewRow();
for (int i = 0; i < listCols.Count; i++)
{
dataRow[((DataColumn)listCols[i])] = dr[i];
}
dt.Rows.Add(dataRow);
}
string[] columnNames = dt.Columns.Cast <DataColumn>().Select(x => x.ColumnName).ToArray();
ResultSet = dt.Select().Select(drr => drr.ItemArray.Select(x => x.ToString()).ToArray()).ToList();
OnScreenClick newItem = new OnScreenClick
{
ClickLevel = details.ClickLevel,
ClickedWidgetId = details.ClickedWidgetId,
ClickedOnValue = details.ClickedOnValue,
GridColumns = columnNames,
GridData = ResultSet,
GridInput = null
};
returnValue.Add(newItem);
}
}
return(returnValue);
}
public static async Task <ResponseBase <List <ReportGrid> > > ViewReport(TeamHttpContext teamHttpContext, int reportId)
{
try
{
//string connString = Utility.GetConnectionString("PgAdmin4ConnectionString");
string userid = teamHttpContext.ContextUserId;
//string userid = "Admin";
List <ReportGrid> returnValue = new List <ReportGrid>();
PostgresService postgresService = new PostgresService();
List <ReportConfig> objReportConfig = new List <ReportConfig>();
List <string[]> ResultSet = new List <string[]>();
using (TeamDbContext dbContext = new TeamDbContext())
{
objReportConfig = dbContext.ReportConfigs.Where(x => x.Id == reportId && x.Deleted == false && x.ReportReqUserId == userid).ToList();
}
if (objReportConfig.Count != 0)
{
foreach (var item in objReportConfig)
{
string connString = Utility.GetConnectionString(item.ReportConnectionString);
NpgsqlDataReader dr = postgresService.ExecuteSqlReturnReader(connString, item.ReportQuery);
DataTable dtSchema = dr.GetSchemaTable();
DataTable dt = new DataTable();
List <DataColumn> listCols = new List <DataColumn>();
if (dtSchema != null)
{
foreach (DataRow drow in dtSchema.Rows)
{
string columnName = Convert.ToString(drow["ColumnName"]);
DataColumn column = new DataColumn(columnName, (Type)(drow["DataType"]));
column.Unique = (bool)drow["IsUnique"];
column.AutoIncrement = (bool)drow["IsAutoIncrement"];
listCols.Add(column);
dt.Columns.Add(column);
}
}
// Read rows from DataReader and populate the DataTable
while (dr.Read())
{
DataRow dataRow = dt.NewRow();
for (int i = 0; i < listCols.Count; i++)
{
dataRow[((DataColumn)listCols[i])] = dr[i];
}
dt.Rows.Add(dataRow);
}
string[] columnNames = dt.Columns.Cast <DataColumn>().Select(x => x.ColumnName).ToArray();
ResultSet = dt.Select().Select(drr => drr.ItemArray.Select(x => x.ToString()).ToArray()).ToList();
ReportGrid newItem = new ReportGrid
{
ReportId = reportId,
ReportName = item.ReportName,
GridColumns = columnNames,
GridData = ResultSet,
IsActive = !(item.Deleted)
};
returnValue.Add(newItem);
}
}
return(GetTypedResponse(teamHttpContext, returnValue));
}
catch (Exception)
{
return(null);
}
}
