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