private static void GetRealDataTimer(object source,ElapsedEventArgs e) { try { OdbcDataAdapter adapter = new OdbcDataAdapter("select A_CV from FIX", RealConnection); DataSet ds = new DataSet(); adapter.Fill(ds, "FIX"); for (int i = 0; i < 100; i++) { data[i] = ds.Tables[0].Rows[i]["A_CV"].ToString(); } adapter.Dispose(); // RealConnection..Close(); } catch (Exception ex) { for (int i = 0; i < 100; i++) { data[i] = "???"; } } }
public DataSet GetRealData() { //string RealConnectionSting = "DSN=FIX Dynamics Real Time Data";//实时数据库连接字符串 ////string HisConnectionSting = "DSN=FIX Dynamics Historical Data";//历史数据库连接字符串 //OdbcConnection RealConnection;//实时数据库连接 ////OdbcConnection HisConnection;//历史数据库连接 //DataSet errds = new DataSet(); try { //RealConnection = new OdbcConnection(RealConnectionSting); //RealConnection.Open(); OdbcDataAdapter adapter = new OdbcDataAdapter("select A_CV from FIX", RealConnection); DataSet ds = new DataSet(); adapter.Fill(ds, "FIX"); adapter.Dispose(); return ds; // RealConnection..Close(); } catch (Exception ex) { return new DataSet(); } }
public static System.Data.DataSet ExecuteDatasetQueryOnDB(string sSQL, string sConnectionString) { System.Data.Odbc.OdbcDataAdapter QDataAdapter = null; DataSet QDataSet = null; try { QDataSet = new DataSet(); QDataAdapter = new System.Data.Odbc.OdbcDataAdapter(sSQL, sConnectionString); QDataAdapter.Fill(QDataSet); return(QDataSet); } finally { if (QDataSet != null) { QDataSet.Dispose(); } QDataSet = null; if (QDataAdapter != null) { QDataAdapter.Dispose(); } QDataAdapter = null; } }
public static string FetchTable(string SelectCommand, string XMLconnectionPhysicalFilePath, ref DataTable Dt) { OdbcConnection OdbcConn = new OdbcConnection(); OdbcCommand OdbcCmd = new OdbcCommand(); OdbcDataAdapter OdbcAdap = new OdbcDataAdapter(); try { string DbErr = InitializeODBCConnection(ref OdbcConn, XMLconnectionPhysicalFilePath); if (DbErr == string.Empty) { OdbcCmd.Connection = OdbcConn; OdbcCmd.CommandText = SelectCommand; OdbcAdap.SelectCommand = OdbcCmd; OdbcAdap.Fill(Dt); return string.Empty; } else return DbErr; } catch (Exception err) { return err.Message; } finally { OdbcAdap.Dispose(); OdbcCmd.Dispose(); OdbcConn.Close(); OdbcConn.Dispose(); } }
public static DataTable GetData(string strConn, string strSql, int timeout) { DataTable dt = new DataTable("td"); using (OdbcConnection conn = new OdbcConnection(strConn)) { conn.Open(); OdbcCommand cmd = null; OdbcDataAdapter da = null; try { cmd = new OdbcCommand(strSql, conn) { CommandTimeout = timeout }; da = new OdbcDataAdapter { SelectCommand = cmd }; da.Fill(dt); return dt; } catch (Exception ex) { throw new Exception("error getting data " + ex.Message); } finally { if (da != null) { da.Dispose(); } if (cmd != null) { cmd.Dispose(); } conn.Close(); } } }
public static string GetDataTable(ref DataTable _ConnectionDt, ref DataTable ReturnDt, string SelectCommand) { OdbcConnection odbc_Conn = new OdbcConnection(); OdbcDataAdapter odbcAdap = new OdbcDataAdapter(); OdbcCommand odbcCmd = new OdbcCommand(); string ErrMsg = string.Empty; try { ErrMsg = MakeInformixConnection(ref _ConnectionDt, ref odbc_Conn); if (ErrMsg == string.Empty) { odbcCmd.Connection = odbc_Conn; odbcCmd.CommandText = SelectCommand; odbcAdap.SelectCommand = odbcCmd; odbcAdap.Fill(ReturnDt); odbc_Conn.Close(); } return ErrMsg; } catch (Exception err) { return err.Message; } finally { odbcAdap.Dispose(); odbcCmd.Dispose(); odbc_Conn.Dispose(); } }
protected void Button1_Click(object sender, EventArgs e) { csvFolder = Server.MapPath("~/Files/"); csvFile = Path.GetFileName(FileUpload1.PostedFile.FileName); csvPath = csvFolder + csvFile; csvFilename = csvFolder + "QzBank.csv"; //csvPath = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName); if (File.Exists(csvFilename)) { File.Delete(csvFilename); } FileUpload1.SaveAs(csvPath); File.Move(csvPath, csvFilename); connString = @"Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + csvFolder + ";Extensions=csv,txt;"; //string connString = WebConfigurationManager.ConnectionStrings["CsvUTF8ConnectionString"].ConnectionString; using (OdbcConnection conn = new OdbcConnection(connString)) { string query = "SELECT * FROM QzBank.CSV"; using (OdbcDataAdapter da = new System.Data.Odbc.OdbcDataAdapter(query, conn)) { da.Fill(dt); GridView1.DataSource = dt; GridView1.DataBind(); sdt = dt; dt.Dispose(); da.Dispose(); } } connString1 = WebConfigurationManager.ConnectionStrings["QuizBankConnectionString"].ConnectionString; using (SqlConnection conn = new SqlConnection(connString1)) { conn.Open(); using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn)) { bulkCopy.DestinationTableName = "dbo.QuizBank"; bulkCopy.WriteToServer(sdt); } conn.Dispose(); } }
public XmlDataDocument GetDataSet(string query) { return ErrorResult("Sorry - Old call"); try { // Create a connection to the data source using (OdbcConnection connection = new OdbcConnection(GetConnectionString())) { if (connection == null) return ErrorResult("No connection"); connection.Open(); if (connection.State != ConnectionState.Open) return ErrorResult("No open connection"); using (OdbcDataAdapter dataAdapter = new OdbcDataAdapter(query, connection)) { if (dataAdapter == null) return ErrorResult("No data adapter"); // "Root" is the root element name using (DataSet dataSet = new DataSet("Root")) { // "Row" is the name of each row in the set int rowCount = dataAdapter.Fill(dataSet, "Row"); if (dataSet.Tables.Count <= 0) return ErrorResult("No dataset returned"); // Indicate we want columns mapped as Xml attributes instead of elements foreach (DataColumn column in dataSet.Tables[0].Columns) column.ColumnMapping = MappingType.Attribute; connection.Close(); connection.Dispose(); dataAdapter.Dispose(); return new XmlDataDocument(dataSet); } } } } catch (Exception e) { return ErrorResult(e.Message); //j + "\r\n\r\n" + e.StackTrace); } }
public System.Data.DataTable LoadExcel(string pPath) { string connString = "Driver={Driver do Microsoft Excel(*.xls)};DriverId=790;SafeTransactions=0;ReadOnly=1;MaxScanRows=16;Threads=3;MaxBufferSize=2024;UserCommitSync=Yes;FIL=excel 8.0;PageTimeout=5;"; //连接字符串 //简单解释下这个连续字符串,Driver={Driver do Microsoft Excel(*.xls)} 这种连接写法不需要创建一个数据源DSN,DRIVERID表示驱动ID,Excel2003后都使用790, //FIL表示Excel文件类型,Excel2007用excel 8.0,MaxBufferSize表示缓存大小, 如果你的文件是2010版本的,也许会报错,所以要找到合适版本的参数设置。 connString += "DBQ=" + pPath; //DBQ表示读取Excel的文件名(全路径) OdbcConnection conn = new OdbcConnection(connString); OdbcCommand cmd = new OdbcCommand(); cmd.Connection = conn; //获取Excel中第一个Sheet名称,作为查询时的表名 string sheetName = this.GetExcelSheetName(pPath); string sql = "select * from [" + sheetName.Replace('.', '#') + "$]"; cmd.CommandText = sql; OdbcDataAdapter da = new OdbcDataAdapter(cmd); DataSet ds = new DataSet(); try { da.Fill(ds); return ds.Tables[0]; //返回Excel数据中的内容,保存在DataTable中 } catch (Exception x) { ds = null; throw new Exception("从Excel文件中获取数据时发生错误!可能是Excel版本问题,可以考虑降低版本或者修改连接字符串值"); } finally { cmd.Dispose(); cmd = null; da.Dispose(); da = null; if (conn.State == ConnectionState.Open) { conn.Close(); } conn = null; } }
public void importar() { eventLog eventTracerLog = new eventLog(); SqlConnection Conn; SqlCommand cmdPedidos; String strQuery; String queryValues; Boolean verifyInsert = true; Boolean errorFound = false; String[] ColumnsName = { "tipo", "campo_desconocido", "idProveedor", "idPedido", "fechaDesde", "fechaHasta", "idMaterial", "descripcion", "medida", "mail", "centro", "nameProveedor", "idSAP", "centroDesc", "posicion", "cantidad", "ocabi" }; String[] ColumnsTypes = { "string", "int", "int", "int", "string", "string", "int", "string", "string", "string", "string", "string", "string", "string", "int", "int", "string" }; DataSet dsExcel = new DataSet(); String[] valoresRow = new String[ColumnsName.Length]; SqlDataAdapter dtPedidoConsulta = new SqlDataAdapter(); DataSet dsPedidoConsulta = new DataSet(); Boolean pedidoExist = false; String baseDir = "e:\\proveedores\\compras_ofertas\\"; try { if (File.Exists(AppDomain.CurrentDomain.BaseDirectory + "/salidawebprd.csv")) { File.Delete(AppDomain.CurrentDomain.BaseDirectory + "/salidawebprd.csv"); } // COPIA DEL ARCHIVO File.Copy(baseDir+"salidawebprd.txt", AppDomain.CurrentDomain.BaseDirectory + "/salidawebprd.csv",true); String strTextFile = File.ReadAllText(AppDomain.CurrentDomain.BaseDirectory + "/salidawebprd.csv"); strTextFile = strTextFile.Replace(',', '.'); strTextFile = strTextFile.Replace('"', 'º'); File.WriteAllText(AppDomain.CurrentDomain.BaseDirectory + "/salidawebprd.csv", strTextFile); String strConn = @"Driver={Microsoft Text Driver (*.txt; *.csv)};" + "Dbq=" + AppDomain.CurrentDomain.BaseDirectory + ";"; // Get all Table Names OdbcConnection conn = new OdbcConnection(strConn); OdbcDataAdapter daExcel = new OdbcDataAdapter("SELECT * FROM [salidawebprd.csv]", conn); daExcel.Fill(dsExcel); if (dsExcel.Tables[0].Rows.Count > 0) { Conn = new SqlConnection("Persist Security Info=False;Data Source=localhost;Initial Catalog=zeniprovedores;User ID=arlequinsql;Password=123"); //Conn = new SqlConnection("Persist Security Info=False;Data Source=verite;Initial Catalog=webores;User ID=sa;Password=Pqqk7D0XUaos4H"); Conn.Open(); dtPedidoConsulta.SelectCommand = new SqlCommand("(SELECT DISTINCT pedidos.idPedido FROM pedidos) UNION ALL (SELECT DISTINCT pedidos_hist.idPedido FROM pedidos_hist)", Conn); dtPedidoConsulta.Fill(dsPedidoConsulta, "pedidos"); cmdPedidos = new SqlCommand(); //cmdPedidos.ExecuteNonQuery(); for (int rowCount = 0; rowCount < dsExcel.Tables[0].Rows.Count; rowCount++) { if (dsExcel.Tables[0].Rows[rowCount][0].ToString().Length >= 6) { valoresRow = dsExcel.Tables[0].Rows[rowCount][0].ToString().Split(dsExcel.Tables[0].Rows[rowCount][0].ToString()[6]); pedidoExist = false; foreach (DataRow rowSelect in dsPedidoConsulta.Tables[0].Rows) { if (rowSelect[0].ToString() == valoresRow[3]) //-- Compara Si existe el Pedido { pedidoExist = true; break; } } if (!pedidoExist) { strQuery = "INSERT INTO pedidos ("; queryValues = ""; verifyInsert = true; for (int columnCount = 0; columnCount < ColumnsName.Length; columnCount++) { if (ColumnsName[columnCount] != "tipo") { strQuery += ColumnsName[columnCount] + ","; if (ColumnsName[columnCount] != "ocabi" && (valoresRow[columnCount] == "" || valoresRow[columnCount] == null)) { verifyInsert = false; } switch (ColumnsTypes[columnCount]) { case "int": { queryValues += valoresRow[columnCount].Trim() + ","; break; } default: { queryValues += "'" + valoresRow[columnCount].Trim() + "',"; break; } } } } if (verifyInsert) { strQuery = strQuery.Substring(0, strQuery.Length - 1) + ") VALUES (" + queryValues.Substring(0, queryValues.Length - 1) + ")"; //Response.Write(strQuery + "<br><br>"); cmdPedidos = new SqlCommand(strQuery, Conn); cmdPedidos.ExecuteNonQuery(); } } } } daExcel.Dispose(); dsExcel.Dispose(); dsPedidoConsulta.Dispose(); dtPedidoConsulta.Dispose(); cmdPedidos.Dispose(); conn.Close(); conn.Dispose(); Conn.Close(); Conn.Dispose(); } } catch (SqlException sqlEx) { errorFound = true; eventTracerLog.createEntry("Importación de Pedidos: " + sqlEx.Message, true); } catch (Exception ex) { errorFound = false; eventTracerLog.createEntry("Importación de Pedidos: " + ex.Message, true); } finally { if (!errorFound) { eventTracerLog.createEntry("Importación de Pedidos", false); } } }
/// <summary> /// Execute คำสั่ง SQL แล้วเก็บค่าที่ได้ใส่ DataTable /// </summary> /// <param name="strSQL"></param> /// <param name="outMessage"></param> /// <returns></returns> public DataTable Bind(string strSQL,out string outMessage,int timeOut=30) { #region Variable var csSQL = getConnectionString(cs); var dt = new DataTable(); outMessage = ""; #endregion #region Procedure if (!string.IsNullOrEmpty(csSQL)) { if (dbType == DBType.SQLServer) { #region SQLServer try { using (var myConn_SQL = new SqlConnection(csSQL)) using (var myDa_SQL = new SqlDataAdapter(QueryFilterByDatabaseType(strSQL), myConn_SQL)) { myDa_SQL.SelectCommand.CommandTimeout = timeOut; myDa_SQL.Fill(dt); myConn_SQL.Dispose(); myDa_SQL.Dispose(); if (dt.Rows.Count > 0 && dt != null) { return dt; } else { dt.Dispose(); return null; } } } catch (Exception ex) { outMessage = ex.Message; return null; } #endregion } else if (dbType == DBType.ODBC) { #region ODBC try { using (var myConn_ODBC = new OdbcConnection(csSQL)) using (var myDa_ODBC = new OdbcDataAdapter(QueryFilterByDatabaseType(strSQL), myConn_ODBC)) { myDa_ODBC.SelectCommand.CommandTimeout = timeOut; myDa_ODBC.Fill(dt); myConn_ODBC.Dispose(); myDa_ODBC.Dispose(); if (dt.Rows.Count > 0 && dt != null) { return dt; } else { dt.Dispose(); return null; } } } catch (Exception ex) { outMessage = ex.Message; return null; } #endregion } else if (dbType == DBType.MySQL) { #region MySQL try { using (var myConn_MySQL = new MySql.Data.MySqlClient.MySqlConnection(csSQL)) using (var myDa_MySQL = new MySql.Data.MySqlClient.MySqlDataAdapter(QueryFilterByDatabaseType(strSQL), myConn_MySQL)) { myDa_MySQL.SelectCommand.CommandTimeout = timeOut; myDa_MySQL.Fill(dt); myConn_MySQL.Dispose(); myDa_MySQL.Dispose(); if (dt.Rows.Count > 0 && dt != null) { return dt; } else { dt.Dispose(); return null; } } } catch (Exception ex) { outMessage = ex.Message; return null; } #endregion } else { outMessage = "Not found DBType."; return null; } } else { outMessage = "Not found AppSettingName."; return null; } #endregion }
/// <summary> /// Execute คำสั่ง SQL แล้วเก็บค่าที่ได้ใส่ DataTable /// </summary> /// <param name="strSql">SQL Query</param> /// <param name="strDBType">ชนิดของฐานข้อมูล เช่น sql,odbc,mysql</param> /// <param name="appSettingName">ชื่อตัวแปรที่เก็บ ConnectionString ในไฟล์ AppSetting</param> /// <param name="outMessage">คืนค่าข้อความ กรณีเกิดข้อผิดพลาด</param> /// <returns>DataTable</returns> /// <example> /// string outMessage; /// clsSQL.Bind("SELECT * FROM member",clsSQL.DBType.MySQL,"cs",out outMessage); /// </example> public DataTable Bind(string strSql, DBType dbType, string appSettingName, out string outMessage) { #region Variable var csSQL = (appSettingName.Contains("=") ? appSettingName : System.Configuration.ConfigurationManager.AppSettings[appSettingName]); var dt = new DataTable(); outMessage = ""; #endregion #region Procedure if (!string.IsNullOrEmpty(csSQL)) { if (dbType == DBType.SQLServer) { #region SQLServer try { using (var myConn_SQL = new SqlConnection(csSQL)) using (var myDa_SQL = new SqlDataAdapter(strSql, myConn_SQL)) { myDa_SQL.Fill(dt); myConn_SQL.Dispose(); myDa_SQL.Dispose(); if (dt.Rows.Count > 0 && dt != null) { return dt; } else { dt.Dispose(); return null; } } } catch (Exception ex) { outMessage = ex.Message; return null; } #endregion } else if (dbType == DBType.ODBC) { #region ODBC try { using (var myConn_ODBC = new OdbcConnection(csSQL)) using (var myDa_ODBC = new OdbcDataAdapter(strSql, myConn_ODBC)) { myDa_ODBC.Fill(dt); myConn_ODBC.Dispose(); myDa_ODBC.Dispose(); if (dt.Rows.Count > 0 && dt != null) { return dt; } else { dt.Dispose(); return null; } } } catch (Exception ex) { outMessage = ex.Message; return null; } #endregion } else { outMessage = "Not found DBType."; return null; } } else { outMessage = "Not found AppSettingName."; return null; } #endregion }
/// <summary> /// Execute คำสั่ง SQL แล้วเก็บค่าที่ได้ใส่ DataTable โดยสามารถระบุ SQL Parameter ได้ /// </summary> /// <param name="strSql">SQL Query</param> /// <param name="arrParameter">SQL Parameter (new string[,] { { "?ID", txtTest.Text } })</param> /// <param name="strDBType">ชนิดของฐานข้อมูล เช่น sql,odbc,mysql</param> /// <param name="appSettingName">ชื่อตัวแปรที่เก็บ ConnectionString ในไฟล์ AppSetting</param> /// <returns>ข้อมูล</returns> /// <example> /// strSQL.Append("SELECT email FROM member WHERE id=?ID"); /// dt = Bind(strSQL.ToString(), new string[,] { { "?ID", txtTest.Text } }, clsSQL.DBType.MySQL, "cs"); /// </example> public DataTable Bind(string strSql, string[,] arrParameter, DBType dbType, string appSettingName) { #region Variable var csSQL = (appSettingName.Contains("=") ? appSettingName : System.Configuration.ConfigurationManager.AppSettings[appSettingName]); var dt = new DataTable(); var i = 0; #endregion #region Procedure if (!string.IsNullOrEmpty(csSQL)) { if (dbType == DBType.SQLServer) { #region SQLServer using (var myConn_SQL = new SqlConnection(csSQL)) using (var myDa_SQL = new SqlDataAdapter(strSql, myConn_SQL)) { for (i = 0; i < arrParameter.Length / arrParameter.Rank; i++) { myDa_SQL.SelectCommand.Parameters.AddWithValue(arrParameter[i, 0], arrParameter[i, 1]); } myDa_SQL.Fill(dt); myConn_SQL.Dispose(); myDa_SQL.Dispose(); if (dt.Rows.Count > 0 && dt != null) { return dt; } else { dt.Dispose(); return null; } } #endregion } else if (dbType == DBType.ODBC) { #region ODBC using (var myConn_ODBC = new OdbcConnection(csSQL)) using (var myDa_ODBC = new OdbcDataAdapter(strSql, myConn_ODBC)) { for (i = 0; i < arrParameter.Length / arrParameter.Rank; i++) { myDa_ODBC.SelectCommand.Parameters.AddWithValue(arrParameter[i, 0], arrParameter[i, 1]); } myDa_ODBC.Fill(dt); myConn_ODBC.Dispose(); myDa_ODBC.Dispose(); if (dt.Rows.Count > 0 && dt != null) { return dt; } else { dt.Dispose(); return null; } } #endregion } else { return null; } } else { return null; } #endregion }
public void Dispose () { OdbcDataAdapter da = new OdbcDataAdapter (); da.DeleteCommand = new OdbcCommand (); da.InsertCommand = new OdbcCommand (); da.SelectCommand = new OdbcCommand (); da.UpdateCommand = new OdbcCommand (); da.Dispose (); Assert.IsNull (da.DeleteCommand, "#1"); Assert.IsNull (da.InsertCommand, "#2"); Assert.IsNull (da.SelectCommand, "#3"); Assert.IsNotNull (da.TableMappings, "#4"); Assert.AreEqual (0, da.TableMappings.Count, "#5"); Assert.IsNull (da.UpdateCommand, "#6"); }
/**/ /// <summary> /// 与数据库建立连接 /// </summary> /// <param name="connectString">数据库连接字符串</param> /// <returns>返回类型</returns> public override bool Connect(string connectString) { bool isConnect = false; if (m_Conn == null || m_Conn.State != ConnectionState.Open) { m_Conn = new OdbcConnection(connectString); m_Conn.Open(); isConnect = true; } return isConnect; } /**/ /// <summary> /// 执行数据库查询语句 /// </summary> /// <param name="strSQL">结构化数据库查询语句</param> /// <returns>返回类型</returns> public override DataTable ExecuteSQL(string strSQL) { DataTable dt = new DataTable(); OdbcCommand command = null; OdbcDataAdapter da = null; try { command = new OdbcCommand(strSQL, m_Conn); da = new OdbcDataAdapter(); da.SelectCommand = command; da.Fill(dt); } catch (Exception e) { #if DEBUG System.Console.WriteLine(e.StackTrace.ToString()); #endif } finally { command.Dispose(); da.Dispose(); } return dt; }
private DataTable GetData(string Query) { string _connStr = Settings.Database.Conn. Replace("{Database}", Settings.Database.Name). Replace("{Server}", Settings.Database.Host). Replace("{Port}", Settings.Database.Port). Replace("{Username}", Settings.Database.User). Replace("{Password}", Settings.Database.Pass); DataTable _dt = new DataTable(); DBError = ""; using (OdbcConnection _dbconn = new OdbcConnection(_connStr)) { try { _dbconn.Open(); using (OdbcCommand _command = new OdbcCommand()) { _command.Connection = _dbconn; _command.CommandType = CommandType.Text; _command.CommandText = Query; OdbcDataAdapter _adap = new OdbcDataAdapter(_command); _adap.Fill(_dt); _adap.Dispose(); } } catch (Exception _ex) { DBError = _ex.Message; if (DBError.Substring(0,(DBError.Length >> 1) - 1).Equals(DBError.Substring((DBError.Length >> 1) + 1))) { // ODBC is doubling up error messages for some reason. DBError = DBError.Substring(0, (DBError.Length >> 1) - 1); } _dt.Columns.Add("Empty", typeof(string)); } if (_dt.Rows.Count < 1) { _dt.Rows.Add(); } } return _dt; }
/// <summary> /// 实时数据库的数据集 /// </summary> /// <returns></returns> public static void GetRealData() { //string RealConnectionSting = "DSN=FIX Dynamics Real Time Data";//实时数据库连接字符串 string RealConnectionSting = ConfigurationSettings.AppSettings["RealDataConnectionString"];//从web.config中读出数据库连接字符串 //string HisConnectionSting = "DSN=FIX Dynamics Historical Data";//历史数据库连接字符串 OdbcConnection RealConnection;//实时数据库连接 //OdbcConnection HisConnection;//历史数据库连接 RealConnection = new OdbcConnection(RealConnectionSting); OdbcDataAdapter adapter = new OdbcDataAdapter("select A_CV from FIX", RealConnection); DataSet ds = new DataSet(); RealConnection.Open();// 11.19日修改 adapter.Fill(ds, "FIX"); RealConnection.Close();// 11.19日修改 adapter.Dispose(); for (int i = 0; i < 56; i++) //将前56个点放入Boiler1数组中 模拟量 { Boiler1[i] = ds.Tables[0].Rows[i]["A_CV"].ToString(); } for (int i = 209; i < 220;i++ ) //从第210项开始的11个开关量送入Boiler1数组 { Boiler1[i-153] = ds.Tables[0].Rows[i]["A_CV"].ToString(); } for (int j = 56; j < 113; j++) //将57-113的57个点放入Boiler2数组中 模拟量 { Boiler2[j-56] = ds.Tables[0].Rows[j]["A_CV"].ToString(); } for (int j = 220; j < 231; j++) //从第220项开始的11个开关量送入Boiler2数组 { Boiler2[j - 163] = ds.Tables[0].Rows[j]["A_CV"].ToString(); } for (int k = 113; k < 189; k++) //将114-189的76个点放入Trubine数组中 模拟量 { Trubine[k - 113] = ds.Tables[0].Rows[k]["A_CV"].ToString(); } for (int m = 189; m < 209; m++) //将190-109的20个点放入DEH数组中 模拟量 { DEH[m - 189] = ds.Tables[0].Rows[m]["A_CV"].ToString(); } for (int k = 231; k < 247; k++) //从第232-247项的16个开关量送入Trubine数组 { Trubine[k - 155] = ds.Tables[0].Rows[k]["A_CV"].ToString(); } for (int H = 247; H < 250; H++) //从第248-250项的3个开关量送入DEH数组 { DEH[H - 227] = ds.Tables[0].Rows[H]["A_CV"].ToString(); } //for (int j = 62; j < 125; j++) //将63-125的点放入Boiler2数组中 模拟量+开关量 63个 //{ // Boiler2[j - 62] = ds.Tables[0].Rows[j]["A_CV"].ToString(); //} //for (int k = 126; k < 199; k++) // 将前126-199的点放入Trubine数组中 模拟量+开关量 74个 //{ // Trubine[k - 126] = ds.Tables[0].Rows[k]["A_CV"].ToString(); //} //for (int m = 200; m < 228; m++)//将前200-228的点放入DEH数组中 模拟量+开关量 29个 //{ // DEH[m - 200] = ds.Tables[0].Rows[m]["A_CV"].ToString(); //} //return ds; ds.Dispose(); }
/// <summary> /// Execute คำสั่ง SQL แล้วเก็บค่าที่ได้ใส่ DataTable /// </summary> /// <param name="strSql">SQL Query</param> /// <param name="strDBType">ชนิดของฐานข้อมูล เช่น sql,odbc,mysql</param> /// <param name="appsetting_name">ชื่อตัวแปรที่เก็บ ConnectionString ในไฟล์ AppSetting</param> /// <returns>ข้อมูล</returns> /// <example> /// clsSQL.Bind("SELECT * FROM member",clsSQL.DBType.MySQL,"cs"); /// </example> public DataTable Bind(string strSql, DBType dbType, string appsetting_name) { #region Variable var csSQL = System.Configuration.ConfigurationManager.AppSettings[appsetting_name]; var dt = new DataTable(); #endregion #region Procedure if (!string.IsNullOrEmpty(csSQL)) { if (dbType == DBType.SQLServer) { #region SQLServer var myConn_SQL = new SqlConnection(csSQL); var myDa_SQL = new SqlDataAdapter(strSql, myConn_SQL); myDa_SQL.Fill(dt); myConn_SQL.Dispose(); myDa_SQL.Dispose(); if (dt.Rows.Count > 0 && dt != null) { return dt; } else { dt.Dispose(); return null; } #endregion } else if (dbType == DBType.ODBC) { #region ODBC var myConn_ODBC = new OdbcConnection(csSQL); var myDa_ODBC = new OdbcDataAdapter(strSql, myConn_ODBC); myDa_ODBC.Fill(dt); myConn_ODBC.Dispose(); myDa_ODBC.Dispose(); if (dt.Rows.Count > 0 && dt != null) { return dt; } else { dt.Dispose(); return null; } #endregion } else { return null; } } else { return null; } #endregion }
public void importar() { string cuenta = ""; eventLog eventTracerLog = new eventLog(); Boolean errorFound = false; SqlConnection Conn; SqlCommand cmdPedidos; String strQuery; String queryValues; Boolean verifyInsert = true; String[] ColumnsName = { "no", "idProveedor", "tipoDoc", "idSap", "idFactura", "ejercicio", "monto", "moneda", "cuentaDesc", "claveCont", "fecha", "estado", "relacion", "tipoCuenta", "fechaComp", "viaPago" }; String[] ColumnsTypes = { "int", "string", "string", "int", "string", "int", "int", "string", "string", "int", "datetime", "string", "int", "string", "datetime", "string" }; DataSet dsExcel = new DataSet(); String[] valoresRow = new String[ColumnsName.Length]; SqlDataAdapter dtPedidoConsulta = new SqlDataAdapter(); DataSet dsPedidoConsulta = new DataSet(); Boolean pedidoExist = false; String[] fecha = new String[3]; Boolean compensada = false; String strConn = @"Driver={Microsoft Text Driver (*.txt; *.csv)};" + "Dbq=" + AppDomain.CurrentDomain.BaseDirectory + ";"; OdbcConnection conn; OdbcDataAdapter daExcel; DateTime fechaActual = DateTime.Now; String baseDir = "e:\\proveedores\\compras_ofertas\\"; try { //IF FILE EXISTS, THE DELETE IT if (File.Exists(AppDomain.CurrentDomain.BaseDirectory + "/log_facturacion.txt")) { String fileBackup = AppDomain.CurrentDomain.BaseDirectory + "/log_facturacion_" + fechaActual.Hour.ToString() + "_" + fechaActual.Minute.ToString() + "_" + fechaActual.Day.ToString() + "_" + fechaActual.Month.ToString() + "_" + fechaActual.Year.ToString() + ".txt"; File.Copy(AppDomain.CurrentDomain.BaseDirectory + "/log_facturacion.txt", fileBackup); File.Delete(AppDomain.CurrentDomain.BaseDirectory + "/log_facturacion.txt"); } File.CreateText(AppDomain.CurrentDomain.BaseDirectory + "/log_facturacion.txt").Close(); if (File.Exists(AppDomain.CurrentDomain.BaseDirectory + "/facturacion.csv")) { File.Delete(AppDomain.CurrentDomain.BaseDirectory + "/facturacion.csv"); } // FTP CONNECTION File.Copy(baseDir+"Facturacion.txt", AppDomain.CurrentDomain.BaseDirectory + "/facturacion.csv"); // REPLACE INVALIDED CHARACTERS String strTextFile = File.ReadAllText(AppDomain.CurrentDomain.BaseDirectory + "/facturacion.csv"); strTextFile = strTextFile.Replace(',', '.'); strTextFile = strTextFile.Replace('"', 'º'); strTextFile = strTextFile.Replace(';', ','); File.WriteAllText(AppDomain.CurrentDomain.BaseDirectory + "/facturacion.csv", "\n"+strTextFile); //CONNECTION FILE CSV conn = new OdbcConnection(strConn); daExcel = new OdbcDataAdapter("SELECT * FROM [facturacion.csv]", conn); daExcel.Fill(dsExcel); if (dsExcel.Tables[0].Rows.Count > 0) { Conn = new SqlConnection("Persist Security Info=False;Data Source=localhost;Initial Catalog=zeniprovedores;User ID=arlequinsql;Password=123"); //Conn = new SqlConnection("Persist Security Info=False;Data Source=verite;Initial Catalog=webores;User ID=sa;Password=Pqqk7D0XUaos4H"); Conn.Open(); dtPedidoConsulta.SelectCommand = new SqlCommand("SELECT DISTINCT facturacion.idFactura, facturacion.idProveedor, facturacion.estado FROM facturacion", Conn); dtPedidoConsulta.Fill(dsPedidoConsulta, "facturacion"); cmdPedidos = new SqlCommand(); //cmdPedidos.ExecuteNonQuery(); for (int rowCount = 0; rowCount < dsExcel.Tables[0].Rows.Count; rowCount++) { if (dsExcel.Tables[0].Rows[rowCount][0].ToString().Length >= 6) { valoresRow = dsExcel.Tables[0].Rows[rowCount][0].ToString().Split(','); cuenta = dsExcel.Tables[0].Rows[rowCount][0].ToString(); eventTracerLog.createEntry("procesando: " + rowCount +" - "+cuenta, true); pedidoExist = false; compensada = false; foreach (DataRow rowSelect in dsPedidoConsulta.Tables[0].Rows) { if (rowSelect[0].ToString() == valoresRow[4] && Convert.ToInt32(rowSelect[1].ToString()) == Convert.ToInt32(valoresRow[1].ToString())) //-- Compara Si existe el Pedido { compensada = (rowSelect["estado"].ToString().ToLower() == "x" ? true : false); pedidoExist = true; break; } } eventTracerLog.createEntry("procesando ok: " + rowCount + " - " + cuenta, true); if (!pedidoExist) { strQuery = "INSERT INTO facturacion ("; } else { strQuery = "UPDATE facturacion SET "; } queryValues = ""; verifyInsert = true; if (!compensada) { for (int columnCount = 0; columnCount < ColumnsName.Length; columnCount++) { if (ColumnsName[columnCount] != "no") { strQuery += ColumnsName[columnCount] + (pedidoExist ? "=" : ","); if (ColumnsName[columnCount] != "estado" && ColumnsName[columnCount] != "relacion" && ColumnsName[columnCount] != "viaPago" && (valoresRow[columnCount] == "" || valoresRow[columnCount] == null)) { verifyInsert = false; } if (Convert.ToInt32(valoresRow[0].ToString()) < 30000 || Convert.ToInt32(valoresRow[0].ToString()) >= 49999) { verifyInsert = false; } switch (valoresRow[2]) { case "KR": case "KG": case "KU": case "RE": case "KC": case "ZP": case "KZ": case "KA": case "KD": case "EX": break; default: verifyInsert = false; break; } if (valoresRow[13].ToString().ToUpper() != "K") { verifyInsert = false; } switch (ColumnsTypes[columnCount]) { case "int": { if (valoresRow[columnCount].Trim() == "") { valoresRow[columnCount] = "0"; } if (pedidoExist) { strQuery += valoresRow[columnCount].Trim() + ","; } else { queryValues += valoresRow[columnCount].Trim() + ","; } break; } case "datetime": { fecha = valoresRow[columnCount].Trim().Split('.'); if (pedidoExist) { if (fecha[1] != "00") { strQuery += "'" + fecha[1] + "-" + fecha[0] + "-" + fecha[2] + "',"; } else { strQuery += "null,"; } } else { if (fecha[1] != "00") { queryValues += "'" + fecha[1] + "-" + fecha[0] + "-" + fecha[2] + "',"; } else { queryValues += "null,"; } } break; } default: { if (pedidoExist) { strQuery += "'" + valoresRow[columnCount].Trim() + "',"; } else { queryValues += "'" + valoresRow[columnCount].Trim() + "',"; } break; } } } } if (verifyInsert) { if (pedidoExist) { strQuery = strQuery.Substring(0, strQuery.Length - 1) + " WHERE idFactura = '" + valoresRow[4] + "' and idProveedor = " + valoresRow[1]; } else { strQuery = strQuery.Substring(0, strQuery.Length - 1) + ") VALUES (" + queryValues.Substring(0, queryValues.Length - 1) + ")"; } cmdPedidos = new SqlCommand(strQuery, Conn); cmdPedidos.ExecuteNonQuery(); } if (!verifyInsert) { strQuery = ""; } File.WriteAllText(AppDomain.CurrentDomain.BaseDirectory + "/log_facturacion.txt", File.ReadAllText(AppDomain.CurrentDomain.BaseDirectory + "/log_facturacion.txt").ToString() + "\n" + "linea: " + (rowCount + 1) + " " + " Ejecucion: " + verifyInsert + " Datos: " + dsExcel.Tables[0].Rows[rowCount][0].ToString() + " Consulta ejecutada: " + strQuery); } } } daExcel.Dispose(); dsExcel.Dispose(); dsPedidoConsulta.Dispose(); dtPedidoConsulta.Dispose(); cmdPedidos.Dispose(); conn.Close(); conn.Dispose(); Conn.Close(); Conn.Dispose(); } } catch (SqlException sqlEx) { errorFound = true; eventTracerLog.createEntry("Importación de Facturación de sql: " + sqlEx.Message, true); } catch (Exception ex) { errorFound = true; eventTracerLog.createEntry("Importación de Facturación de parseo: " + ex.Message, true); } finally { if (!errorFound) { eventTracerLog.createEntry("Importación de Facturación correcta", false); } } }
public string[] GetRealDataTimer() { try { OdbcDataAdapter adapter = new OdbcDataAdapter("select * from FIX", RealConnection); DataSet ds = new DataSet(); adapter.Fill(ds, "FIX"); for (int i = 0; i < 100; i++) { data[i] = ds.Tables[0].Rows[i]["A_CV"].ToString(); } adapter.Dispose(); // RealConnection..Close(); return data; } catch (Exception ex) { for (int i = 0; i < 100; i++) { data[i] = "???"; } return data; } }
/// <summary> /// 返回历史数据集 /// </summary> /// <returns></returns> public static DataSet GetHisData(string Tag_Name,string StartTime,string EndTime,string Interval)//StartTime:"2007-09-06 00:00:00" Interval:"00:00:05" { //if (Con)//初始为true 可以访问历史数据库 //{ // Con = false;//对别的访问数据库的事件加锁 //string HisConnectionString = "DSN=FIX Dynamics Historical Data";//历史数据库连接字符串 string HisConnectionString = ConfigurationSettings.AppSettings["HisDataConnectionString"];//从web.config中读出数据库连接字符串 OdbcConnection HisConnection;//历史数据库连接 string QueryString; string TN; string ST; string ET; string Inter; TN = Tag_Name; ST = StartTime; ET = EndTime; Inter = Interval; if (ST == "") { ST = "2008-02-25 09:50:00"; } if (ET == "") { ET = "2008-02-25 10:00:00"; } if (Inter == "") { Inter = "00:00:10"; } if (TN == "") { //QueryString = "select TAG,VALUE,DATETIME,INTERVAL from FIX where INTERVAL='" + Inter + "'AND (DATETIME>={ts'" + ST + "'} AND DATETIME<={ts'" + ET + "'})"; QueryString = "select VALUE,DATETIMEL from FIX where TAG='RAC4211/PV1'AND INTERVAL='" + Inter + "'AND (DATETIME>={ts'" + ST + "'} AND DATETIME<={ts'" + ET + "'})"; } else { //QueryString = "select TAG,VALUE,DATETIME,INTERVAL from FIX where TAG='" + TN + "'AND INTERVAL='" + Inter + "'AND (DATETIME>={ts'" + ST + "'} AND DATETIME<={ts'" + ET + "'})"; QueryString = "select VALUE,DATETIME from FIX where TAG='" + TN + "'AND INTERVAL='" + Inter + "'AND (DATETIME>={ts'" + ST + "'} AND DATETIME<={ts'" + ET + "'})"; } DataSet ds = new DataSet(); try { HisConnection = new OdbcConnection(HisConnectionString); HisConnection.Open(); OdbcDataAdapter adapter = new OdbcDataAdapter(QueryString, HisConnection); adapter.Fill(ds, "FIX"); adapter.Dispose(); HisConnection.Close(); return ds; } catch (Exception ec) { return ds; } // finally // { // Con = true;//释放对历史数据库的锁 // } //} //else //若历史数据库访问权限加锁 则返回空 //{ // DataSet ds = new DataSet(); // return ds; //} }
public static DataSet GetRealData1() { //string RealConnectionSting = "DSN=FIX Dynamics Real Time Data";//实时数据库连接字符串 string RealConnectionSting = ConfigurationSettings.AppSettings["RealDataConnectionString"];//从web.config中读出数据库连接字符串 //string HisConnectionSting = "DSN=FIX Dynamics Historical Data";//历史数据库连接字符串 OdbcConnection RealConnection;//实时数据库连接 //OdbcConnection HisConnection;//历史数据库连接 RealConnection = new OdbcConnection(RealConnectionSting); OdbcDataAdapter adapter = new OdbcDataAdapter("select A_CV from FIX", RealConnection); DataSet ds = new DataSet(); RealConnection.Open();// 11.19日修改 adapter.Fill(ds, "FIX"); RealConnection.Close();// 11.19日修改 adapter.Dispose(); //for (int i = 0; i < 62; i++) //将前62个点放入Boiler1数组中 模拟量+开关量 //{ // Boiler1[i] = ds.Tables[0].Rows[i]["A_CV"].ToString(); //} //for (int j = 62; j < 125; j++) //将63-125的点放入Boiler2数组中 模拟量+开关量 63个 //{ // Boiler2[j - 62] = ds.Tables[0].Rows[j]["A_CV"].ToString(); //} //for (int k = 126; k < 199; k++) // 将前126-199的点放入Trubine数组中 模拟量+开关量 74个 //{ // Trubine[k - 126] = ds.Tables[0].Rows[k]["A_CV"].ToString(); //} //for (int m = 200; m < 228; m++)//将前200-228的点放入DEH数组中 模拟量+开关量 29个 //{ // DEH[m - 200] = ds.Tables[0].Rows[m]["A_CV"].ToString(); //} return ds; //ds.Dispose(); }
public DataTable Bind(string strSQL, string[,] arrParameter, out string outMessage) { #region Variable var csSQL = getConnectionString(cs); var dt = new DataTable(); var i = 0; outMessage = ""; #endregion #region Procedure if (!string.IsNullOrEmpty(csSQL)) { if (dbType == DBType.SQLServer) { #region SQLServer try { using (var myConn_SQL = new SqlConnection(csSQL)) using (var myDa_SQL = new SqlDataAdapter(QueryFilterByDatabaseType(strSQL), myConn_SQL)) { for (i = 0; i < arrParameter.Length / arrParameter.Rank; i++) { myDa_SQL.SelectCommand.Parameters.AddWithValue(arrParameter[i, 0], arrParameter[i, 1]); } myDa_SQL.Fill(dt); myConn_SQL.Dispose(); myDa_SQL.Dispose(); if (dt.Rows.Count > 0 && dt != null) { return dt; } else { dt.Dispose(); return null; } } } catch (Exception ex) { outMessage = ex.Message; return null; } #endregion } else if (dbType == DBType.ODBC) { #region ODBC try { using (var myConn_ODBC = new OdbcConnection(csSQL)) using (var myDa_ODBC = new OdbcDataAdapter(QueryFilterByDatabaseType(strSQL), myConn_ODBC)) { for (i = 0; i < arrParameter.Length / arrParameter.Rank; i++) { myDa_ODBC.SelectCommand.Parameters.AddWithValue(arrParameter[i, 0], arrParameter[i, 1]); } myDa_ODBC.Fill(dt); myConn_ODBC.Dispose(); myDa_ODBC.Dispose(); if (dt.Rows.Count > 0 && dt != null) { return dt; } else { dt.Dispose(); return null; } } } catch (Exception ex) { outMessage = ex.Message; return null; } #endregion } else if (dbType == DBType.MySQL) { #region MySQL try { using (var myConn_MySQL = new MySql.Data.MySqlClient.MySqlConnection(csSQL)) using (var myDa_MySQL = new MySql.Data.MySqlClient.MySqlDataAdapter(QueryFilterByDatabaseType(strSQL), myConn_MySQL)) { for (i = 0; i < arrParameter.Length / arrParameter.Rank; i++) { myDa_MySQL.SelectCommand.Parameters.AddWithValue(arrParameter[i, 0], arrParameter[i, 1]); } myDa_MySQL.Fill(dt); myConn_MySQL.Dispose(); myDa_MySQL.Dispose(); if (dt.Rows.Count > 0 && dt != null) { return dt; } else { dt.Dispose(); return null; } } } catch (Exception ex) { outMessage = ex.Message; return null; } #endregion } else { outMessage = "Not found DBType."; return null; } } else { outMessage = "Not found AppSettingName."; return null; } #endregion }
/// <summary> /// Execute คำสั่ง SQL แล้วเก็บค่าที่ได้ใส่ DataTable /// </summary> /// <param name="strSql">SQL Query</param> /// <param name="strDBType">ชนิดของฐานข้อมูล เช่น sql,odbc,mysql</param> /// <param name="appsetting_name">ชื่อตัวแปรที่เก็บ ConnectionString ในไฟล์ AppSetting</param> /// <returns>ข้อมูล</returns> /// <example> /// clsSQL.Bind("SELECT * FROM member",clsSQL.DBType.MySQL,"cs"); /// </example> public DataTable Bind(string strSql, DBType dbType, string appsetting_name) { string csSQL = System.Configuration.ConfigurationManager.AppSettings[appsetting_name]; DataTable dt = new DataTable(); if (!string.IsNullOrEmpty(csSQL)) { if (dbType==DBType.SQLServer) { SqlConnection myConn_SQL = new SqlConnection(csSQL); SqlDataAdapter myDa_SQL = new SqlDataAdapter(strSql, myConn_SQL); myDa_SQL.Fill(dt); myConn_SQL.Dispose(); myDa_SQL.Dispose(); if (dt.Rows.Count > 0 && dt != null) { return dt; } else { dt.Dispose(); return null; } } else if (dbType == DBType.ODBC) { OdbcConnection myConn_ODBC = new OdbcConnection(csSQL); OdbcDataAdapter myDa_ODBC = new OdbcDataAdapter(strSql, myConn_ODBC); myDa_ODBC.Fill(dt); myConn_ODBC.Dispose(); myDa_ODBC.Dispose(); if (dt.Rows.Count > 0 && dt != null) { return dt; } else { dt.Dispose(); return null; } } else if (dbType == DBType.MySQL) { MySql.Data.MySqlClient.MySqlConnection myConn_MySQL = new MySql.Data.MySqlClient.MySqlConnection(csSQL); MySql.Data.MySqlClient.MySqlDataAdapter myDa_MySQL = new MySql.Data.MySqlClient.MySqlDataAdapter(strSql, myConn_MySQL); myDa_MySQL.Fill(dt); myConn_MySQL.Dispose(); myDa_MySQL.Dispose(); if (dt.Rows.Count > 0 && dt != null) { return dt; } else { dt.Dispose(); return null; } } else { SqlConnection myConn_SQL = new SqlConnection(csSQL); SqlDataAdapter myDa_SQL = new SqlDataAdapter(strSql, myConn_SQL); myDa_SQL.Fill(dt); myConn_SQL.Dispose(); myDa_SQL.Dispose(); if (dt.Rows.Count > 0 && dt != null) { return dt; } else { dt.Dispose(); return null; } } } else { return null; } }
/// <summary> /// คืนค่าจากคำสั่ง SQLQuery เป็น DataTable /// </summary> /// <param name="strSQL"></param> /// <returns></returns> public DataTable Bind(string strSQL) { #region Variable var csSQL = getConnectionString(cs); var dt = new DataTable(); #endregion #region Procedure if (!string.IsNullOrEmpty(csSQL)) { if (dbType == DBType.SQLServer) { #region SQLServer using (var myConn_SQL = new SqlConnection(csSQL)) using (var myDa_SQL = new SqlDataAdapter(QueryFilterByDatabaseType(strSQL), myConn_SQL)) { if (myConn_SQL.IsConnected()) { myDa_SQL.SelectCommand.CommandTimeout = 0; myDa_SQL.Fill(dt); myConn_SQL.Dispose(); myDa_SQL.Dispose(); if (dt.Rows.Count > 0 && dt != null) { return dt; } else { dt.Dispose(); return null; } } else { return null; } } #endregion } else if (dbType == DBType.ODBC) { #region ODBC using (var myConn_ODBC = new OdbcConnection(csSQL)) using (var myDa_ODBC = new OdbcDataAdapter(QueryFilterByDatabaseType(strSQL), myConn_ODBC)) { myDa_ODBC.Fill(dt); myConn_ODBC.Dispose(); myDa_ODBC.Dispose(); if (dt.Rows.Count > 0 && dt != null) { return dt; } else { dt.Dispose(); return null; } } #endregion } else if (dbType == DBType.MySQL) { #region MySQL using(var myConn_MySQL = new MySql.Data.MySqlClient.MySqlConnection(csSQL)) using (var myDa_MySQL = new MySql.Data.MySqlClient.MySqlDataAdapter(QueryFilterByDatabaseType(strSQL), myConn_MySQL)) { if (myConn_MySQL.IsConnected()) { myDa_MySQL.SelectCommand.CommandTimeout = 0; myDa_MySQL.Fill(dt); myConn_MySQL.Dispose(); myDa_MySQL.Dispose(); if (dt.Rows.Count > 0 && dt != null) { return dt; } else { dt.Dispose(); return null; } } else { return null; } } #endregion } else { return null; } } else { return null; } #endregion }
public static DataTable GetData(string strConn, string strSql, int startRecord, int maxRecords) { DataSet ds = new DataSet(); using (OdbcConnection conn = new OdbcConnection(strConn)) { conn.Open(); OdbcCommand cmd = null; OdbcDataAdapter da = null; try { cmd = new OdbcCommand(strSql, conn); da = new OdbcDataAdapter { SelectCommand = cmd }; da.Fill(ds, startRecord, maxRecords, "tb"); return ds.Tables["tb"]; } catch (Exception ex) { throw new Exception("error getting data " + ex.Message); } finally { if (da != null) { da.Dispose(); } if (cmd != null) { cmd.Dispose(); } conn.Close(); } } }
/// <summary> /// Execute คำสั่ง SQL แล้วเก็บค่าที่ได้ใส่ DataTable โดยสามารถระบุ SQL Parameter ได้ /// </summary> /// <param name="strSql">SQL Query</param> /// <param name="arrParameter">SQL Parameter (new string[,] { { "?ID", txtTest.Text } })</param> /// <param name="strDBType">ชนิดของฐานข้อมูล เช่น sql,odbc,mysql</param> /// <param name="appsetting_name">ชื่อตัวแปรที่เก็บ ConnectionString ในไฟล์ AppSetting</param> /// <param name="outMessage">ข้อความ กรณีเกิดข้อผิดพลาด</param> /// <returns>DataTable</returns> /// <example> /// string outMessage; /// strSQL.Append("SELECT email FROM member WHERE id=?ID"); /// dt = Bind(strSQL.ToString(), new string[,] { { "?ID", txtTest.Text } }, clsSQL.DBType.MySQL, "cs",out outMessage); /// </example> public DataTable Bind(string strSql, string[,] arrParameter, DBType dbType, string appsetting_name, out string outMessage) { #region Variable var csSQL = System.Configuration.ConfigurationManager.AppSettings[appsetting_name]; var dt = new DataTable(); var i = 0; outMessage = ""; #endregion #region Procedure if (!string.IsNullOrEmpty(csSQL)) { if (dbType == DBType.SQLServer) { #region SQLServer try { var myConn_SQL = new SqlConnection(csSQL); var myDa_SQL = new SqlDataAdapter(strSql, myConn_SQL); for (i = 0; i < arrParameter.Length / arrParameter.Rank; i++) { myDa_SQL.SelectCommand.Parameters.AddWithValue(arrParameter[i, 0], arrParameter[i, 1]); } myDa_SQL.Fill(dt); myConn_SQL.Dispose(); myDa_SQL.Dispose(); if (dt.Rows.Count > 0 && dt != null) { return dt; } else { dt.Dispose(); return null; } } catch (Exception ex) { outMessage = ex.Message; return null; } #endregion } else if (dbType == DBType.ODBC) { #region ODBC try { var myConn_ODBC = new OdbcConnection(csSQL); var myDa_ODBC = new OdbcDataAdapter(strSql, myConn_ODBC); for (i = 0; i < arrParameter.Length / arrParameter.Rank; i++) { myDa_ODBC.SelectCommand.Parameters.AddWithValue(arrParameter[i, 0], arrParameter[i, 1]); } myDa_ODBC.Fill(dt); myConn_ODBC.Dispose(); myDa_ODBC.Dispose(); if (dt.Rows.Count > 0 && dt != null) { return dt; } else { dt.Dispose(); return null; } } catch (Exception ex) { outMessage = ex.Message; return null; } #endregion } else { outMessage = "Not found DBType."; return null; } } else { outMessage = "Not found AppSettingName."; return null; } #endregion }
/// <summary> /// ��Ʈw�d�M��k /// </summary> /// <param name="selectcmd"></param> /// <returns></returns> public System.Data.DataTable select(ICommand selectcmd) { string selectCmd = selectcmd.getCommand(); try { cmd = new OdbcCommand(selectCmd, GetConn()); OdbcDataAdapter da = new OdbcDataAdapter(cmd); //cmd = new IBM.Data.DB2.DB2Command(selectCmd, GetConn()); //IBM.Data.DB2.DB2DataAdapter da = new IBM.Data.DB2.DB2DataAdapter(cmd); System.Data.DataTable DT = new System.Data.DataTable(); da.Fill(DT); da.Dispose(); return DT; } catch { try { lock (typeof(OdbcConnection)) { GetConn().Close(); GetConn().Open(); cmd = new OdbcCommand(selectCmd, GetConn()); OdbcDataAdapter da = new OdbcDataAdapter(cmd); //cmd = new IBM.Data.DB2.DB2Command(selectCmd, GetConn()); //IBM.Data.DB2.DB2DataAdapter da = new IBM.Data.DB2.DB2DataAdapter(cmd); System.Data.DataTable DT = new System.Data.DataTable(); da.Fill(DT); da.Dispose(); return DT; } } catch (Exception ex) { throw ex; } } }
private void loadCSV(string path) { if (!File.Exists(path)) { MessageBox.Show(this, "File does not exist:\r\n" + path, "No File", MessageBoxButtons.OK, MessageBoxIcon.Stop); return; } try { Pref.Default.csvPath = path; Pref.Default.Save(); if (path.Length > 50) lOpenFile.Text = "otevřen: " + path.Remove(3) + " ... " + path.Substring(path.Length - 30); else lOpenFile.Text = "otevřen: " + path; string conStr = @"Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + Path.GetDirectoryName(Path.GetFullPath(path)) + ";Extensions=csv,txt"; OdbcConnection conn = new OdbcConnection(conStr); OdbcDataAdapter da = new OdbcDataAdapter("Select * from [" + Path.GetFileName(path) + "]", conn); DataTable dt = new DataTable(path); da.Fill(dt); parseDts(dt, dgvFw, dgvPorty); da.Dispose(); conn.Close(); conn.Dispose(); } catch (Exception ex) { MessageBox.Show(this, "There was an error loading the CSV file:\r\n" + ex.Message, "IO Error", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } }
/// <summary> /// Execute คำสั่ง SQL แล้วเก็บค่าที่ได้ใส่ DataTable โดยสามารถระบุ SQL Parameter ได้ /// </summary> /// <param name="strSql">SQL Query</param> /// <param name="arrParameter">SQL Parameter (new string[,] { { "?ID", txtTest.Text } })</param> /// <param name="strDBType">ชนิดของฐานข้อมูล เช่น sql,odbc,mysql</param> /// <param name="appsetting_name">ชื่อตัวแปรที่เก็บ ConnectionString ในไฟล์ AppSetting</param> /// <returns>ข้อมูล</returns> /// <example> /// strSQL.Append("SELECT email FROM member WHERE id=?ID"); /// dt = Bind(strSQL.ToString(), new string[,] { { "?ID", txtTest.Text } }, clsSQL.DBType.MySQL, "cs"); /// </example> public DataTable Bind(string strSql, string[,] arrParameter, DBType dbType, string appsetting_name) { string csSQL = System.Configuration.ConfigurationManager.AppSettings[appsetting_name]; DataTable dt = new DataTable(); int i; if (!string.IsNullOrEmpty(csSQL)) { if (dbType == DBType.SQLServer) { SqlConnection myConn_SQL = new SqlConnection(csSQL); SqlDataAdapter myDa_SQL = new SqlDataAdapter(strSql, myConn_SQL); for (i = 0; i < arrParameter.Length / arrParameter.Rank; i++) { myDa_SQL.SelectCommand.Parameters.AddWithValue(arrParameter[i, 0], arrParameter[i, 1]); } myDa_SQL.Fill(dt); myConn_SQL.Dispose(); myDa_SQL.Dispose(); if (dt.Rows.Count > 0 && dt != null) { return dt; } else { dt.Dispose(); return null; } } else if (dbType == DBType.ODBC) { OdbcConnection myConn_ODBC = new OdbcConnection(csSQL); OdbcDataAdapter myDa_ODBC = new OdbcDataAdapter(strSql, myConn_ODBC); for (i = 0; i < arrParameter.Length / arrParameter.Rank; i++) { myDa_ODBC.SelectCommand.Parameters.AddWithValue(arrParameter[i, 0], arrParameter[i, 1]); } myDa_ODBC.Fill(dt); myConn_ODBC.Dispose(); myDa_ODBC.Dispose(); if (dt.Rows.Count > 0 && dt != null) { return dt; } else { dt.Dispose(); return null; } } else if (dbType == DBType.MySQL) { MySql.Data.MySqlClient.MySqlConnection myConn_MySQL = new MySql.Data.MySqlClient.MySqlConnection(csSQL); MySql.Data.MySqlClient.MySqlDataAdapter myDa_MySQL = new MySql.Data.MySqlClient.MySqlDataAdapter(strSql, myConn_MySQL); for (i = 0; i < arrParameter.Length / arrParameter.Rank; i++) { myDa_MySQL.SelectCommand.Parameters.AddWithValue(arrParameter[i, 0], arrParameter[i, 1]); } myDa_MySQL.Fill(dt); myConn_MySQL.Dispose(); myDa_MySQL.Dispose(); if (dt.Rows.Count > 0 && dt != null) { return dt; } else { dt.Dispose(); return null; } } else { SqlConnection myConn_SQL = new SqlConnection(csSQL); SqlDataAdapter myDa_SQL = new SqlDataAdapter(strSql, myConn_SQL); for (i = 0; i < arrParameter.Length / arrParameter.Rank; i++) { myDa_SQL.SelectCommand.Parameters.AddWithValue(arrParameter[i, 0], arrParameter[i, 1]); } myDa_SQL.Fill(dt); myConn_SQL.Dispose(); myDa_SQL.Dispose(); if (dt.Rows.Count > 0 && dt != null) { return dt; } else { dt.Dispose(); return null; } } } else { return null; } }
public System.Data.DataTable Select(string cmd) { try { OdbcDataAdapter da = new OdbcDataAdapter(cmd, GetConn()); //IBM.Data.DB2.DB2DataAdapter da = new IBM.Data.DB2.DB2DataAdapter(cmd, GetConn()); System.Data.DataTable DT = new System.Data.DataTable(); da.Fill(DT); da.Dispose(); return DT; } catch { try { lock (typeof(OdbcConnection)) { GetConn().Close(); GetConn().Open(); OdbcDataAdapter da = new OdbcDataAdapter(cmd, GetConn()); //IBM.Data.DB2.DB2DataAdapter da = new IBM.Data.DB2.DB2DataAdapter(cmd, GetConn()); System.Data.DataTable DT = new System.Data.DataTable(); da.Fill(DT); da.Dispose(); return DT; } } catch (Exception ex) { throw ex; } } }
/// <summary> /// ��Ʈw�d�M��k /// </summary> /// <param name="selectcmd"></param> /// <returns></returns> public System.Data.DataTable select(ICommand selectcmd) { string selectCmd = selectcmd.getCommand(); try { cmd = new OdbcCommand(selectCmd, conn); OdbcDataAdapter da = new OdbcDataAdapter(cmd); System.Data.DataTable DT = new System.Data.DataTable(); da.Fill(DT); da.Dispose(); return DT; } catch { try { conn.Close(); conn.Open(); cmd = new OdbcCommand(selectCmd, conn); OdbcDataAdapter da = new OdbcDataAdapter(cmd); System.Data.DataTable DT = new System.Data.DataTable(); da.Fill(DT); da.Dispose(); return DT; } catch (Exception ex) { throw ex; } } }