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