private void ViewTable_Load(object sender, EventArgs e)
{
using (OdbcCommand cmd = _odbcConnection.CreateCommand())
{
cmd.CommandText = "SELECT * FROM " + _selectedTable;
using (OdbcDataReader reader = cmd.ExecuteReader())
{
int fieldCount = reader.FieldCount;
for (int i = 0; i < fieldCount; i++)
{
dataGrid.Columns.Add(reader.GetName(i), reader.GetName(i));
}
MessageBox.Show("Does this column has rows: " + reader.HasRows);
while (reader.Read())
{
DataGridViewRow newRow = new DataGridViewRow();
for (int i = 0; i < fieldCount; i++)
{
DataGridViewCell newCell = new DataGridViewTextBoxCell();
newCell.Value = reader.GetValue(i);
newCell.ValueType = reader.GetFieldType(i);
newRow.Cells.Add(newCell);
newCell.ReadOnly = true;
}
dataGrid.Rows.Add(newRow);
}
}
}
}
/// <summary>
/// 查询数据记录
/// </summary>
/// <param name="sql">查询SQL</param>
/// <returns>返回第一行查询数据</returns>
public Dictionary <string, object> Find(string sql)
{
try
{
Dictionary <string, object> res = new Dictionary <string, object>();
OdbcConnection conn = (OdbcConnection)GetConnection();
OdbcCommand cmd = new OdbcCommand(sql, conn);
cmd.CommandTimeout = this.Timeout * 1000;
OdbcDataReader da = cmd.ExecuteReader();
if (da.Read())
{
if (da.FieldCount > 0)
{
for (int i = 0; i < da.FieldCount; i++)
{
if (da.IsDBNull(i))
{
res.Add(da.GetName(i), null);
}
else
{
res.Add(da.GetName(i), da[i]);
}
}
}
}
da.Close();
CloseConnection(conn);
return(res);
}
catch (Exception ex)
{
throw ex;
}
}
private void frmReportView_Load(object sender, EventArgs e)
{
string databasePath = AppDomain.CurrentDomain.BaseDirectory + "\\EquipmentDatabase.accdb";
con = new OdbcConnection("Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=" + databasePath + ";Uid=Admin;PWD=;");
cmd = con.CreateCommand();
cmd.CommandText = queryString;
con.Open();
reader = cmd.ExecuteReader();
if (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
dgvData.Columns.Add(reader.GetName(i), reader.GetName(i));
}
do
{
object[] rowData = new object[reader.FieldCount];
reader.GetValues(rowData);
dgvData.Rows.Add(rowData);
} while((reader.Read()));
}
else
{
MessageBox.Show("There was no data to retireve");
}
con.Close();
}
public string GetAsString(string Row)
{
try
{
ExError = new Exception();
switch (ConType)
{
case 1:
{
if (!Odbcdr.IsClosed)
{
for (int i = 0; i < Odbcdr.FieldCount; i++)
{
if (Odbcdr.GetName(i).ToUpper() == Row.ToUpper())
{
return(Odbcdr[i].ToString());
}
}
}
} break;
case 2:
{
if (!OleDbdr.IsClosed)
{
for (int i = 0; i < OleDbdr.FieldCount; i++)
{
if (OleDbdr.GetName(i).ToUpper() == Row.ToUpper())
{
return(OleDbdr[i].ToString());
}
}
}
} break;
case 3:
{
if (!Sqldr.IsClosed)
{
for (int i = 0; i < Sqldr.FieldCount; i++)
{
if (Sqldr.GetName(i).ToUpper() == Row.ToUpper())
{
return(Sqldr[i].ToString());
}
}
}
} break;
}
return(null);
}
catch (Exception x)
{
ExError = x;
LogWindow.SqlLog.LogAdd(ExError.Message);
return(null);
}
}
public void ProcessRequest(HttpContext context)
{
context.Response.ContentType = "text/csv";
context.Response.AddHeader("Content-Disposition",
"filename=export.csv;attachment");
int idWS = Int32.Parse(context.Request.Params["id"]);
OdbcCommand cmd = new OdbcCommand();
cmd.Connection = HELPERS.NewOdbcConn();
cmd.CommandText =
"SELECT ROLE.c_u_Name as RoleName, " +
"VEC.c_u_StandardActivity as StandardActivity, " +
"VEC.c_u_RoleType as RoleType, " +
"VEC.c_u_Application as Application, " +
"VEC.c_u_System as System, " +
"VEC.c_u_Platform as Platform, " +
"VEC.c_u_EntitlementName as EntitlementName, " +
"VEC.c_u_EntitlementValue as EntitlementValue, " +
"VEC.c_u_AuthObjValue as AuthObjValue, " +
"VEC.c_u_FieldSecName as FieldSecName, " +
"VEC.c_u_FieldSecValue as FieldSecValue, " +
"VEC.c_u_Commentary as Commentary " +
// "VEC.c_u_EditStatus as EditStatus " +
"FROM t_r_BusRoleWorkspaceEntitlement LINK " +
"LEFT OUTER JOIN t_RBSR_AUFW_u_BusRole ROLE ON LINK.c_r_BusRole = ROLE.c_id " +
"LEFT OUTER JOIN t_RBSR_AUFW_u_WorkspaceEntitlement VEC ON LINK.c_r_WorkspaceEntitlement = VEC.c_id " +
"WHERE LINK.c_r_EditingWorkspace = ?;";
cmd.Parameters.Add("c_r_EditingWorkspace", OdbcType.Int);
cmd.Parameters["c_r_EditingWorkspace"].Value = (object)idWS;
OdbcDataReader dr = cmd.ExecuteReader();
int indexChecksum = -32;
for (int i = 0; i < dr.VisibleFieldCount; i++)
{
context.Response.Write(CSVquoteize(dr.GetName(i)) + ",");
if (dr.GetName(i) == "c_u_CHECKSUM")
{
indexChecksum = i;
}
}
context.Response.Write("\n");
while (dr.Read())
{
for (int i = 0; i < dr.VisibleFieldCount; i++)
{
if (i != indexChecksum)
{
context.Response.Write(CSVquoteize(dr.GetValue(i) as string) + ",");
}
}
context.Response.Write("\n");
}
dr.Close();
}
public Dictionary <string, Dynamic> Fields(bool upper = true)
{
var res = new Dictionary <string, Dynamic>();
for (int c = 0; c < FieldCount; c++)
{
var col = upper ? DataReader.GetName(c).ToUpper() : DataReader.GetName(c);
res.Add(col, new Dynamic(DataReader.GetValue(c), DataReader.GetName(c)));
}
return(res);
}
private void Form11_Load(object sender, EventArgs e)
{
con = new OdbcConnection("Dsn=ExcelDsn");
cmd = new OdbcCommand("Select *from [Student$]", con);
con.Open();
dr = cmd.ExecuteReader();
label2.Text = dr.GetName(0);
label3.Text = dr.GetName(1);
label4.Text = dr.GetName(2);
label5.Text = dr.GetName(3);
ShowData();
}
private static void PreencheGrid(OdbcDataReader reader, DataGridView dgv)
{
dgv.Rows.Clear();
dgv.Columns.Clear();
bool fazer = true;
int colBool = -1;
while (reader.Read())
{
#region - Nomes Colunas -
if (fazer)
{
for (int i = 0; i < reader.FieldCount; i++)
{
if (reader.GetDataTypeName(i).Contains("bool"))
{
DataGridViewCheckBoxColumn colch = new DataGridViewCheckBoxColumn();
colch.Name = reader.GetName(i);
colch.HeaderText = reader.GetName(i);
colch.ReadOnly = true;
dgv.Columns.Add(colch);
colBool = i;
}
else
{
dgv.Columns.Add(reader.GetName(i), reader.GetName(i));
dgv.Columns[i].ReadOnly = true;
}
}
fazer = false;
}
#endregion
#region - Valores Colunas -
object[] linha = new object[reader.FieldCount];
for (int i = 0; i < reader.FieldCount; i++)
{
/*reader.GetFieldType(i).ToString()*/
//MessageBox.Show("[" + reader.GetName(i) + "]: " + reader[i].ToString() + " (" + reader.GetDataTypeName(i) + ")");
linha[i] = (colBool == i) ? ((reader[i].ToString().Equals("0")) ? false : true) : reader[i];
}
dgv.Rows.Add(linha);
#endregion
}
dgv.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells);
}
private void execSql()
{
//h ttp://stackoverflow.com/questions/13257401/c-sharp-prepared-statement-and-multiple-queries
string myConnectionString = "DSN=" + odbcConnectionName + ";UID=" + userIdTextBox.Text + ";PWD=" + passwordTextBox.Text + ";";
OdbcConnection myConnection = new OdbcConnection();
myConnection.ConnectionString = myConnectionString;
myConnection.Open();
OdbcCommand odbcCommand = myConnection.CreateCommand();
odbcCommand.CommandText = "select count(*) from testc.vpfact_fad";
object obj = odbcCommand.ExecuteScalar();
odbcCommand.Parameters.Clear();
//odbcCommand.Parameters.Add("?ID7", OdbcType.VarChar).Value = parameter7;
//queryStatement1() returns the query string insert into TABLE (field1, field2...) values(?,?,...)
odbcCommand.CommandText = "select * from testc.vpfact_fad fetch first 3 rows only";
OdbcDataReader odbcDataReader = odbcCommand.ExecuteReader();
textBox1.AppendText("RowCount a: " + dataGridView1.RowCount + "\n");
//dataGridView1.ColumnCount = odbcDataReader.FieldCount;
for (int i = 0; i < odbcDataReader.FieldCount; ++i)
{
dataGridView1.Columns.Add(odbcDataReader.GetName(i), odbcDataReader.GetName(i));
}
textBox1.AppendText("RowCount b: " + dataGridView1.RowCount + "\n");
for (int i = 0; i < odbcDataReader.FieldCount; ++i)
{
textBox1.AppendText("[" + i + "] " + odbcDataReader.GetName(i) + " ");
}
//h ttps://msdn.microsoft.com/en-us/library/system.windows.forms.datagridview.columns%28v=vs.110%29.aspx
while (odbcDataReader.Read())
{
int row = dataGridView1.Rows.Add();
for (int i = 0; i < odbcDataReader.FieldCount; ++i)
{
dataGridView1.Rows[row].Cells[i].Value = odbcDataReader.GetString(i);
}
}
textBox1.AppendText("RowCount c: " + dataGridView1.RowCount + "\n");
myConnection.Close();
}
public static bool CreateForm(OdbcDataReader reader, Branch branch)
{
int fCount = reader.FieldCount;
for (int i = 0; i < fCount; i++)
{
string name = reader.GetName(i);
// Map to DB field. Need to change if db changed
switch (name)
{
case "branch_id": branch._branchID = reader.GetInt32(i);
break;
case "branch_name": branch._branchName = reader.GetString(i);
break;
case "branch_code": branch._branchCode = reader.GetString(i);
break;
case "address": branch._address = reader.GetString(i);
break;
case "tel": branch._tel = reader.GetString(i);
break;
case "img": branch._img = reader.GetString(i);
break;
case "supervisor": branch._supervisor = reader.GetString(i);
break;
}
}
return(reader.HasRows);
}
public string udpate_table(string tbname, params string[] values)
{
string _sql = "select * from " + tbname;
OdbcCommand cmd = new OdbcCommand(_sql, _myconnecttion);
OdbcDataReader MyReader = cmd.ExecuteReader();
string[] field_name = new string[MyReader.FieldCount];
for (int j = 0; j < MyReader.FieldCount; j++)
{
field_name[j] = MyReader.GetName(j);
}
string ss = "update ";
ss += tbname;
ss += " set ";
for (int i = 0; i < values.Length; i++)
{
ss += field_name[i] + "='" + values[i] + "'";
if (i < values.Length - 1)
{
ss += ",";
}
}
return(ss);
}
public static bool CreateForm(OdbcDataReader reader, PaidGroup paidGroup)
{
int fCount = reader.FieldCount;
for (int i = 0; i < fCount; i++)
{
string name = reader.GetName(i);
// Map to DB field. Need to change if db changed
switch (name)
{
case "paid_group_id": paidGroup._paidGroupID = reader.GetInt32(i);
break;
case "name": paidGroup._name = reader.GetString(i);
break;
case "current_round": paidGroup._currentRound = reader.GetInt32(i);
break;
case "rate_info": paidGroup._rawRateInfo = reader.GetString(i);
paidGroup._rateInfo = PaidRateInfo.Parse(paidGroup._rawRateInfo);
break;
// helper info
}
}
return(reader.HasRows);
}
// This loads only promotion info not course list
public static bool CreateForm(OdbcDataReader reader, Promotion promotion)
{
int fCount = reader.FieldCount;
for (int i = 0; i < fCount; i++)
{
string name = reader.GetName(i);
// Map to DB field. Need to change if db changed
switch (name)
{
case "promotion_id": promotion._promotionID = reader.GetInt32(i);
break;
case "promotion_name": promotion._promotionName = reader.GetString(i);
break;
case "promotion_desc": promotion._promotionDesc = reader.GetString(i);
break;
case "cost": promotion._cost = reader.GetInt32(i);
promotion._discountedCost = promotion._cost;
break;
case "is_active": promotion._isActive = reader.GetInt32(i) > 0 ? true : false;
break;
}
}
return(reader.HasRows);
}
private unRetourRequete copieDonnees(ref OdbcDataReader source)
{
unRetourRequete retour = new unRetourRequete();
try
{
retour.Tables.Add("Resultat1");
for (int i = 0; i <= source.FieldCount - 1; i++)
{
retour.Tables[0].Columns.Add(source.GetName(i), source.GetFieldType(i));
}
while (source.Read())
{
retour.Tables[0].Rows.Add();
for (int numColonne = 0; numColonne <= source.FieldCount - 1; numColonne++)
{
retour.Tables[0].Rows[retour.Tables[0].Rows.Count - 1][numColonne] = source[numColonne];
}
}
return(retour);
}
catch (Exception ex)
{
_lastError = ex.Message;
}
finally
{
try
{
source.Close();
}
catch {}
}
return(null);
}
public List <Dictionary <string, object> > ExecuteSelectCommandWithTransaction(OdbcCommand command, OdbcTransaction transaction)
{
List <Dictionary <string, object> > tablaResult = new List <Dictionary <string, object> >();
try
{
if (command.Transaction == null)
{
command.Transaction = transaction;
}
OdbcDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Dictionary <string, object> rowResult = new Dictionary <string, object>();
for (int i = 0; i < reader.FieldCount; i++)
{
rowResult.Add(reader.GetName(i), reader.GetValue(i));
}
tablaResult.Add(rowResult);
}
}
catch (MyOdbcException e)
{
throw new MyOdbcException("Error Informix OdbcDao: " + e.ToString());
}
return(tablaResult);
}
internal Dictionary <string, object> GetData()
{
Dictionary <string, object> dict = new Dictionary <string, object>();
using (OdbcConnection connection = new OdbcConnection(ConnectionString))
{
connection.Open();
using (OdbcCommand com = new OdbcCommand("SELECT * FROM {0}".FormatString(TableName), connection))
{
using (OdbcDataReader reader = com.ExecuteReader())
{
while (reader.Read())
{
//Load column names
for (int index = 0; index < reader.FieldCount; index++)
{
dict.Add(reader.GetName(index), reader[index] == DBNull.Value ? null : reader[index]);
}
return(dict);
}
}
}
}
return(dict);
}
public void Main()
{
using (OdbcConnection connection = new OdbcConnection("DSN=SycamoreWestDS"))
{
string sqlQuery = "SELECT * FROM emails limit 100";
OdbcCommand command = new OdbcCommand(sqlQuery, connection);
connection.Open();
OdbcDataReader reader = command.ExecuteReader();
//Print Column Names
for (int i = 0; i < reader.FieldCount; i++)
{
Console.Write(reader.GetName(i) + "\t");
}
Console.Write("\n");
if (reader.HasRows)
{
while (reader.Read())
{
Console.WriteLine("{0}\t{1}\t{2}", reader.GetInt32(0), reader.GetString(1), reader.GetString(2));
}
}
reader.Close();
command.Dispose();
}
}
protected List <KeyValueCollection> GetEntityPage(OdbcDataReader reader, int offset, int limit)
{
List <KeyValueCollection> entitys = new List <KeyValueCollection>();
// 读取列
List <string> columns = new List <string>();
for (int i = 0; i < reader.FieldCount; i++)
{
columns.Add(reader.GetName(i));
}
// 反射创建实体类
int currentIndex = 0;
while (reader.Read())
{
if (offset > currentIndex++)
{
continue;
}
if (offset + limit == currentIndex)
{
break;
}
KeyValueCollection entity = new KeyValueCollection();
foreach (var col in columns)
{
entity[col] = reader[col];
}
entitys.Add(entity);
}
return(entitys);
}
public void DoGetDbData(string tableName)
{
using (OdbcConnection connection = new OdbcConnection(_dsnName))
{
connection.Open();
using (OdbcCommand command = connection.CreateCommand())
{
command.CommandText = "SELECT * FROM " + tableName;
using (OdbcDataReader DbReader = command.ExecuteReader())
{
int colCount = DbReader.FieldCount;
Console.WriteLine("Table:\t" + tableName + Environment.NewLine);
string columnName = "";
while (DbReader.Read())
{
for (int i = 0; i < colCount; i++)
{
try
{
columnName = DbReader.GetName(i);
var columnValue = DbReader.GetString(i);
Console.WriteLine(columnName + ":\t" + columnValue);
}
catch (Exception ex)
{
Console.WriteLine(columnName + "->Exception->" + ex.Message);
}
}
Console.WriteLine();
} // while(DbReader.Read())
} // using ( OdbcDataReader DbReader = command.ExecuteReader() )
} // using (OdbcCommand command = connection.CreateCommand())
} // using (OdbcConnection connection = new OdbcConnection
}
private IDictionary <string, object> DataToExpando(OdbcDataReader reader)
{
int i, ix = 0;
IDictionary <string, object> eo = new Dictionary <string, object>();
Regex rx = new Regex(@"expr(\d+)", RegexOptions.Compiled | RegexOptions.IgnoreCase);
Group g;
Match m;
string key;
for (i = 0; i < reader.FieldCount; i++)
{
key = reader.GetName(i).ToLower();
m = rx.Match(key);
if (m.Success)
{
g = m.Groups[1];
ix = Math.Max(ix, Convert.ToInt32(g.Value));
}
if (eo.ContainsKey(key))
{
key = "expr" + ++ix;
}
eo.Add(key, reader[i]);
}
return(eo);
}
static void Main(string[] args)
{
String errorMsg;
OdbcConnection con = Connect("sa", "sa.pwd", "2055", "192.168.190.200", "mydb", out errorMsg);
Console.WriteLine(errorMsg);
if (con != null)
{
Console.WriteLine("In database {0} ", con.Database);
OdbcCommand command = con.CreateCommand();
command.CommandText = "SELECT name FROM sysobjects WHERE type='U' ORDER BY name";
OdbcDataReader reader = command.ExecuteReader();
foreach (String fName in reader.GetName())
{
Console.Write(fName + ":");
}
Console.WriteLine();
while (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
String col = reader.GetValue(i).ToString();
Console.Write(col + ":");
}
Console.WriteLine();
}
reader.Close();
command.Dispose();
Close(con);
Console.WriteLine("Press any key too continue...");
Console.ReadLine();
}
}
/*Insert A Single Recordset into a table*/
public bool Insert(String szTable, String[] szData)
{
ReaderClose();
String szSQL = String.Concat("SELECT TOP 1 * FROM ", szTable);
int iRet = 0;
try{
m_myReader = PrepareAndExecute(szSQL);
String[] szFields = new String[m_myReader.FieldCount - 1];
szSQL = "";
szSQL = "INSERT INTO " + szTable + " ( ";
szFields[0] = m_myReader.GetName(1); //Primary Key
szSQL += szFields[0];
for (int i = 1; i < szFields.Length; i++)
{
szSQL += ", ";
szFields[i] = m_myReader.GetName(i + 1);
szSQL += szFields[i];
}
ReaderClose();
szSQL += ") VALUES ('";
for (int q = 0; q < szData.Length; q++)
{
szData[q] = szData[q].Replace("'", "`");
}
szSQL += szData[0];
for (int i = 1; i < szData.Length; i++)
{
szSQL += "', '" + szData[i];
}
szSQL += "')";
m_myCommand = new OdbcCommand(szSQL);
m_myCommand.Connection = m_myDbConn;
iRet = m_myCommand.ExecuteNonQuery();
}
catch (Exception e) {
new genericDialogs().ShowErrorBox(e.ToString());
}
return((iRet >= 1)?true:false);
}
public void getQueryResultSource(DataGridView src, String OdbcQuery, String tableName = null)
{
_cmd = new OdbcCommand();
_cmd.Connection = _conn;
_cmd.CommandType = CommandType.Text;
_query = OdbcQuery;// "SELECT * FROM [TestTable]";
_cmd.CommandText = _query;
//_conn.Open();
OdbcDataReader reader = _cmd.ExecuteReader(CommandBehavior.CloseConnection);
//List<List<String>> src = new List<List<string>>();
//List<String> list;
//DataGridViewRow row;
while (reader.Read())
{
//row = new DataGridViewRow();
//src.AddNew();
List <Object> objList = new List <object>();
//list = new List<string>();
if (src.Columns.Count == 0)
{
for (int i = 0; i < reader.FieldCount; i++)
{
src.Columns.Add(reader.GetName(i), reader.GetName(i));
}
}
for (int i = 0; i < reader.FieldCount; i++)
{
//list.Add(reader.GetString(i));
objList.Add(reader.GetString(i));
//src.Insert(i, reader.GetString(i));
//obj.Add(reader.GetName(i), reader.GetString(i));
}
//row.CreateCells(src, objList.ToArray());
src.Rows.Add(objList.ToArray());
//src.Add(list);
//_src.Add(obj.Values);
}
reader.Close();
//return src;
}
private void buttonAbfrage_Click(object sender, EventArgs e)
{
listBox1.Items.Clear();// vorhandenen Einträge in der listbox entfernen.
string sqlcommand = textBox1.Text.Trim();
odbcommand = new OdbcCommand(sqlcommand, odbcconnection); //initialisierung des command-objekts.
odbcconnection.Open(); //connection Öffnen
if (sqlcommand.ToUpper().StartsWith("SELECT"))
{
try//zu viele Ausnahmen!!!!!!!!
{
this.odbcreader = odbcommand.ExecuteReader();
int spalten = odbcreader.FieldCount;//fieldcount Eigenschaft:Infors ,wie viel Spalten mit Daten bei Ausführung der SQL-Anweisung zurückgeben.
string spaltenname = "";
for (int i = 0; i < spalten; i++)
{
spaltenname += odbcreader.GetName(i) + "\t";//Getname Methode liefert die Spaltenüberschriften.
}
listBox1.Items.Add(spaltenname);
listBox1.Items.Add("");
while (odbcreader.Read())//read-methode liefert true ,solange noch weitere Zeilen vorhanden sind.anderenfalls false.
{
string zeile = "";
for (int j = 0; j < spalten; j++)
{
zeile += odbcreader.GetValue(j).ToString() + "\t";
}
listBox1.Items.Add(zeile);
}
}
catch (Exception ex)
{
listBox1.Items.Add(ex.GetType() + ":" + ex.Message);//Ausnahmetyp und die von der Ausnahme generierte Meldung.
}
finally
{
this.odbcconnection.Close();
}
}
else
{
try
{
int rows = odbcommand.ExecuteNonQuery();
listBox1.Items.Add(rows + "betroffene Datensätze");
}
catch (Exception exc)
{
listBox1.Items.Add(exc.GetType() + exc.Message);
}
finally
{
this.odbcconnection.Close();
}
}
}
public List <Dictionary <string, object> > IOExecProc(DbCommand commands)
{
List <Dictionary <string, object> > lDict = new List <Dictionary <string, object> >();
try
{
//using (_connection)
//{
OpenConnection();
using (commands)
{
SetParameters(commands);
OdbcDataReader reader = _command.ExecuteReader();
while (reader.Read())
{
Dictionary <string, object> line = new Dictionary <string, object>();
for (int x = 0; x < reader.FieldCount; x++)
{
if (!reader.IsDBNull(x))
{
line.Add(reader.GetName(x), reader.GetValue(x).ToString());
}
else
{
line.Add(reader.GetName(x), 0);
}
}
lDict.Add(line);
}
reader.Close();
}
//}
}
catch (Exception ex)
{
CloseConnection();
throw ex;
}
_connection.Close();
return(lDict);
}
/// <summary>
/// 反射实体类信息
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="reader"></param>
/// <returns></returns>
protected List <T> GetEntityCollection <T>(OdbcDataReader reader) where T : class, new()
{
PropertyInfo[] properties = typeof(T).GetProperties();
if (properties == null || properties.Length <= 0)
{
throw new ArgumentNullException("实体类{0}不包含任何属性信息！".ToFormat(typeof(T).Name));
}
// 读取列
Dictionary <string, string> columns = new Dictionary <string, string>(StringComparer.OrdinalIgnoreCase);
for (int i = 0; i < reader.FieldCount; i++)
{
string cname = reader.GetName(i);
columns[cname] = cname;
}
// 反射创建实体类
List <T> entitys = new List <T>();
while (reader.Read())
{
T entity = typeof(T).Create <T>();
foreach (PropertyInfo property in properties)
{
string p_name = property.Name;
object[] attributes = property.GetCustomAttributes(typeof(Field), true);
object[] proc_attributes = property.GetCustomAttributes(typeof(Proc), true);
// 存在自定义Field实例处理方法
if (attributes != null && attributes.Length > 0)
{
Field dbfield = attributes[0] as Field;
// 检测自增变量信息
p_name = dbfield.Name.IsNullOrEmpty() ? p_name : dbfield.Name;
}
else if (proc_attributes != null && proc_attributes.Length > 0)
{
Proc dbfield = proc_attributes[0] as Proc;
// 检测自增变量信息
p_name = dbfield.Name.IsNullOrEmpty() ? p_name : dbfield.Name;
}
// 不存在时处理方法
else
{
}
// 读取数据，并赋值
if (columns.ContainsKey(p_name))
{
property.SetValue(entity, reader[columns[p_name]].ChangeTo(property.PropertyType), null);
}
}
entitys.Add(entity);
}
return(entitys);
}
public DataTable runSqlQuery(string query)
{
DataTable dtTemp = new DataTable();
DataRow dr;
//String parameter;
int i;
try
{
connect();
odbcCmd = new OdbcCommand(query, conn);
conn.Open();
}
catch (Exception ex)
{
logger.SaveLog(ex.Message, logPrefix);
logger.SaveLog(query, logPrefix);
return(dtTemp);
}
OdbcDataReader myReader = odbcCmd.ExecuteReader();
//creating columns
i = 0;
while (i < myReader.FieldCount)
{
dtTemp.Columns.Add(myReader.GetName(i));
i++;
}
//getting rows
while (myReader.Read())
{
dr = dtTemp.NewRow();
i = 0;
while (i < myReader.FieldCount)
{
if (Convert.IsDBNull(myReader.GetValue(i)) == true)
{
dr.SetField(i, 0);
}
else
{
dr.SetField(i, myReader.GetValue(i));
}
i++;
}
dtTemp.Rows.Add(dr);
}
// converting time from INT to DateTime & formating time
disconnect();
return(dtTemp);
}
private string readSQLHeadline(OdbcDataReader dr, int drlenght, string csv)
{
for (int i = 0; i < drlenght; i++)
{
csv += dr.GetName(i) + seperator;
}
csv = csv.Remove(csv.Length - 1); //Cut last seperator
csv += "\r\n"; //Add newline
return(csv);
}
/// <summary>
/// Método Genérico que retorna una lista de Objetos de la entidad solicitada
/// </summary>
/// <typeparam name="T">Entidad solicitada</typeparam>
/// <param name="filtro">List filtro</param>
/// <returns>List object</returns>
public List <object> Leer <T>(List <FiltroGenerico> filtro)
{
ObjetoTransformar obj = ProcesarEntidad(typeof(T));
List <object> lista = new List <object>();
var props = typeof(T).GetProperties();
string conString = WebConfigurationManager.
ConnectionStrings[Utiles.CNS].ConnectionString;
//acá construimos la query de acuerdo al filtro
string query = EntregaQueryFiltro(obj.NombreTabla, filtro);
using (OdbcConnection con = new OdbcConnection(conString))
using (OdbcCommand cmd =
new OdbcCommand(query, con))
{
con.Open();
using (OdbcDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
object objClassInstance = GetInstance(obj.ObjetoType.FullName);
foreach (var prop in props)
{
for (int i = 0; i < reader.FieldCount; i++)
{
string nomCol = reader.GetName(i).ToUpper().Replace("_", "");
if (prop.Name.ToUpper() == nomCol)
{
var propertyInfo = typeof(T).GetProperty(prop.Name);
//propertyInfo.SetValue(new Saydex.Core.Entidad.Otro.Cliente(), Convert.ChangeType(reader.GetValue(0), propertyInfo.PropertyType), null);
//object objClassInstance = GetInstance(obj.ObjetoType.FullName);
if (reader.GetValue(i) == null)
{
if (propertyInfo.PropertyType.ToString().Contains("Date"))
{
}
}
propertyInfo.SetValue(objClassInstance, Convert.ChangeType(reader.GetValue(i), propertyInfo.PropertyType), null);
//lista.Add(objClassInstance);
}
}
//lista.Add(typeof(T));
}
lista.Add(objClassInstance);
}
}
con.Close();
}
return(lista);
}
public override void Execute()
{
Packet p = _cfgp.Receive();
string parms = p.Content as string;
Drop(p);
_cfgp.Close();
string[] parmArray = parms.Split(',');
_dsn = parmArray[0];
_query = parmArray[1];
_table = parmArray[2];
_timeout = Double.Parse(parmArray[3]);
OdbcConnection conn = new OdbcConnection(_dsn);
OdbcCommand cmd = new OdbcCommand(_query, conn);
OdbcDataReader rdr = null;
try
{
conn.Open();
}
catch (Exception e)
{
FlowError.Complain("cannot open connection");
}
try
{
rdr = cmd.ExecuteReader();
}
catch (Exception e)
{
FlowError.Complain("cannot execute query");
}
List <string> columns = new List <string>();
for (int i = 0; i < rdr.FieldCount; ++i)
{
columns.Add(rdr.GetName(i));
}
int serialno = 0;
while (ReadOdbcRow(rdr))
{
Record rec = new Record(_table, columns.ToArray());
for (int i = 0; i < rdr.FieldCount; ++i)
{
rec[i] = rdr[i];
}
p = Create(rec);
p.Attributes["SerialNo"] = ++serialno;
_outp.Send(p);
}
// _outp.Close( ); --not needed
}
