private unRetourRequete copieDonnees(ref OleDbDataReader 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 static DataTable ConvertDataReaderToDataTable(OleDbDataReader reader)
{
var objDataTable = new DataTable("TmpDataTable");
try
{
var intFieldCount = reader.FieldCount; //获取当前行中的列数；
for (var intCounter = 0; intCounter <= intFieldCount - 1; intCounter++)
{
objDataTable.Columns.Add(reader.GetName(intCounter), reader.GetFieldType(intCounter));
}
//populate datatable
objDataTable.BeginLoadData();
//object[] objValues = new object[intFieldCount -1];
var objValues = new object[intFieldCount];
while (reader.Read())
{
reader.GetValues(objValues);
objDataTable.LoadDataRow(objValues, true);
}
reader.Close();
objDataTable.EndLoadData();
return(objDataTable);
}
catch (Exception ex)
{
throw new Exception("转换出错出错!", ex);
}
}
public IEnumerable <Property> GetColumns(string workSheet)
{
using (OleDbConnection connection = _connectionBuilder.GetConnection(_fileConfiguration.FileName))
{
using (OleDbCommand command = connection.CreateCommand())
{
command.CommandText = String.Format("SELECT * FROM [{0}]", workSheet);
using (OleDbDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
// Empty .xls worksheet returns F1
if (reader.FieldCount == 1 && reader.GetName(0) == "F1")
{
yield break;
}
for (int i = 0; i < reader.FieldCount; i++)
{
yield return(new Property(reader.GetFieldType(i).ToString(),
reader.GetName(i)));
}
}
}
}
}
}
public static DataTable ReadDataTable(string Sql, string DataPath)
{
OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0 ;Data Source=" + DataPath);
conn.Open();
OleDbCommand comm = new OleDbCommand(Sql, conn);
OleDbDataReader dr = comm.ExecuteReader();
DataTable dt = new DataTable();
for (int i = 0; i < dr.FieldCount; i++)
{
DataColumn dc = new DataColumn(dr.GetName(i), dr.GetFieldType(i));
dt.Columns.Add(dc);
}
while (dr.Read())
{
DataRow ddr = dt.NewRow();
for (int i = 0; i < dr.FieldCount; i++)
{
ddr[i] = dr.GetValue(i).ToString();
}
dt.Rows.Add(ddr);
}
dr.Close();
conn.Close();
return(dt);
}
public DataTable ReadData(string Sql)
{
OleDbCommand comm = new OleDbCommand(Sql, conn);
OleDbDataReader dr = comm.ExecuteReader();
DataTable dt = new DataTable();
for (int i = 0; i < dr.FieldCount; i++)
{
DataColumn dc = new DataColumn(dr.GetName(i), dr.GetFieldType(i));
dt.Columns.Add(dc);
}
while (dr.Read())
{
DataRow ddr = dt.NewRow();
for (int i = 0; i < dr.FieldCount; i++)
{
ddr[i] = dr.GetValue(i).ToString();
}
dt.Rows.Add(ddr);
}
dr.Close();
return(dt);
}
public string GetTypeColumn(string connectionOleString, string tableName)
{
using (OleDbConnection connectionOle = new OleDbConnection(connectionOleString))
{
OleDbCommand commandOle = new OleDbCommand("Select * from " + tableName, connectionOle);
connectionOle.Open();
OleDbDataReader readerOle = commandOle.ExecuteReader();
string commandForCreatTable = "(id int identity(1,1) primary key,";
for (int c = 1; c < readerOle.VisibleFieldCount; c++)
{
System.Type type = readerOle.GetFieldType(c);
string temp = type.Name;
if (temp.Contains("Int"))
{
commandForCreatTable = commandForCreatTable + readerOle.GetName(c) + " int,";
}
/*
* if (temp.Contains("Boolean"))
* {
*
* }*/
if (temp.Contains("String"))
{
commandForCreatTable = commandForCreatTable + readerOle.GetName(c) + " nvarchar(50),";
}
}
commandForCreatTable = commandForCreatTable.Remove(commandForCreatTable.Length - 1, 1);
commandForCreatTable = commandForCreatTable + ")";
return(commandForCreatTable);
}
}
protected DataTable ReadQueryResult(OleDbCommand command)
{
DataTable table = new DataTable();
using (OleDbDataReader reader = command.ExecuteReader())
{
for (int i = 0; i < reader.FieldCount; i++)
{
table.Columns.Add(reader.GetName(i), reader.GetFieldType(i));
}
object[] values = new object[reader.FieldCount];
while (reader.Read())
{
reader.GetValues(values);
table.Rows.Add(values);
}
}
foreach (DataColumn col in table.Columns)
{
if (col.ColumnName.Contains(memberCaption))
{
col.ColumnName = col.ColumnName.Remove(col.ColumnName.Length - memberCaption.Length);
}
}
return(table);
}
/// <summary>
/// 执行存储过程返回DataTable
/// </summary>
/// <param name="StoredProcedureName">存储过程名</param>
/// <param name="ParametersNames">数组参数名</param>
/// <param name="ParametersValue">数组参数值</param>
/// <returns>DataTable</returns>
public DataTable ExecuteSPForDtl(string StoredProcedureName, string[] ParametersNames, object[] ParametersValue)
{
cmd.Parameters.Clear();
DataTable pDT = new DataTable();
cmd.CommandText = StoredProcedureName;
cmd.CommandType = CommandType.StoredProcedure;
for (int i = 0; i < ParametersNames.Length; i++)
{
cmd.Parameters.Add(ParametersNames[i], ParametersValue[i]);
}
OleDbDataReader SDR = cmd.ExecuteReader();
for (int i = 0; i < SDR.FieldCount; i++)
{
pDT.Columns.Add(SDR.GetName(i), SDR.GetFieldType(i));
}
while (SDR.Read())
{
DataRow pDr1 = pDT.NewRow();
for (int i = 0; i < pDT.Columns.Count; i++)
{
pDr1[i] = SDR.GetValue(i);
}
pDT.Rows.Add(pDr1);
}
SDR.Close();
return(pDT);
}
/// <summary>
/// Deserialize the object.
/// </summary>
/// <param name="type">The type being read.</param>
/// <param name="reader">The reader to use.</param>
/// <returns>Returns the deserialized object.</returns>
internal static object Deserialize(Type type, OleDbDataReader reader)
{
object data = Activator.CreateInstance(type);
foreach (PropertyInfo prop in type.GetProperties())
{
AccessColumnAttribute attr = prop.GetCustomAttribute <AccessColumnAttribute>();
if (attr != null)
{
int ordinal = reader.GetOrdinal(attr.Name);
object value = null;
if (!reader.IsDBNull(ordinal))
{
Type fieldType = reader.GetFieldType(ordinal);
if (fieldType == typeof(string))
{
value = reader.GetString(ordinal);
}
else if (fieldType == typeof(short))
{
value = reader.GetInt16(ordinal);
}
else if (fieldType == typeof(int))
{
value = reader.GetInt32(ordinal);
}
else if (fieldType == typeof(byte))
{
value = reader.GetByte(ordinal);
}
else if (fieldType == typeof(DateTime))
{
value = reader.GetDateTime(ordinal);
}
else if (fieldType == typeof(double))
{
value = reader.GetDouble(ordinal);
}
else if (fieldType == typeof(decimal))
{
value = reader.GetDecimal(ordinal);
}
else if (fieldType == typeof(bool))
{
value = reader.GetBoolean(ordinal);
}
else
{
Debugger.Break();
}
}
prop.SetValue(data, value);
}
}
return(data);
}
private static List <object> FetchRow(string constr, string query)
{
OleDbConnection dbCon = new OleDbConnection(constr);
dbCon.Open();
OleDbCommand cmd = new OleDbCommand(query, dbCon);
OleDbDataReader lecteur = cmd.ExecuteReader();
List <object> ob = new List <object>();
while (lecteur.Read())
{
#region lecture des données
for (int i = 0; i < lecteur.VisibleFieldCount; i++)
{
if (lecteur.IsDBNull(i))
{
ob.Add(null);
}
else
{
Type t = lecteur.GetFieldType(i);
switch (t.Name)
{
case "Byte[]":
long len = lecteur.GetBytes(i, 0, null, 0, 0);
byte[] im = new byte[len];
lecteur.GetBytes(i, 0, im, 0, (int)len);
ob.Add(im);
break;
case "Int16":
ob.Add(lecteur.GetInt16(i));
break;
case "Int32":
ob.Add(lecteur.GetInt32(i));
break;
case "Decimal":
ob.Add(lecteur.GetDecimal(i));
break;
case "String":
ob.Add(lecteur.GetString(i));
break;
default:
ob.Add(null);
break;
}
}
}
#endregion
}
return(ob);
}
public Type GetFieldType(int i)
{
if (SDR != null)
{
return(SDR.GetFieldType(i));
}
else
{
return(ODR.GetFieldType(i));
}
}
public void run()
{
Exception exp = null;
try
{
BeginCase("check type string");
Compare(rdr.GetFieldType(1).FullName, typeof(string).FullName);
}
catch (Exception ex) { exp = ex; }
finally{ EndCase(exp); exp = null; }
try
{
BeginCase("check type date");
Compare(rdr.GetFieldType(2).FullName, typeof(DateTime).FullName);
}
catch (Exception ex) { exp = ex; }
finally{ EndCase(exp); exp = null; }
}
//
// 将OleDbDataReader中的数据转换为List
// 向更高层的程序屏蔽底层的OleDb类，并且使用后关闭reader释放资源
public List <Dictionary <string, object> > GetRecord(string select)
{
// 首先使用reader从数据库获取数据
OleDbDataReader reader = this.conn.GetRecord(select);
try
{
// 定义结果集
var result = new List <Dictionary <string, object> >();
while (reader.Read())
{
// reader的当前记录保存在Dictionary<string, object>中
var record = new Dictionary <string, object>();
for (int i = 0; i < reader.FieldCount; i++)
{
string name = reader.GetName(i);
object value;
if (reader.IsDBNull(i))
{
// 对于数据库空值的情况，用DBValueNull()专门给一个默认值
value = DefaultValue4DBNull.DefaultValue(reader.GetFieldType(i));
}
else
{
value = reader[i];
}
record.Add(name, value);
}
// 当前记录全部保存完毕，添加到结果集中
result.Add(record);
}
// 全部记录保存完毕，返回结果集
return(result);
}
catch (OleDbException ex)
{
string errorMessage = "从数据库SELECT数据集发生异常\n"
+ "发生错误的SQL语句：" + select;
throw new Exception(errorMessage, ex);
}
finally
{
// 最后关闭reader
reader.Close();
reader.Dispose();
}
}
private void ReadHeader(List <Model.Field> fields, OleDbDataReader reader)
{
for (int i = 0; i < reader.FieldCount; i++)
{
fields.Add(new Model.Field {
ColumnIndex = i,
FieldName = reader.GetName(i),
IsNullable = false,
FieldType = TranslatedFieldType(reader.GetFieldType(i).ToString())
});
}
}
/// <summary>
/// Fill
/// </summary>
/// <param name="ds"></param>
public void Fill(DataSet ds)
{
if (ds == null)
{
ds = new DataSet();
}
using (OleDbDataReader dr = command.ExecuteReader())
{
do
{
var dt = new DataTable();
var columns = dt.Columns;
var rows = dt.Rows;
for (int i = 0; i < dr.FieldCount; i++)
{
string name = dr.GetName(i).Trim();
if (!columns.Contains(name))
{
columns.Add(new DataColumn(name, dr.GetFieldType(i)));
}
else
{
columns.Add(new DataColumn(name + i, dr.GetFieldType(i)));
}
}
while (dr.Read())
{
DataRow daRow = dt.NewRow();
for (int i = 0; i < columns.Count; i++)
{
daRow[columns[i].ColumnName] = dr.GetValue(i);
}
dt.Rows.Add(daRow);
}
dt.AcceptChanges();
ds.Tables.Add(dt);
} while (dr.NextResult());
}
}
protected static DbType GetCategoryColumnType(OleDbDataReader reader)
{
DbType dbType;
var categoryColumnType = reader.GetFieldType(2);
if (categoryColumnType == typeof (int))
dbType = DbType.Int32;
else if (categoryColumnType == typeof (double))
dbType = DbType.Double;
else
throw new Exception("Category column has invalid type '" + categoryColumnType.Name + "'");
return dbType;
}
/// <summary>
/// Test basic SQL statements execution
/// </summary>
private static void Test_Demo_Basic()
{
using (OleDbConnection conn = new OleDbConnection())
{
conn.ConnectionString = TestCasesOld.connString;
conn.Open();
(new OleDbCommand("drop table if exists t", conn)).ExecuteNonQuery();
(new OleDbCommand("create table t(id int, str string)", conn)).ExecuteNonQuery();
(new OleDbCommand("insert into t values(1, 'abc')", conn)).ExecuteNonQuery();
using (OleDbCommand cmd = new OleDbCommand("select * from t", conn))
{
using (OleDbDataReader reader = cmd.ExecuteReader())
{
reader.Read();
Debug.Assert(reader.GetName(0).ToLower() == "id");
Debug.Assert(reader.GetName(1).ToLower() == "str");
Debug.Assert(reader.GetDataTypeName(0) == "DBTYPE_I4");
Debug.Assert(reader.GetDataTypeName(1) == "DBTYPE_LONGVARCHAR");
Debug.Assert(reader.GetFieldType(0).UnderlyingSystemType.Name == "Int32");
Debug.Assert(reader.GetFieldType(1).UnderlyingSystemType.Name == "String");
Debug.Assert(reader.GetValue(0).ToString() == "1");
Debug.Assert(reader.GetString(1) == "abc");
Debug.Assert(reader.GetInt32(0) == 1);
Debug.Assert(reader.GetString(1) == "abc");
}
}
(new OleDbCommand("drop table if exists t", conn)).ExecuteNonQuery();
}
}
private void Fetch(string sql)
{
OleDbDataReader reader = null;
try {
var db = new DataBlock();
db.OpenDB(BewebData.GetConnectionString());
var conn = new OleDbConnection(db.connString);
conn.Open();
var exec = new OleDbCommand(sql, conn);
var sw = Stopwatch.StartNew();
sw.Start();
reader = exec.ExecuteReader();
sw.Stop();
var fields = new List <Dictionary <string, string> >();
var data = new List <List <string> >();
if (reader.FieldCount > 0)
{
for (int i = 0; i < reader.FieldCount; i++)
{
var f = new Dictionary <string, string>();
f["name"] = reader.GetName(i);
f["type"] = reader.GetFieldType(i).ToString().Replace("System.", "");
fields.Add(f);
}
while (reader.Read())
{
var row = fields.Select(field => reader[field["name"]].ToString()).ToList();
data.Add(row);
}
}
var jsonSerialiser = new JavaScriptSerializer();
Web.Write("{\"success\": true, \"type\":\"query\", \"time\": " + sw.ElapsedMilliseconds + ", \"rows\": " + data.Count + ", \"fields\": " + jsonSerialiser.Serialize(fields) + ", \"data\": " + jsonSerialiser.Serialize(data) + "}");
} catch (Exception ex) {
Web.Write("{\"success\": false, \"error\":\"" + ex.Message + "\"}");
} finally {
if (reader != null && !reader.IsClosed)
{
reader.Close();
}
}
}
public DataTable ExecuteTable(string sql)
{
DataTable datatable = new DataTable();
OleDbConnection myConnection = new OleDbConnection(connectionString);
OleDbCommand myORACCommand = myConnection.CreateCommand();
myORACCommand.CommandText = sql;
myConnection.Open();
OleDbDataReader dataReader = myORACCommand.ExecuteReader();
try
{ ///动态添加表的数据列
for (int i = 0; i < dataReader.FieldCount; i++)
{
DataColumn myDataColumn = new DataColumn();
myDataColumn.DataType = dataReader.GetFieldType(i);
myDataColumn.ColumnName = dataReader.GetName(i);
datatable.Columns.Add(myDataColumn);
}
///添加表的数据
while (dataReader.Read())
{
DataRow myDataRow = datatable.NewRow();
for (int i = 0; i < dataReader.FieldCount; i++)
{
myDataRow[i] = dataReader[i];
}
datatable.Rows.Add(myDataRow);
myDataRow = null;
}
///关闭数据读取器
dataReader.Close();
myConnection.Close();
datatable.TableName = "table";
return(datatable);
}
catch (Exception ex)
{
///抛出类型转换错误
//SystemError.CreateErrorLog(ex.Message);
dataReader.Close();
myConnection.Close();
throw new Exception(ex.Message, ex);
}
}
/// <summary>
/// 查询字段属性
/// </summary>
/// <param name="TableName">表名</param>
/// <param name="Field">字段名</param>
/// <param name="FieldType">字段类型</param>
/// <returns>true 字段存在，false 字段不存在</returns>
public bool CheckField(string TableName, string Field, out System.Type FieldType, out bool CanBeNull, out bool IsPrimaryKey)
{
FieldType = typeof(object);
CanBeNull = false;
IsPrimaryKey = false;
if (string.IsNullOrEmpty(TableName))
{
throw new NullReferenceException("TableName Is Null Or Empty");
}
else if (string.IsNullOrEmpty(Field))
{
throw new NullReferenceException("Field Is Null Or Empty");
}
try
{
OleDbConnection conn = (OleDbConnection)GetConnection();
bool res = false;
DataTable dt = conn.GetSchema("Columns", new string[] { null, null, TableName });
int m = dt.Columns.IndexOf("COLUMN_NAME");
int n = dt.Columns.IndexOf("ISNULLABLE");
int o = dt.Columns.IndexOf("COLUMN_KEY");
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow dr = dt.Rows[i];
if (dr.ItemArray.GetValue(m).ToString().ToUpper() == Field.ToUpper())
{
CanBeNull = dr.ItemArray.GetValue(n).ToString().ToUpper().Contains("YES");
IsPrimaryKey = dr.ItemArray.GetValue(o).ToString().ToUpper().Contains("PRI");
res = true;
break;
}
}
if (res)
{
OleDbCommand cmd = new OleDbCommand("select " + Field + " from " + TableName + "", conn);
cmd.CommandTimeout = this.Timeout * 1000;
OleDbDataReader da = cmd.ExecuteReader();
FieldType = da.GetFieldType(0);
}
CloseConnection(conn);
return(res);
}
catch
{
return(false);
}
}
protected DataTable ReadQueryResult(OleDbCommand command)
{
DataTable table = new DataTable();
using (OleDbDataReader reader = command.ExecuteReader()) {
for (int i = 0; i < reader.FieldCount; i++)
{
table.Columns.Add(reader.GetName(i), reader.GetFieldType(i));
}
object[] values = new object[reader.FieldCount];
while (reader.Read())
{
reader.GetValues(values);
table.Rows.Add(values);
}
}
return(table);
}
private void populateItemDetail(string itemName, string date)
{
string sqlQry = "SELECT tDate, Pcode as Item, Tno as TagNo, MetalType, Pcs, GW,NW FROM TRANS WHERE PCODE = '" + itemName + "' AND (OUTDATE IS NULL OR OUTDATE >= #" + date + "#) AND TDATE <= #" + date + "#";
OleDbDataReader reader = dbUtils.fetch(sqlQry);
int columnCount = reader.FieldCount;
/* dgItemDetail.Columns.Clear();
* for (int i = 0; i < columnCount; i++)
* {
* dgItemDetail.Columns.Add(reader.GetName(i).ToString(), reader.GetName(i).ToString());
* }*/
dgItemDetail.Rows.Clear();
string[] rowData = new string[columnCount];
while (reader.Read())
{
for (int k = 0; k < columnCount; k++)
{
if (reader.GetFieldType(k).ToString() == "System.Int16")
{
rowData[k] = reader.GetInt16(k).ToString();
}
if (reader.GetFieldType(k).ToString() == "System.Int32")
{
rowData[k] = reader.GetInt32(k).ToString();
}
if (reader.GetFieldType(k).ToString() == "System.Int64")
{
rowData[k] = reader.GetInt64(k).ToString();
}
if (reader.GetFieldType(k).ToString() == "System.String")
{
rowData[k] = reader.GetString(k);
}
if (reader.GetFieldType(k).ToString() == "System.Double")
{
rowData[k] = dbUtils.Decimal3digit(reader.GetDouble(k).ToString());
}
if (reader.GetFieldType(k).ToString() == "System.DateTime")
{
rowData[k] = reader.GetDateTime(k).ToShortDateString();
}
}
dgItemDetail.Rows.Add(rowData);
}
reader.Close();
reader = null;
}
public static DataTable GetAccess(string sql)
{
OleDbCommand command = new OleDbCommand();
command.Connection = conn;
OleDbDataReader dr = null;
command.CommandText = sql;
DataTable dt = new DataTable();
try
{
dr = command.ExecuteReader();
for (int i = 0; i < dr.FieldCount; i++)
{
DataColumn dc = new DataColumn(dr.GetName(i), dr.GetFieldType(i));
dt.Columns.Add(dc);
}
while (dr.Read())
{
DataRow ddr = dt.NewRow();
for (int i = 0; i < dr.FieldCount; i++)
{
ddr[i] = dr.GetValue(i).ToString();
}
dt.Rows.Add(ddr);
}
dr.Dispose();
return(dt);
}
catch (Exception ex)
{
CodeAll.AddLog("数据库操作失败", sql, ex.Message);
return(null);
}
}
public String ReadDataS(string Sql)
{
OleDbCommand comm = new OleDbCommand(Sql, conn);
OleDbDataReader dr = comm.ExecuteReader();
DataTable dt = new DataTable();
for (int i = 0; i < dr.FieldCount; i++)
{
DataColumn dc = new DataColumn(dr.GetName(i), dr.GetFieldType(i));
dt.Columns.Add(dc);
}
dr.Read();
string Vaule = dr.GetValue(0).ToString();
dr.Close();
return(Vaule);
}
public void GetExcelCostVariantData(AspxCommonInfo aspxCommonObj, string fileName)
{
try
{
if (fileName != "")
{
string xlPath = HttpContext.Current.Server.MapPath("~/" + fileName); //@"F:\optionvariant 022113.xls"; //location of xlsx file
string constr1 = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + xlPath +
";Extended Properties=\"Excel 12.0 Xml; HDR=YES; IMEX=1;\"";
OleDbConnection con1 = new OleDbConnection(constr1);
OleDbCommand cmd1 = new OleDbCommand("select * from [Sheet2$]", con1);
con1.Open();
OleDbDataReader dreader1 = cmd1.ExecuteReader();
while (dreader1.Read())
{
var costVariantOptions = new List <CostVariantsOptions>();
for (int col = 0; col < dreader1.FieldCount; col++)
{
var columnName = dreader1.GetName(col).ToString();
var columnFieldType = dreader1.GetFieldType(col).ToString(); // Gets the column type
var columnDbType = dreader1.GetDataTypeName(col).ToString(); // Gets the column database type
var columnValue = dreader1.GetValue(col).ToString();
costVariantOptions.Add(new CostVariantsOptions()
{
ColumnName = columnName,
ColumnValue = columnValue
});
if (dreader1.FieldCount == col + 1)
{
SaveCostVariantOptionsFromExcel(costVariantOptions, aspxCommonObj);
}
}
}
dreader1.Close();
}
}
catch (Exception ex)
{
throw ex;
}
}
public List <string> GetExcelConnection(string fileName)
{
try
{
var xlsHeader = new List <string>();
if (fileName != "")
{
string xlPath = HttpContext.Current.Server.MapPath("~/" + fileName); //fileName.Replace("/", @"\"); //@"F:\AKBook.xls"; //location of xlsx file
string constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + xlPath +
";Extended Properties=\"Excel 12.0 Xml; HDR=YES; IMEX=1;\"";
OleDbConnection con = new OleDbConnection(constr);
OleDbCommand cmd = new OleDbCommand("select * from [Sheet1$]", con);
con.Open();
OleDbDataReader dreader = cmd.ExecuteReader();
while (dreader.Read())
{
for (int col = 0; col < dreader.FieldCount; col++)
{
var columnName = dreader.GetName(col).ToString();
var columnFieldType = dreader.GetFieldType(col).ToString(); // Gets the column type
var columnDbType = dreader.GetDataTypeName(col).ToString(); // Gets the column database type
var columnValue = dreader.GetValue(col).ToString();
if (!xlsHeader.Contains(columnName))
{
xlsHeader.Add(columnName);
}
}
}
dreader.Close();
return(xlsHeader);
}
else
{
return(xlsHeader);
}
}
catch (Exception ex)
{
throw ex;
}
}
public void StoreColumnTypes(OleDbDataReader Reader)
{
ColumnDataTypes = new DataType[Reader.FieldCount];
for (int i = 0; i < ColumnDataTypes.Length; i++)
{
Type T = Reader.GetFieldType(i);
if (T == Type.GetType("System.String"))
{
ColumnDataTypes[i] = DataType.TypeString;
}
else if (T == Type.GetType("System.DateTime"))
{
ColumnDataTypes[i] = DataType.TypeDate;
}
else
{
ColumnDataTypes[i] = DataType.TypeInt;
}
}
}
public static int Main(string[] args)
{
int
Result = -1;
StreamWriter
fstr_out = null;
string
tmpString = "log.log";
OleDbConnection
conn = null;
OleDbCommand
cmd = null;
OleDbDataReader
rdr = null;
try
{
try
{
fstr_out = new StreamWriter(tmpString, false, System.Text.Encoding.GetEncoding(1251));
fstr_out.AutoFlush = true;
//tmpString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\Мои документы\\Jsic Veksel\\T4.mdb;Jet OLEDB:Database Password=3944";
tmpString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\\Soft.src\\CBuilder\\Tests\\Access\\TestAccess.mdb;User Id=admin;Password=;";
conn = new OleDbConnection(tmpString);
conn.Open();
fstr_out.WriteLine("ConnectionString: " + conn.ConnectionString);
fstr_out.WriteLine("ConnectionTimeout: " + conn.ConnectionTimeout.ToString());
fstr_out.WriteLine("Database: " + conn.Database);
fstr_out.WriteLine("DataSource: " + conn.DataSource);
fstr_out.WriteLine("Provider: " + conn.Provider);
fstr_out.WriteLine("ServerVersion: " + conn.ServerVersion);
fstr_out.WriteLine("State: " + conn.State.ToString());
fstr_out.WriteLine();
if (cmd == null)
{
cmd = conn.CreateCommand();
}
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select sum(Mark) as SumSum from Examinations where Id=9";
fstr_out.WriteLine("OleDbCommand.ExecuteReader(CommandBehavior.SchemaOnly)");
rdr = cmd.ExecuteReader(CommandBehavior.SchemaOnly);
fstr_out.WriteLine("OleDbDataReader.HasRows=" + rdr.HasRows.ToString());
for (int i = 0; i < rdr.FieldCount; ++i)
{
fstr_out.WriteLine(rdr.GetName(i) + " " + rdr.GetDataTypeName(i) + " " + rdr.GetFieldType(i));
}
fstr_out.WriteLine();
rdr.Close();
rdr = cmd.ExecuteReader();
rdr.Close();
cmd.CommandText = "select ?=sum(Mark) from Examinations where Id=9";
cmd.Parameters.Add("SumSum", OleDbType.Double);
cmd.Parameters["SumSum"].Direction = ParameterDirection.Output;
rdr = cmd.ExecuteReader();
rdr.Close();
conn.Close();
Result = 0;
}
catch (Exception eException)
{
fstr_out.WriteLine(eException.GetType().FullName + Environment.NewLine + "Message: " + eException.Message + Environment.NewLine + "StackTrace:" + Environment.NewLine + eException.StackTrace);
}
}
finally
{
if (rdr != null && !rdr.IsClosed)
{
rdr.Close();
}
if (conn != null && conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
}
if (fstr_out != null)
{
fstr_out.Close();
}
}
return(Result);
}
public void GetExcelData(List <FinalImportDataInfo> finalInfo, AspxCommonInfo aspxCommonObj, string fileName)
{
try
{
if (fileName != "")
{
string xlPath = HttpContext.Current.Server.MapPath("~/" + fileName); //@"F:\AKBook.xls"; //location of xlsx file
string constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + xlPath +
";Extended Properties=\"Excel 12.0 Xml; HDR=YES; IMEX=1;\"";
OleDbConnection con = new OleDbConnection(constr);
OleDbCommand cmd = new OleDbCommand("select * from [Sheet1$]", con);
con.Open();
OleDbDataReader dreader = cmd.ExecuteReader();
while (dreader.Read())
{
var currencyCode = "";
var itemValues = new List <ItemsValues>();
for (int col = 0; col < dreader.FieldCount; col++)
{
var columnName = dreader.GetName(col).ToString();
var columnFieldType = dreader.GetFieldType(col).ToString(); // Gets the column type
var columnDbType = dreader.GetDataTypeName(col).ToString(); // Gets the column database type
var columnValue = dreader.GetValue(col).ToString();
var id = GetAttrubuteId(finalInfo, columnName);
var inputId = GetInputTypeId(finalInfo, columnName);
var validId = GetValidationTypeId(finalInfo, columnName);
if (id > 0)
{
itemValues.Add(new ItemsValues()
{
AttributeId = id,
InputTypeID = inputId,
ValidationTypeID = validId,
AttributeValue = columnValue
});
}
if (columnName == "Category")
{
itemValues.Add(new ItemsValues()
{
AttributeId = 0,
InputTypeID = 0,
ValidationTypeID = 0,
AttributeValue = columnValue
});
}
if (columnName == "CurrencyCode")
{
currencyCode = columnValue;
}
if (dreader.FieldCount == col + 1)
{
int attributeSetId = 2;
int itemId = SaveItemFromExcel(itemValues, false, 1, attributeSetId, currencyCode, aspxCommonObj);
OracleHandler sqlH = new OracleHandler();
sqlH.ExecuteNonQuery("usp_Aspx_TruncateCategoryForExcel");
SaveItemImage(itemId, itemValues, aspxCommonObj);
SaveItemAttributesFromExcel(itemId, attributeSetId, itemValues, aspxCommonObj);
CacheHelper.Clear("CategoryInfo" + aspxCommonObj.StoreID + aspxCommonObj.PortalID + "_" + aspxCommonObj.CultureName);
CacheHelper.Clear("CategoryForSearch" + aspxCommonObj.StoreID + aspxCommonObj.PortalID + "_" + aspxCommonObj.CultureName);
}
}
}
dreader.Close();
GetExcelCostVariantData(aspxCommonObj, fileName);
}
}
catch (Exception ex)
{
throw ex;
}
}
static int Main(string[] args)
{
int
Result = -1;
StreamWriter
fstr_out = null;
string
tmpString = "log.log",
TableName;
#if TEST_DB_BY_ODBC
OdbcConnection
odbc_conn = null;
OdbcCommand
odbc_cmd = null;
OdbcDataReader
odbc_rdr = null;
OdbcDataAdapter
odbc_da = null;
#endif
OleDbConnection
conn = null;
OleDbCommand
cmd = null;
OleDbDataReader
rdr = null;
OleDbDataAdapter
da = null;
DataTable
tmpDataTable;
int
tmpInt;
object[]
tmpObjects;
FileStream
fs;
byte[]
Blob;
try
{
try
{
fstr_out = new StreamWriter(tmpString, false, System.Text.Encoding.GetEncoding(1251));
fstr_out.AutoFlush = true;
string
PathToDb = "E:\\Soft.src\\CBuilder\\Tests\\Paradox\\Test.#1\\db",
CommonDbTableName = "Common",
CommonDbTableSQLCreate = @"
create table " + CommonDbTableName + @"(
FInt integer,
FChar char(254)
)";
#if TEST_DB_BY_ODBC
if (!PathToDb.EndsWith(Path.DirectorySeparatorChar.ToString()))
{
PathToDb += Path.DirectorySeparatorChar;
}
tmpString = "Driver={Microsoft Paradox Driver (*.db )};DriverID=538;Fil=Paradox 5.X;DefaultDir=" + PathToDb + ";Dbq=" + PathToDb + ";CollatingSequence=ASCII";
odbc_conn = new OdbcConnection(tmpString);
odbc_conn.Open();
fstr_out.WriteLine("ConnectionString: " + odbc_conn.ConnectionString);
fstr_out.WriteLine("ConnectionTimeout: " + odbc_conn.ConnectionTimeout.ToString());
fstr_out.WriteLine("Database: " + odbc_conn.Database);
fstr_out.WriteLine("DataSource: " + odbc_conn.DataSource);
fstr_out.WriteLine("Driver: " + odbc_conn.Driver);
fstr_out.WriteLine("ServerVersion: " + odbc_conn.ServerVersion);
fstr_out.WriteLine("State: " + odbc_conn.State.ToString());
fstr_out.WriteLine();
tmpString = PathToDb + CommonDbTableName + ".db";
if (File.Exists(tmpString))
{
File.Delete(tmpString);
}
odbc_cmd = odbc_conn.CreateCommand();
odbc_cmd.CommandType = CommandType.Text;
odbc_cmd.CommandText = CommonDbTableSQLCreate;
odbc_cmd.ExecuteNonQuery();
odbc_cmd.CommandText = "insert into " + CommonDbTableName + " values (1,'FChar (‘„ар)')";
odbc_cmd.ExecuteNonQuery();
#if TEST_BLOB
if (odbc_cmd == null)
{
odbc_cmd = odbc_conn.CreateCommand();
}
odbc_cmd.CommandType = CommandType.Text;
odbc_cmd.CommandText = "select * from TestTypes";
odbc_cmd.Parameters.Clear();
odbc_rdr = odbc_cmd.ExecuteReader();
do
{
if (odbc_rdr.HasRows)
{
for (int i = 0; i < odbc_rdr.FieldCount; ++i)
{
fstr_out.WriteLine(odbc_rdr.GetName(i) + " GetDataTypeName(): \"" + odbc_rdr.GetDataTypeName(i) + "\" GetFieldType(): \"" + odbc_rdr.GetFieldType(i) + "\"");
}
tmpInt = odbc_rdr.GetOrdinal("FGraphic");
while (odbc_rdr.Read())
{
tmpString = "FromBlob.bmp";
if (File.Exists(tmpString))
{
File.Delete(tmpString);
}
Blob = (byte[])odbc_rdr["FGraphic"];
fs = new FileStream(tmpString, FileMode.Create);
fs.Write(Blob, 0, Blob.Length);
fs.Close();
tmpString = "FromBlob_1.bmp";
if (File.Exists(tmpString))
{
File.Delete(tmpString);
}
Blob = new byte[odbc_rdr.GetBytes(tmpInt, 0, null, 0, int.MaxValue)];
rdr.GetBytes(tmpInt, 0, Blob, 0, Blob.Length);
fs = new FileStream(tmpString, FileMode.Create);
fs.Write(Blob, 0, Blob.Length);
fs.Close();
}
}
}while(rdr.NextResult());
odbc_rdr.Close();
#endif
odbc_conn.Close();
#endif
if (PathToDb.EndsWith(Path.DirectorySeparatorChar.ToString()))
{
PathToDb = PathToDb.Remove(PathToDb.Length - 1, 1);
}
tmpString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + PathToDb + ";Extended Properties=Paradox 5.x";
conn = new OleDbConnection(tmpString);
conn.Open();
fstr_out.WriteLine("ConnectionString: " + conn.ConnectionString);
fstr_out.WriteLine("ConnectionTimeout: " + conn.ConnectionTimeout.ToString());
fstr_out.WriteLine("Database: " + conn.Database);
fstr_out.WriteLine("DataSource: " + conn.DataSource);
fstr_out.WriteLine("Provider: " + conn.Provider);
fstr_out.WriteLine("ServerVersion: " + conn.ServerVersion);
fstr_out.WriteLine("State: " + conn.State.ToString());
fstr_out.WriteLine();
tmpString = PathToDb + Path.DirectorySeparatorChar + CommonDbTableName + ".db";
if (File.Exists(tmpString))
{
File.Delete(tmpString);
}
cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = CommonDbTableSQLCreate;
cmd.ExecuteNonQuery();
cmd.CommandText = "insert into " + CommonDbTableName + " values (1,'FChar (‘„ар)')";
cmd.ExecuteNonQuery();
#if TEST_BLOB
if (cmd == null)
{
cmd = conn.CreateCommand();
}
cmd.CommandType = CommandType.Text;
TableName = "TestTypes";
tmpObjects = new object[] { null, null, TableName, null };
fstr_out.WriteLine("OleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Columns)");
tmpDataTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, tmpObjects);
fstr_out.WriteLine("Columns in " + TableName + " table:");
foreach (DataRow row in tmpDataTable.Rows)
{
fstr_out.WriteLine("\t" + row["TABLE_CATALOG"] + " " + row["TABLE_NAME"] + " " + row["COLUMN_NAME"].ToString() + " " + row["DATA_TYPE"] + " " + row["TABLE_SCHEMA"]);
}
fstr_out.WriteLine();
#if TEST_BLOB_SAVE
cmd.CommandText = "update TestTypes set FGraphic = ?";
cmd.Parameters.Clear();
cmd.Parameters.Add("FGraphic", OleDbType.LongVarBinary);
fs = new FileStream("welcome.bmp", FileMode.Open, FileAccess.Read);
Blob = new byte[fs.Length];
fs.Read(Blob, 0, Blob.Length);
cmd.Parameters["FGraphic"].Value = Blob;
tmpInt = cmd.ExecuteNonQuery();
#endif
cmd.CommandText = "select * from TestTypes";
cmd.Parameters.Clear();
rdr = cmd.ExecuteReader();
do
{
if (rdr.HasRows)
{
for (int i = 0; i < rdr.FieldCount; ++i)
{
fstr_out.WriteLine(rdr.GetName(i) + " GetDataTypeName(): \"" + rdr.GetDataTypeName(i) + "\" GetFieldType(): \"" + rdr.GetFieldType(i) + "\"");
}
tmpInt = rdr.GetOrdinal("FGraphic");
while (rdr.Read())
{
tmpString = "FromBlob.bmp";
if (File.Exists(tmpString))
{
File.Delete(tmpString);
}
Blob = (byte[])rdr["FGraphic"];
fs = new FileStream(tmpString, FileMode.Create);
fs.Write(Blob, 0, Blob.Length);
fs.Close();
tmpString = "FromBlob_1.bmp";
if (File.Exists(tmpString))
{
File.Delete(tmpString);
}
Blob = new byte[rdr.GetBytes(tmpInt, 0, null, 0, int.MaxValue)];
rdr.GetBytes(tmpInt, 0, Blob, 0, Blob.Length);
fs = new FileStream(tmpString, FileMode.Create);
fs.Write(Blob, 0, Blob.Length);
fs.Close();
}
}
}while(rdr.NextResult());
rdr.Close();
#endif
Result = 0;
}
catch (Exception eException)
{
Console.WriteLine(eException.GetType().FullName + Environment.NewLine + "Message: " + eException.Message + Environment.NewLine + "StackTrace:" + Environment.NewLine + eException.StackTrace);
}
}
finally
{
#if TEST_DB_BY_ODBC
if (odbc_rdr != null && !odbc_rdr.IsClosed)
{
odbc_rdr.Close();
}
if (odbc_conn != null && odbc_conn.State == System.Data.ConnectionState.Open)
{
odbc_conn.Close();
}
#endif
if (rdr != null && !rdr.IsClosed)
{
rdr.Close();
}
if (conn != null && conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
}
if (fstr_out != null)
{
fstr_out.Close();
}
}
return(Result);
}
