public void readsequentialyodbc()
{
int iter = 0;
int currentIndex = 0;
int pageSize = 50;
string sql = ConfigurationManager.ConnectionStrings["odbc"].ConnectionString;
string sqltest = ConfigurationManager.ConnectionStrings["frymek"].ConnectionString;
OdbcConnection connection = new OdbcConnection(sql);
string orderSQL = "SELECT * FROM RREV.BGDTBIK order by BIK_ENTIDAD, BIK_CENTRO_ALTA, BIK_CUENTA";
// Assumes that connection is a valid SqlConnection object.
OdbcDataAdapter adapter = new OdbcDataAdapter(orderSQL, connection);
DataSet dataSet = new DataSet();
adapter.Fill(dataSet, currentIndex, pageSize, "Orders");
DataTable dataTable = dataSet.Tables[0];
while (dataTable.Rows.Count > 0)
{
Console.WriteLine("iteration: " + iter++);
foreach (DataRow row in dataTable.Rows)
{
row.SetAdded();
}
BultInsert(pageSize, sqltest, dataTable);
currentIndex += pageSize;
dataSet.Tables["Orders"].Rows.Clear();
adapter.Fill(dataSet, currentIndex, pageSize, "Orders");
dataTable = dataSet.Tables[0];
}
Console.WriteLine(String.Format("done rows: {0} with iteration: {1}", iter * pageSize, iter));
}
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] = "???";
}
}
}
// CSVファイルをDataTableに取得する
private DataTable getDtFromCsv(string csvDir, string csvFileName)
{
DataTable dt = null;
//接続文字列
string conString = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + csvDir + ";Extensions=asc,csv,tab,txt;";
OdbcConnection con = new System.Data.Odbc.OdbcConnection(conString);
string commText = "SELECT * FROM [" + csvFileName + "]";
using (OdbcDataAdapter da = new System.Data.Odbc.OdbcDataAdapter(commText, con))
{
//DataTableに格納する
using (dt = new DataTable())
{
da.Fill(dt);
}
}
if (dt == null)
{
throw new Exception("CSVのテーブル展開に失敗しました。");
}
return(dt);
}
/// <summary>
/// Retorna un datatable con todas las noticias.
/// Cada noticia con el pathmedium de su imagen de portada.
/// </summary>
/// <returns></returns>
public static DataTable getDataTableNoticias()
{
DataTable dataTable = new DataTable();
String query = "SELECT n.id, n.titulo, n.descripcion, n.principal, i.pathBig "+
"FROM noticia n, imagen_x_noticia ixn, imagen i "+
"WHERE ixn.idImagen = i.id AND ixn.idNoticia = n.id AND i.portada = 1";
using (OdbcConnection con = new OdbcConnection(Constantes.CONNECTION_STRING))
{
using (OdbcCommand cmd = new OdbcCommand(query, con))
{
con.Open();
cmd.CommandType = CommandType.Text;
try
{
dataTable = new DataTable();
OdbcDataAdapter adapter = new OdbcDataAdapter();
adapter.SelectCommand = cmd;
adapter.Fill(dataTable);
}
catch (Exception e)
{
//StreamWriter sw = new StreamWriter(HttpContext.Current.Server.MapPath("~") + "log.txt", true);
//sw.WriteLine(e.Message);
//sw.Flush();
//sw.Close();
//throw new SportingException("Ocurrio un problema al intentar obtener todas las noticias. " + e.Message);
throw e;
}
}
}
return dataTable;
}
void abrirdbf(string tabla)
{
strRutaDbf = System.Configuration.ConfigurationSettings.AppSettings["PathDbfs"];
strSelect = "SELECT * FROM " + tabla + ";";
strConexion = "Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=" + strRutaDbf + ";";
OdbcConnection dbConexionDbf = new System.Data.Odbc.OdbcConnection(strConexion);
try
{
dbConexionDbf.Open();
OdbcDataAdapter da = new System.Data.Odbc.OdbcDataAdapter(strSelect, dbConexionDbf);
da.Fill(dsDocumentos, tabla);
dbConexionDbf.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error al abrir la base de datos\n" + ex.Message);
return;
}
finally
{
if (dbConexionDbf != null && oleConexion.State != ConnectionState.Closed)
{
dbConexionDbf.Close();
}
}
}
public static BusinessObjects.Client GetClient(string clientid)
{
DataTable dt = new DataTable();
BusinessObjects.Client c = new BusinessObjects.Client();
string sql = @"SELECT rtrim(ID) ID, TYPE, NAME,[Contact],[Phone],[Fax],[ReferenceNo]
FROM CLIENT_DAT WHERE ID=?";
using (OdbcDataAdapter adp = new OdbcDataAdapter(sql, Properties.Settings.Default.IESDBConn))
{
adp.SelectCommand.Parameters.Add(new OdbcParameter("@clientid", clientid));
adp.Fill(dt);
}
if (dt.Rows.Count > 0)
{
c.ClientID = dt.Rows[0]["ID"].ToString();
c.Type = dt.Rows[0]["TYPE"].ToString();
c.Name = dt.Rows[0]["NAME"].ToString();
c.Contact = dt.Rows[0]["Contact"].ToString();
c.Phone = dt.Rows[0]["Phone"].ToString();
c.Fax = dt.Rows[0]["Fax"].ToString();
c.ReferenceNo = dt.Rows[0]["ReferenceNo"].ToString();
return c;
}
return null;
}
public static List<BusinessObjects.ContainerCargo> GetCargo(int ContainerID)
{
DataTable dt = new DataTable();
string sql = string.Format(@"SELECT * FROM Cargo where ContainerID={0}", ContainerID);
OdbcDataAdapter adp = new OdbcDataAdapter(sql, Properties.Settings.Default.IESDBConn);
adp.Fill(dt);
List<BusinessObjects.ContainerCargo> lc = new List<BusinessObjects.ContainerCargo>();
foreach (DataRow row in dt.Rows)
{
BusinessObjects.ContainerCargo c = new BusinessObjects.ContainerCargo();
c.ID = int.Parse(row["ID"].ToString());
c.Description = row["Description"].ToString();
int p;
int.TryParse(row["Pieces"].ToString(),out p);
c.Pieces = p; p = 0;
c.UOM = row["UOM"].ToString();
int.TryParse(row["GrossWeight"].ToString(), out p);
c.GrossWeight = p; p = 0;
int.TryParse(row["GrossWeight"].ToString(), out p);
c.NetWeight = p;
bool h; bool.TryParse(row["Hazardous"].ToString(),out h);
c.Hazardous = h;
lc.Add(c);
}
return lc;
}
public bool connect(string host, string db, string user, string password)
{
string connectString = "DRIVER={MySQL ODBC 5.1 Driver};" + "SERVER=" + host + ";" + "DATABASE=" + db + ";" + "UID=" + user + ";" + "PASSWORD="******";" + "OPTION=67108867";
//Console.WriteLine("connecting to db: " + connectString);
try
{
connection = new OdbcConnection(connectString);
connection.Open();
//Set the data adapter
adapter = new OdbcDataAdapter();
connected = true;
//Console.WriteLine("connected");
return true;
}
catch (Exception e)
{
Console.WriteLine("Cannot connect to the MySQL server.");
Console.WriteLine(e.StackTrace);
connected = false;
return false;
}
}
public static DataTable getDataTableImagenes(int id)
{
OdbcConnection con = ConexionBD.ObtenerConexion();
DataSet ds = new DataSet();
List<Noticia> listaNoticias = new List<Noticia>();
DataTable dataTable = null;
try
{
OdbcCommand cmd = new OdbcCommand("SELECT i.pathBig, i.pathSmall, i.pathMedium FROM imagen_x_noticia ixn, imagen i" +
" WHERE ixn.idNoticia = " + id + " AND i.id = ixn.idImagen", con);
cmd.CommandType = CommandType.Text;
dataTable = new DataTable();
OdbcDataAdapter adapter = new OdbcDataAdapter();
adapter.SelectCommand = cmd;
adapter.Fill(dataTable);
}
catch (Exception e)
{
throw new SportingException("Ocurrio un problema al intentar obtener las imagenes de las noticias. " + e.Message);
}
finally
{
con.Close();
}
return dataTable;
}
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.odbcDataAdapter = new System.Data.Odbc.OdbcDataAdapter();
this.odbcSelectCommand1 = new System.Data.Odbc.OdbcCommand();
this.odbcInsertCommand1 = new System.Data.Odbc.OdbcCommand();
this.odbcConnection = new System.Data.Odbc.OdbcConnection();
//
// odbcDataAdapter
//
this.odbcDataAdapter.InsertCommand = this.odbcInsertCommand1;
this.odbcDataAdapter.SelectCommand = this.odbcSelectCommand1;
this.odbcDataAdapter.TableMappings.AddRange(new System.Data.Common.DataTableMapping[] {
new System.Data.Common.DataTableMapping("Table", "users", new System.Data.Common.DataColumnMapping[] {
new System.Data.Common.DataColumnMapping("id", "id"),
new System.Data.Common.DataColumnMapping("user", "user"),
new System.Data.Common.DataColumnMapping("password", "password")})});
//
// odbcSelectCommand1
//
this.odbcSelectCommand1.CommandText = "SELECT id, `user`, password FROM users";
this.odbcSelectCommand1.Connection = this.odbcConnection;
//
// odbcInsertCommand1
//
this.odbcInsertCommand1.CommandText = "INSERT INTO users(`user`, password) VALUES (?, ?)";
this.odbcInsertCommand1.Connection = this.odbcConnection;
this.odbcInsertCommand1.Parameters.Add(new System.Data.Odbc.OdbcParameter("user", System.Data.Odbc.OdbcType.VarChar, 30, "user"));
this.odbcInsertCommand1.Parameters.Add(new System.Data.Odbc.OdbcParameter("password", System.Data.Odbc.OdbcType.VarChar, 30, "password"));
//
// odbcConnection
//
this.odbcConnection.ConnectionString = "STMT=;OPTION=3;DSN=test;UID=root;PASSWORD=;DESC=MySQL ODBC 3.51 Driver DSN;DATABA" +
"SE=test;SERVER=localhost;PORT=3306";
}
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(); }
}
[Test] // OdbcDataAdapter (OdbcCommand)
public void Constructor2 ()
{
OdbcCommand cmd = new OdbcCommand ();
OdbcDataAdapter da = new OdbcDataAdapter (cmd);
Assert.IsTrue (da.AcceptChangesDuringFill, "#1");
#if NET_2_0
Assert.IsTrue (da.AcceptChangesDuringUpdate, "#2");
#endif
Assert.IsNull (da.Container, "#3");
Assert.IsFalse (da.ContinueUpdateOnError, "#4");
Assert.IsNull (da.DeleteCommand, "#5");
#if NET_2_0
Assert.AreEqual (LoadOption.OverwriteChanges, da.FillLoadOption, "#6");
#endif
Assert.IsNull (da.InsertCommand, "#7");
Assert.AreEqual (MissingMappingAction.Passthrough, da.MissingMappingAction, "#8");
Assert.AreEqual (MissingSchemaAction.Add, da.MissingSchemaAction, "#9");
#if NET_2_0
Assert.IsFalse (da.ReturnProviderSpecificTypes, "#10");
#endif
Assert.IsNotNull (da.SelectCommand, "#11");
Assert.AreSame (cmd, da.SelectCommand, "#12");
Assert.IsNull (da.Site, "#13");
Assert.IsNotNull (da.TableMappings, "#14");
Assert.AreEqual (0, da.TableMappings.Count, "#15");
#if NET_2_0
Assert.AreEqual (1, da.UpdateBatchSize, "#16");
#endif
Assert.IsNull (da.UpdateCommand, "#17");
}
public static string constr = "DSN=ground_tariff"; //@"Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=d:\documents and settings\axkhan2\desktop\437proj\GHEC Rates NL.xls;DefaultDir=d:\documents and settings\axkhan2\desktop\437proj\;";
static void Main()
{
OdbcConnection conxls = new OdbcConnection(constr);
try
{
conxls.Open();
OdbcDataAdapter da = new OdbcDataAdapter("select * from [Sheet1$]", conxls);
DataSet set = new DataSet();
da.Fill(set, "mytariff");
DataTable table = set.Tables[0];
DataView dv = new DataView(table);
// Console.WriteLine(dv[0]);
//InsertData(rdxls);
}
catch(OdbcException od)
{
Console.WriteLine(od.Message);
}
finally
{
conxls.Close();
}
}
protected void Page_Load(object sender, EventArgs e)
{
// string ImageId = System.Convert.ToString(Request.QueryString["name"]);
int articleID = System.Convert.ToInt32(Request.QueryString["textid"]);
string MyConString = ConfigurationSettings.AppSettings["connectionString"];
OdbcConnection connection = new OdbcConnection(MyConString);
string sqlNews1 = "select * FROM latestnews";
OdbcCommand commandNews1 = new OdbcCommand(sqlNews1, connection);
connection.Open();
OdbcDataReader dr = commandNews1.ExecuteReader(CommandBehavior.CloseConnection);
if (dr.Read())
{
Response.ContentType = dr["date"].ToString();
Response.ContentType = dr["title"].ToString();
Response.ContentType = dr["content"].ToString();
}
connection.Close();
string selectNews = "SELECT date, title, content FROM latestnews ORDER BY date DESC";
OdbcCommand command = new OdbcCommand(selectNews, connection);
OdbcDataAdapter adapter = new OdbcDataAdapter(command);
DataSet ds = new DataSet();
adapter.Fill(ds);
DataList1.DataSource = ds;
DataList1.DataBind();
}
protected void Page_Load(object sender, EventArgs e)
{
string cate = Request.QueryString["category"];
int artID = System.Convert.ToInt32(Request.QueryString["articleID"]);
string MyConString = ConfigurationSettings.AppSettings["connectionString"];
OdbcConnection connection = new OdbcConnection(MyConString);
// build our query statement
string sqlText = "select * FROM articles";
OdbcCommand command = new OdbcCommand(sqlText, connection);
// open the database and get a datareader
connection.Open();
OdbcDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection);
connection.Close();
string selectSQL = "SELECT * FROM articles WHERE articleID = '" + artID + "'";
OdbcCommand cmd1 = new OdbcCommand(selectSQL, connection);
OdbcDataAdapter adapter = new OdbcDataAdapter(cmd1);
DataSet ds = new DataSet();
adapter.Fill(ds);
DataList10.DataSource = ds;
DataList10.DataBind();
}
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;
}
}
/// <summary>
/// Executa uma query de busca com retorno de dados
/// </summary>
/// <param name="sql">comando sql para aquela ação</param>
/// <returns>Um dataset contendo os dados buscados</returns>
public DataSet BuscaDados(string sql)
{
//Instancia um objeto de dataset para retornar
DataSet dados = new DataSet();
using(OdbcConnection conn = new OdbcConnection(this.ConnectionString))
{
try
{
//Abre a conexão com o servidor de banco
conn.Open();
//Cria um objeto de reader do odbc, para retornar dados
OdbcDataAdapter read = new OdbcDataAdapter(sql, conn);
//Preenche o DataSet instanciado no incio do codigo
read.Fill(dados);
}
catch (Exception ex)
{
//Lança um erro, caso aconteça
throw new Exception(ex.Message);
}
finally
{
//Fecha conexão
conn.Close();
}
}
return dados;
}
public static Jugador getJugador_plantelActual(int id)
{
OdbcConnection conexion = null;
OdbcCommand cmd = null;
Jugador jugador = new Jugador();
try
{
conexion = ConexionBD.ObtenerConexion();
String getJugador = "select j.id, j.nombreApellido, j.posicion, j.idPlantel " +
"from jugador j where j.idPlantel = 1 and j.id = " + id;
cmd = new OdbcCommand(getJugador, conexion);
OdbcDataAdapter da = new OdbcDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
jugador.IdJugador = Convert.ToInt32(dt.Rows[0]["id"].ToString());
jugador.NombreApellido = dt.Rows[0]["nombreApellido"].ToString();
jugador.Posicion = dt.Rows[0]["posicion"].ToString();
jugador.Foto = ImagenDAL.getImagenJugador(conexion, id);
}
catch (Exception e)
{
throw new SportingException("Ocurrio un error al intentar obtener los datos de un jugador. " + e.Message);
}
finally
{
cmd.Connection.Close();
}
return jugador;
}
public string getQuotesWS(string currency_name)
{
string connString = "DSN=MySQLODBC;UID=root;PWD=admin";
OdbcConnection conn = new OdbcConnection(connString);
OdbcCommand comm = new OdbcCommand();
comm.Connection = conn;
comm.CommandText = "select CurrencyID from currency where CurrencyName = '" + currency_name + "'";
conn.Open();
OdbcDataAdapter da = new OdbcDataAdapter(comm);
DataSet ds = new DataSet();
da.Fill(ds);
DataRow row = ds.Tables[0].Rows[0];
string currency_id = row[0].ToString();
comm.CommandText = "select currentprice from currencyprediction where CurrencyID = " + currency_id + "";
OdbcDataAdapter da1 = new OdbcDataAdapter(comm);
DataSet ds1 = new DataSet();
da1.Fill(ds1);
DataRow row1 = ds1.Tables[0].Rows[0];
string currentQuotePrice = row1[0].ToString();
return currentQuotePrice;
}
public void synchronizateDataSet()
{
this.SynchronizatedStartTime = DateTime.Now;
OdbcConnection conn = new OdbcConnection();
conn.ConnectionString = this.DBConnectionSetting.ConnectionStr;
try
{
foreach (var item in QuerySet_)
{
OdbcCommand sqlCommand = new OdbcCommand();
conn.Open();
// ------------------------------------------ Position --------------------------------------------
sqlCommand.CommandText = item.Value.Replace("_ReferenceDate_", this.ReferenceDate_.ToString("yyyyMMdd")).ToString();
OdbcDataAdapter dataAdapter = new OdbcDataAdapter(sqlCommand.CommandText, conn);
positionDataSet_.Tables.Add(item.Key);
dataAdapter.Fill(positionDataSet_.Tables[item.Key]);
}
}
catch (Exception e)
{
ErrorManager.setError(e);
}
this.SynchronizatedEndTime = DateTime.Now;
}
public void load_list()
{
try
{
string workerName = "admin"; //Session["USER_ID"].ToString();
string connection = ConfigurationSettings.AppSettings["ConnectionString"];
OdbcConnection dbCon = new OdbcConnection(connection);
dbCon.Open();
string sql = "select count(*) from Board";
OdbcCommand cmd = new OdbcCommand(sql, dbCon);
total = int.Parse(cmd.ExecuteScalar().ToString());
cmd.CommandText = "select * from Board order by headnum DESC, depth ASC";
OdbcDataAdapter dbAdap = new OdbcDataAdapter(cmd);
DataSet ds = new DataSet();
dbAdap.Fill(ds);
dbAdap.Update(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
dbCon.Close();
}
catch { }
}
protected void Button1_Click(object sender, EventArgs e)
{
string[] strArray = new string[5] { "Delhi", "Mumbai", "Kolkata", "Chennai", "Chandigarh" };
foreach (string str in strArray)
{
OdbcConnection oledbConn = new OdbcConnection("DSN=exceldb");
try
{
// Open connection
oledbConn.Open();
// Create OleDbCommand object and select data from worksheet Sheet1
OdbcCommand cmd = new OdbcCommand("SELECT * FROM [Details$]", oledbConn);
//cmd.Parameters.AddWithValue("@city",str);where city=@city
// Create new OleDbDataAdapter
OdbcDataAdapter oleda = new OdbcDataAdapter();
oleda.SelectCommand = cmd;
// Create a DataSet which will hold the data extracted from the worksheet.
// DataSet ds = new DataSet();
DataTable dt = new DataTable();
// Fill the DataSet from the data extracted from the worksheet.
oleda.Fill(dt);
/* if(dt.Rows.Count>0)
{
ID = dt.Rows[0]["ID"].ToString(); //Where ColumnName is the Field from the DB that you want to display
name= dt.Rows[0]["Name"].ToString();
Address = dt.Rows[0]["Address"].ToString();
EmailAddress = dt.Rows[0]["emailaddress"].ToString();
if(EmailAddress!=null)
{
if(EmailAddress==)
}
EmailContent = dt.Rows[0]["emailcontent"].ToString();
}
*/
DataView dv = new DataView(dt);
// dv.Sort = "emailcontents";
// dv.Sort = "Name";
// Bind the data to the GridView
Grdexcel.DataSource = dv;
Grdexcel.DataBind();
// cmd = new OdbcCommand("delete FROM [ter$] where Address like 'M%'", oledbConn);
// cmd.ExecuteNonQuery();
//cmd.CommandType
}
catch (Exception ex)
{
Alert.Show("Sorry");
}
finally
{
// Close connection
oledbConn.Close();
}
}
}
//get all the records of the table
public DataTable databind()
{
try
{
//create data table object
DataTable dt_select_record;
//set up new connection
OdbcConnection con = getconnection();
//open the setup coneection
con.Open();
//cretae datapter
OdbcDataAdapter adapter = new OdbcDataAdapter("select * from student_warning ", con);
dt_select_record = new DataTable();
//fill the datatable
adapter.Fill(dt_select_record);
//close the connection
con.Close();
return dt_select_record;
}
catch
{
return null;
}
}
public static int JumlahSuratKeluar(string _reset_id, string _reset_value)
{
DataTable dt = new DataTable();
StringBuilder sb = new StringBuilder();
if (_reset_id.ToLower() == "kategori".ToLower())
sb.Append("SELECT SQL_NO_CACHE nomor_surat from surat_keluar where kategori='" + _reset_value + "'");
else if (_reset_id.ToLower() == "tkkeamanan".ToLower())
sb.Append("SELECT SQL_NO_CACHE nomor_surat from surat_keluar where tk_keamanan='" + _reset_value + "'");
else if (_reset_id.ToLower() == "daily".ToLower())
sb.Append("SELECT SQL_NO_CACHE nomor_surat from surat_keluar where day('" + _reset_value + "')=day('" + _reset_value +
"') and month('" + _reset_value + "')=month('" + _reset_value + "') and year('" + _reset_value + "')=year('" + _reset_value + "')");
else if (_reset_id.ToLower() == "monthly".ToLower())
sb.Append("SELECT SQL_NO_CACHE nomor_surat from surat_keluar where month('" + _reset_value + "')=month('" + _reset_value +
"') and year('" + _reset_value + "')=year('" + _reset_value + "')");
else if (_reset_id.ToLower() == "yearly".ToLower())
sb.Append("SELECT SQL_NO_CACHE nomor_surat from surat_keluar where year('" + _reset_value + "')=year('" + _reset_value + "')");
else
sb.Append("SELECT SQL_NO_CACHE nomor_surat from surat_keluar");
OdbcDataAdapter da = new OdbcDataAdapter(sb.ToString(), T8Application.DBConnection);
da.Fill(dt);
return dt.Rows.Count;
}
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();
}
}
}
protected void Button1_Click(object sender, EventArgs e)
{
string ConnectionString = "";
// ConnectionString =
//@"DRIVER=MapR Drill ODBC Driver;
//AdvancedProperties={CastAnyToVarchar=true;HandshakeTimeout=5;QueryTimeout=180;TimestampTZDisplayTimezone=local;ExcludedSchemas=sys,INFORMATION_SCHEMA;NumberOfPrefetchBuffers=5};
//Catalog=DRILL;
//Schema=;
//AuthenticationType=No Authentication;
//ConnectionType=Direct;
//Host=192.168.2.113;
//Port=31010;
//";
// ConnectionString = "DRIVER=MapR Drill ODBC Driver;Catalog=DRILL;Schema=hbase;ConnectionType=Direct;Host=192.168.2.113;Port=31010";
ConnectionString = "DRIVER =MapR Drill ODBC Driver; AdvancedProperties ={ CastAnyToVarchar = true; HandshakeTimeout = 5; QueryTimeout = 180; TimestampTZDisplayTimezone = local; ExcludedSchemas = sys,INFORMATION_SCHEMA; NumberOfPrefetchBuffers = 5}; Catalog = DRILL; Schema =; AuthenticationType = No Authentication; ConnectionType = ZooKeeper; ZKQuorum = dataNode04:2181,dataNode03: 2181,dataNode02: 2181,nameNode: 2181; ZKClusterID = drillbits1; ";
System.Data.Odbc.OdbcConnection conn = new System.Data.Odbc.OdbcConnection(ConnectionString);
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "SELECT * FROM `hbase`.`tab` ";
System.Data.Odbc.OdbcDataAdapter ad = new System.Data.Odbc.OdbcDataAdapter(cmd);
DataSet myds = new DataSet();
ad.Fill(myds);
}
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(); }
}
private IDbDataAdapter GetAdapter()
{
IDbDataAdapter adapter = null;
switch (this.ProviderType)
{
// case ProviderType.MySql:
// adapter = new mysql.MySqlDataAdapter();
// break;
case ProviderType.Odbc:
adapter = new odbc.OdbcDataAdapter();
break;
case ProviderType.OleDb:
adapter = new oledb.OleDbDataAdapter();
break;
// case ProviderType.Oracle:
// adapter = new ora.OracleDataAdapter();
// break;
case ProviderType.Sql:
adapter = new sql.SqlDataAdapter();
break;
}
return(adapter);
}
public static DataTable ExecuteQuery(string dsnConnection, string query, params object[] queryParams)
{
try
{
using (var connection = new OdbcConnection(dsnConnection))
{
connection.Open();
var queryCommand = connection.CreateCommand();
queryCommand.CommandText = query;
queryCommand.CommandTimeout = 1;
var i = 0;
foreach (var queryParam in queryParams)
{
queryCommand.Parameters.Add("@p" + i, OdbcType.DateTime).Value = queryParam;
i++;
}
var dataSet = new DataSet();
var da = new OdbcDataAdapter(queryCommand);
da.Fill(dataSet);
return dataSet.Tables[0];
}
} catch(Exception ex)
{
Logging.LogError(1, "Error executing query {0} on connection {1} message is {2}", query, dsnConnection, ex.Message);
throw;
}
}
//get all records
public DataTable getall()
{
try
{
DataTable dt_all;
//create new connection with the databse
OdbcConnection conn = getconnection();
//open the created connection
conn.Open();
//declare dataadpter variable and hold the values
OdbcDataAdapter adapter = new OdbcDataAdapter("select * from school_events ", conn);
//create new data tbale object
dt_all = new DataTable();
//fill the data table object through data adapter
adapter.Fill(dt_all);
//close the open clonnection
conn.Close();
//return the filled data table object
return dt_all;
}
catch (Exception ss)
{
string s = ss.Message;
return null;
}
}
/// <summary>
/// initialization casting for InitializeDataAccess()
/// </summary>
/// <param name="type"></param>
/// <param name="ConnectionString"></param>
/// <param name="Query"></param>
private void castProvider(ProviderType type, string ConnectionString, string Query = null)
{
switch (type)
{
case ProviderType.Oledb:
conn = new OleDbConnection(ConnectionString);
cmd = new OleDbCommand(Query, (OleDbConnection)conn);
da = new OleDbDataAdapter();
break;
case ProviderType.Odbc:
conn = new OdbcConnection(ConnectionString);
cmd = new OdbcCommand(Query, (OdbcConnection)conn);
da = new OdbcDataAdapter();
break;
case ProviderType.SqlClient:
conn = new SqlConnection(ConnectionString);
cmd = new SqlCommand(Query, (SqlConnection)conn);
da = new SqlDataAdapter();
break;
//case ProviderType.OracleClient:
// conn = new OracleConnection(ConnectionString);
// cmd = new OracleCommand(Query,(OracleConnection)conn);
// break;
}
}
public string getPredictionWS(string currency_name)
{
string connString = "DSN=MySQLODBC;UID=root;PWD=admin";
OdbcConnection conn = new OdbcConnection(connString);
OdbcCommand comm = new OdbcCommand();
comm.Connection = conn;
comm.CommandTimeout = 300;
comm.CommandText = "select CurrencyID from currency where CurrencyName = '" + currency_name + "'";
conn.Open();
OdbcDataAdapter da = new OdbcDataAdapter(comm);
DataSet ds = new DataSet();
da.Fill(ds);
DataRow row = ds.Tables[0].Rows[0];
string currency_id = row[0].ToString();
comm.CommandText = "select predictsignal,predictprice,recognitionrate from currencyprediction where CurrencyID = " + currency_id + "";
OdbcDataAdapter da1 = new OdbcDataAdapter(comm);
DataSet ds1 = new DataSet();
da1.Fill(ds1);
string predictionarray;
predictionarray = ds1.Tables[0].Rows[0].ItemArray[0] + "," + ds1.Tables[0].Rows[0].ItemArray[1] + "," + ds1.Tables[0].Rows[0].ItemArray[2];
return predictionarray;
}
/// <summary>
/// before create a table, check if it exists or not
/// </summary>
/// <param name="tableName"></param>
/// <returns></returns>
public bool IsTableExist(DbConnect dbConnect, string tableName)
{
if (!dbConnect.IsConnected())
{
dbConnect.ConnectToDatabase();
}
OdbcCommand showTablesCommand = dbConnect.CreateCommand();
showTablesCommand.CommandText = @"SELECT RDB$RELATION_NAME FROM RDB$RELATIONS WHERE RDB$SYSTEM_FLAG = 0;";
DataTable tableNamesTable = new DataTable("Table Names");
System.Data.Odbc.OdbcDataAdapter adapter = new System.Data.Odbc.OdbcDataAdapter();
try
{
adapter.SelectCommand = showTablesCommand;
adapter.Fill(tableNamesTable);
}
catch
{
showTablesCommand.CommandText = "Show tables";
adapter.SelectCommand = showTablesCommand;
adapter.Fill(tableNamesTable);
}
foreach (DataRow dRow in tableNamesTable.Rows)
{
if (dRow[0].ToString().ToLower().Trim() == tableName.ToLower())
{
return(true);
}
}
showTablesCommand.Dispose();
return(false);
}
//retrive all the grades from the database
public DataTable get_all_grades()
{
try
{
DataTable dt_serachresult;
//create new connection with the databse
OdbcConnection conn = getconnection();
//open the created connection
conn.Open();
//declare dataadpter variable and hold the values
OdbcDataAdapter adapter = new OdbcDataAdapter("select distinct SC_GRADE from student_class ", conn);
//create new data tbale object
dt_serachresult = new DataTable();
//fill the data table object through data adapter
adapter.Fill(dt_serachresult);
//close the open clonnection
conn.Close();
//return the filled data table object
return dt_serachresult;
}
catch
{
return null;
}
}
public DataSet OpenCsv(string strFilePath)
{
if (!File.Exists(strFilePath))
{
return null;
}
string strFolderPath = Path.GetDirectoryName(strFilePath);
string strCSVFile = Path.GetFileName(strFilePath);
DataSet ds = null;
string strConnection = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + strFolderPath + ";Extensions=asc,csv,tab,txt;Persist Security Info=False";
try
{
using (OdbcConnection conn = new OdbcConnection(strConnection.Trim()))
{
conn.Open();
string strSql = "select * from [" + strCSVFile + "]";
OdbcDataAdapter odbcDAdapter = new OdbcDataAdapter(strSql, conn);
ds = new DataSet();
odbcDAdapter.Fill(ds, "table");
//ds.Tables[0].Rows[0]["zh"] = "12345678901234567890";
conn.Close();
}
return ds;
}
catch (Exception e)
{
throw e;
}
return ds;
}
public void LoadDB()
{
_MCDB = new DataSet();
try
{
// Creates and opens an ODBC connection
String strConnString = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + _DBPath.Trim() + ";Extensions=asc,csv,tab,txt;Persist Security Info=False;HDR=NO;";
String sql_select;
OdbcConnection conn;
conn = new OdbcConnection(strConnString.Trim());
conn.Open();
//Creates the select command text
sql_select = "select * from [" + this._DBFileName.Trim() + "]";
//Creates the data adapter
OdbcDataAdapter obj_oledb_da = new OdbcDataAdapter(sql_select, conn);
//Fills dataset with the records from CSV file
obj_oledb_da.Fill(_MCDB, "csv");
//closes the connection
conn.Close();
}
catch (Exception e) //Error
{
Console.WriteLine("Error in: " + e.ToString());
}
}
public DataTable ExecuteSQLQuery(string sQuery, string sCompanyCode, OdbcParameter[] param)
{
string sFuncName = "ExecuteSQLQuery()";
string sConstr = ConfigurationManager.ConnectionStrings["DBConnection"].ToString();
string[] sArray = sConstr.Split(';');
string sSplitCompany = sConstr.Split(';').Last();
string sSplit1 = sSplitCompany.Split('=').First();
string sCompanyGenerate = sSplit1 + "=" + sCompanyCode;
sConstr = sArray[0] + ";" + sArray[1] + ";" + sArray[2] + ";" + sArray[3] + ";" + sCompanyGenerate;
System.Data.Odbc.OdbcConnection oCon = new System.Data.Odbc.OdbcConnection(sConstr);
System.Data.Odbc.OdbcCommand oCmd = new System.Data.Odbc.OdbcCommand();
DataSet oDs = new DataSet();
try
{
oCon.Open();
oCmd.CommandType = CommandType.Text;
oCmd.CommandText = sQuery;
if (p_iDebugMode == DEBUG_ON)
{
oLog.WriteToDebugLogFile("SQL Query : " + sQuery, sFuncName);
}
if (p_iDebugMode == DEBUG_ON)
{
oLog.WriteToDebugLogFile("Before adding Parameters", sFuncName);
}
foreach (var item in param)
{
oCmd.Parameters.Add(item);
}
if (p_iDebugMode == DEBUG_ON)
{
oLog.WriteToDebugLogFile("After adding parameters", sFuncName);
}
oCmd.Connection = oCon;
oCmd.CommandTimeout = 120;
System.Data.Odbc.OdbcDataAdapter da = new System.Data.Odbc.OdbcDataAdapter(oCmd);
da.Fill(oDs);
oCon.Close();
if (p_iDebugMode == DEBUG_ON)
{
oLog.WriteToDebugLogFile("Completed with SUCCESS", sFuncName);
}
}
catch (Exception ex)
{
if (p_iDebugMode == DEBUG_ON)
{
oLog.WriteToDebugLogFile("Completed with ERROR", sFuncName);
}
oCon.Dispose();
throw new Exception(ex.Message);
}
return(oDs.Tables[0]);
}
public DataTable ExecuteNonQuery(string sQuery, OdbcParameter[] param)
{
string sFuncName = "ExecuteNonQuery";
if (p_iDebugMode == DEBUG_ON)
{
oLog.WriteToDebugLogFile("Starting Function", sFuncName);
}
string sConstr = ConfigurationManager.ConnectionStrings["DBConnection"].ToString();
System.Data.Odbc.OdbcConnection oCon = new System.Data.Odbc.OdbcConnection(sConstr);
System.Data.Odbc.OdbcCommand oCmd = new System.Data.Odbc.OdbcCommand();
DataSet oDs = new DataSet();
try
{
oCon.Open();
oCmd.CommandType = CommandType.Text;
oCmd.CommandText = sQuery;
if (p_iDebugMode == DEBUG_ON)
{
oLog.WriteToDebugLogFile("SQL Query : " + sQuery, sFuncName);
}
if (p_iDebugMode == DEBUG_ON)
{
oLog.WriteToDebugLogFile("Before adding Parameters", sFuncName);
}
foreach (var item in param)
{
oCmd.Parameters.Add(item);
}
if (p_iDebugMode == DEBUG_ON)
{
oLog.WriteToDebugLogFile("After adding parameters", sFuncName);
}
oCmd.Connection = oCon;
oCmd.CommandTimeout = 120;
System.Data.Odbc.OdbcDataAdapter da = new System.Data.Odbc.OdbcDataAdapter(oCmd);
da.Fill(oDs);
oCon.Close();
if (p_iDebugMode == DEBUG_ON)
{
oLog.WriteToDebugLogFile("Completed with SUCCESS", sFuncName);
}
}
catch (Exception ex)
{
if (p_iDebugMode == DEBUG_ON)
{
oLog.WriteToDebugLogFile("Completed with ERROR", sFuncName);
}
oCon.Dispose();
throw new Exception(ex.Message);
}
return(oDs.Tables[0]);
}
}// readTableDetails
public bool readTableContent(string TableName, ref DataTable TableData, OdbcConnection conn)
{
bool RetValue = true;
try
{
OdbcCommand odbcCommand1 = new OdbcCommand();
odbcCommand1.CommandText = "SELECT * FROM " + TableName + ";";
odbcCommand1.Connection = conn;
//odbcCommand1.
System.Data.Odbc.OdbcDataAdapter adapt = new System.Data.Odbc.OdbcDataAdapter(odbcCommand1);
adapt.SelectCommand = odbcCommand1;
adapt.Fill(TableData);
// to verify its existence
// odbcCommand1.CommandText = "SELECT * FROM all_objects WHERE object_type in ('TABLE', 'VIEW') and object_name = '"+ TableName +"';";
//System.Data.Odbc.OdbcDataAdapter adapt = new System.Data.Odbc.OdbcDataAdapter("SELECT * FROM " + TableName, conn);
//adapt.ContinueUpdateOnError = true;
//adapt.Fill(TableData);
}
catch (OdbcException odbcEx)
{
MessageBox.Show(
"Message: " + odbcEx.Message + ""
+ "Source: " + odbcEx.Source + "\r\n"
+ "InnerException: " + odbcEx.InnerException + "\r\n"
+ "TargetSite: " + odbcEx.TargetSite + "\r\n"
+ "StackTrace: " + odbcEx.StackTrace,
"OdbcException", MessageBoxButtons.OK, MessageBoxIcon.Error);
RetValue = false;
}
catch (System.FormatException formatEx)
{
MessageBox.Show(
"Message: " + formatEx.Message + ""
+ "Source: " + formatEx.Source + "\r\n"
+ "InnerException: " + formatEx.InnerException + "\r\n"
+ "TargetSite: " + formatEx.TargetSite + "\r\n"
+ "StackTrace: " + formatEx.StackTrace,
"FormatException", MessageBoxButtons.OK, MessageBoxIcon.Error);
RetValue = false;
}
catch (Exception ex)
{
MessageBox.Show(
"Message: " + ex.Message + ""
+ "Source: " + ex.Source + "\r\n"
+ "InnerException: " + ex.InnerException + "\r\n"
+ "TargetSite: " + ex.TargetSite + "\r\n"
+ "StackTrace: " + ex.StackTrace,
"Exception", MessageBoxButtons.OK, MessageBoxIcon.Error);
RetValue = false;
}
return(RetValue);
}// readTableDetails
protected void btnOK_Click(object sender, EventArgs e)
{
//System.Data.Odbc.OdbcConnection conn = new System.Data.Odbc.OdbcConnection ();
System.Data.Odbc.OdbcConnection conn;
System.Data.Odbc.OdbcDataAdapter ap;
System.Data.Odbc.OdbcDataReader apcheck;
System.Data.Odbc.OdbcCommand objcommand;
objcommand = new System.Data.Odbc.OdbcCommand();
DataSet ds = new DataSet();
//string connectionString = "Data Source=(local);Initial Catalog=Northwind; User ID=Admin; Password=misdyu";
string constr = "FIL=MS Access;SERVER=localhost;DSN=Pform;UID=root;PWD=;OPTION=3";
conn = new System.Data.Odbc.OdbcConnection(constr);
conn.Open();
//採用ODBC，則禁止使用sqlcommand相關指令，會出現語法錯誤，一直抓不到bug
objcommand.Connection = conn;
objcommand.CommandText = "Select * From accountID Where Name = '" + TextBox1.Text + "'";
try
{
// Process data here.
apcheck = objcommand.ExecuteReader();
if (apcheck.HasRows)
{
Label1.Text = "帳號：該帳戶己註冊過";
apcheck.Close();
conn.Close();
}
else
{
conn.Close();
conn = new System.Data.Odbc.OdbcConnection(constr);
conn.Open();
string sql1 = "Insert into accountID (Name, Pass, Email, Address, Gender, Idt) values ('" + TextBox1.Text + "', '" + TextBox2.Text + "', '" + TextBox4.Text + "' , '" + TextBox3.Text + "' , '" + RadioButtonList1.Text + "', '" + DropDownList1.Text + "')";
ap = new System.Data.Odbc.OdbcDataAdapter(sql1, conn);
ap.Fill(ds);
//傳統寫法
Response.Redirect("ShowData.aspx?ID=" + TextBox1.Text + "&Password="******"&Msg=" + DropDownList1.Text);
//使用字串參數的型式,ERROR會出現兩次註冊
//Response.Redirect(string.Format("ShowData.aspx?ID={0}&Password={1}&Msg={2}", TextBox1.Text, TextBox2.Text, DropDownList1.Text));
}
}
catch (Exception ex)
{
//新版不支援下行
// MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
}
private void btnSearch_Click(object sender, EventArgs e)
{
string strSQL = "";
string varCode = "";
string varName = "";
string varPhys = "";
if (txtPatientID.Text.Length > 0)
{
varCode = "%" + txtPatientID.Text + "%";
}
if (txtFirstName.Text.Length > 0)
{
varName = "%" + txtFirstName.Text + "%";
}
if (txtReferringMD.Text.Length > 0)
{
varPhys = "%" + txtReferringMD.Text + "%";
}
if (chkSearchDate.Checked)
{
strSQL = "SELECT * FROM PatientStudyView WHERE (PatientID LIKE '" + varCode + "' OR PatientName_DICOM LIKE '" + varName + "' or ReferringPhysician_DICOM like '" + varPhys + "') and convert(date,StudyDate) BETWEEN '" + dtpDateFrom.Value.ToString("yyyy-MM-dd") + "' and '" + dtpDateTo.Value.ToString("yyyy-MM-dd") + "' order by StudyDate desc";
}
else
{
strSQL = "SELECT * FROM PatientStudyView WHERE PatientID LIKE '" + varCode + "' OR PatientName_DICOM LIKE '" + varName + "' or ReferringPhysician_DICOM like '" + varPhys + "' order by StudyDate desc";
}
if (string.IsNullOrEmpty(varCode) && string.IsNullOrEmpty(varName) && string.IsNullOrEmpty(varPhys) && chkSearchDate.Checked)
{
strSQL = "SELECT * FROM PatientStudyView WHERE convert(date,StudyDate) BETWEEN '" + dtpDateFrom.Value.ToString("yyyy-MM-dd") + "' and '" + dtpDateTo.Value.ToString("yyyy-MM-dd") + "' order by StudyDate desc";
}
CN1.OpenConnection();
CMD1 = new System.Data.Odbc.OdbcCommand(strSQL, CN1.DBConnection);
//Dim dtst As New DataSet
DicomServerDBDataSet1.PatientStudyViewDataTable table = new DicomServerDBDataSet1.PatientStudyViewDataTable();
System.Data.Odbc.OdbcDataAdapter adptr = new System.Data.Odbc.OdbcDataAdapter();
adptr.SelectCommand = CMD1;
adptr.Fill(table);
DataGridView1.Refresh();
DataGridView1.DataSource = null;
DataGridView1.DataSource = table;
this.Cursor = Cursors.Default;
GridDesign();
CMD1.Dispose();
CN1.closeconnection();
}
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();
}
}
private void btnYesterday_Click(object sender, EventArgs e)
{
CN1.OpenConnection();
CMD1 = new System.Data.Odbc.OdbcCommand("select * from PatientStudyView where datepart(dd,StudyDate)=" + DateTime.Now.AddDays(-1).Day.ToString() + " and datepart(MM,StudyDate)=" + DateTime.Now.AddDays(-1).Month.ToString() + " and datepart(yy,StudyDate)=" + DateTime.Now.AddDays(-1).Year.ToString() + "", CN1.DBConnection);
//Dim dtst As New DataSet
DicomServerDBDataSet1.PatientStudyViewDataTable table = new DicomServerDBDataSet1.PatientStudyViewDataTable();
System.Data.Odbc.OdbcDataAdapter adptr = new System.Data.Odbc.OdbcDataAdapter();
adptr.SelectCommand = CMD1;
adptr.Fill(table);
DataGridView1.Refresh();
DataGridView1.DataSource = null;
DataGridView1.DataSource = table;
this.Cursor = Cursors.Default;
GridDesign();
CMD1.Dispose();
CN1.closeconnection();
}
public static DataTable ReadDbf(string filePath, string tableName)
{
string fileName = Path.GetFileName(filePath);
filePath = Path.GetDirectoryName(filePath);
OdbcConnection conn = null;
string connectStr = "Driver={Microsoft dBASE Driver (*.dbf)}; Dbq=" + filePath;
conn = new System.Data.Odbc.OdbcConnection(connectStr);
string sql = "select * from " + fileName;
System.Data.Odbc.OdbcDataAdapter da = new System.Data.Odbc.OdbcDataAdapter(sql, conn);
conn.Open();
DataTable dt = new DataTable();
da.Fill(dt);
conn.Close();
return(dt);
}
public void ReadDataBase()
{
System.Data.Odbc.OdbcDataAdapter dbAdapter = new System.Data.Odbc.OdbcDataAdapter();
dbAdapter.SelectCommand = this.dbCommand;
DataSet dbDataSet = new DataSet();
ushort dimL = 0;
string strSQL;
strSQL = "SELECT Name,Code FROM DisplayDefination ORDER BY Code";
dbCommand.CommandText = strSQL;
string strTableName = "DisplayDefination";
dbAdapter.Fill(dbDataSet, strTableName);
dimL = (ushort)dbDataSet.Tables[strTableName].Rows.Count;
for (ushort i = 0; i < dimL; i++)
{
string strCode = dbDataSet.Tables[strTableName].Rows[i]["Code"].ToString();
string[] strParameters = dbDataSet.Tables[strTableName].Rows[i]["Name"].ToString().Split('-');
string strName = strParameters[1];
ArchiveParameters.Add(strName, strCode);
}
}
private void LoadView()
{
System.Data.Odbc.OdbcConnection OdbcConnection = new System.Data.Odbc.OdbcConnection(ConfigurationManager.ConnectionStrings["TurningpointSystem"].ConnectionString);
try
{
DataTable dt = new DataTable();
dg = item_list;
StringBuilder sCSVBuilder = new StringBuilder();
System.Data.Odbc.OdbcCommand oCmd = new System.Data.Odbc.OdbcCommand();
OdbcConnection.Open();
oCmd.Connection = OdbcConnection;
string selectCmd;
if (ConfigurationManager.AppSettings["RX_V6"] == "yes")
{
selectCmd = String.Format(
" SELECT distinct A.ORDTIT, 0 as ORUSIT, A.OTCACT, '' AS EXTPRICE, '' AS RETAIL, A.ORDTSQ, A.ORDTQT, A.ORDTSP, '' as ORPKCT, B.*, 0 as OCLNX$" +
" FROM FPORDDTL A LEFT JOIN FPITMMAS B ON A.ORDTIT = B.ITMNUM" +
//" LEFT JOIN FPORDCRD C ON C.OCDOC# = A.OTINV# AND C.OCDLNE = A.ORDTLN" +
" WHERE A.OTINV# = {0} ", invoiceNo);
}
else if (ConfigurationManager.AppSettings["RX"] == "yes")
{
selectCmd = String.Format(
" SELECT distinct A.ORDTIT, 0 as ORUSIT, A.OTCACT, '' AS EXTPRICE, '' AS RETAIL, A.ORDTSQ, A.ORDTQT, A.ORDTSP, '' as ORPKCT, B.*, C.OCLNX$" +
" FROM FPORDDTL A LEFT JOIN FPITMMAS B ON A.ORDTIT = B.ITMNUM" +
" LEFT JOIN FPORDCRD C ON C.OCDOC# = A.OTINV# AND C.OCDLNE = A.ORDTLN" +
" WHERE A.OTINV# = {0} ", invoiceNo);
}
else
{
selectCmd = String.Format(
" SELECT distinct A.ORDTIT, A.ORUSIT, A.OTCACT, '' AS EXTPRICE, '' AS RETAIL, A.ORDTSQ, A.ORDTQT, A.ORDTSP, A.ORPKCT, B.*, C.OCLNX$" +
" FROM FPORDDTL A LEFT JOIN FPITMMAS B ON A.ORDTIT = B.ITMNUM" +
" LEFT JOIN FPORDCRD C ON C.OCDOC# = A.OTINV# AND C.OCDLNE = A.ORDTLN" +
" WHERE A.OTINV# = {0} ", invoiceNo);
}
oCmd.CommandText = selectCmd;
System.Data.Odbc.OdbcDataAdapter da = new System.Data.Odbc.OdbcDataAdapter(oCmd);
da.Fill(dt);
sCSVBuilder.Append("Invoice No, Invoice Date, Amount\n");
sCSVBuilder.Append(invoiceNo + "," + invoiceDate + "," + amount.Replace("$", "") + "\n\n");
for (int k = 0; k <= dg.Columns.Count - 1; k++)
{
if (ConfigurationManager.AppSettings["RX"] == "yes")
{
if ((k != 2) && (k != 9))
{
sCSVBuilder.Append(dg.Columns[k].HeaderText + ",");
}
}
else
{
if (k != 3)
{
sCSVBuilder.Append(dg.Columns[k].HeaderText + ",");
}
}
}
sCSVBuilder.Append("\n");
for (int i = 0; i <= dt.Rows.Count - 1; ++i)
{
DataRow dr = dt.Rows[i];
if (dr["ITINVT"].ToString().Trim() == "Z")
{
dr["ORDTIT"] = dr["ORUSIT"];
}
double extPrice = 0;
try
{
extPrice = Convert.ToDouble(dr["ORDTSQ"]) * Convert.ToDouble(dr["OTCACT"]);
}
catch
{
}
dr["EXTPRICE"] = String.Format("{0:f}", extPrice);
double retailPrice = 0;
try
{
retailPrice = Convert.ToDouble(dr["ORDTSP"]) / Convert.ToDouble(dr["ORPKCT"]);
}
catch
{
}
dr["RETAIL"] = String.Format("{0:f}", retailPrice);
sCSVBuilder.Append(dr["ORDTIT"].ToString() + ",");
sCSVBuilder.Append("\"" + dr["ITDESC"].ToString().Trim() + "\"" + ",");
if (ConfigurationManager.AppSettings["RX"] == "yes")
{
if (dr["ITMNDC"].ToString().Trim() == "")
{
sCSVBuilder.Append("\"\"" + ",");
}
else
{
sCSVBuilder.Append("\"" + dr["ITMNDC"].ToString().Trim() + " *\"" + ",");
}
}
else
{
if (dr["ITMUPC"].ToString().Trim() == "")
{
sCSVBuilder.Append("\"\"" + ",");
}
else
{
sCSVBuilder.Append("\"" + dr["ITMUPC"].ToString().Trim() + " *\"" + ",");
}
}
sCSVBuilder.Append(dr["OTCACT"].ToString() + ",");
sCSVBuilder.Append(dr["EXTPRICE"].ToString() + ",");
sCSVBuilder.Append(dr["ITSIZE"].ToString() + ",");
sCSVBuilder.Append(dr["ORDTQT"].ToString() + ",");
sCSVBuilder.Append(dr["ORDTSQ"].ToString() + ",");
if (ConfigurationManager.AppSettings["RX"] != "yes")
{
sCSVBuilder.Append(dr["RETAIL"].ToString() + ",");
}
sCSVBuilder.Append("\n");
}
sCSV = sCSVBuilder.ToString();
DataView dv = new DataView(dt);
dg.DataSource = dv;
dg.DataBind();
}
catch (Exception ex) {
}
finally {
OdbcConnection.Close();
}
}
private void btnYesterday_Click(object sender, EventArgs e)
{
string sqlQuery = "";
if (chkAll.Checked)
{
sqlQuery = "select * from PatientStudyView where datepart(dd,StudyDate)=" + DateTime.Now.AddDays(-1).Day.ToString() + " and datepart(MM,StudyDate)=" + DateTime.Now.AddDays(-1).Month.ToString() + " and datepart(yy,StudyDate)=" + DateTime.Now.AddDays(-1).Year.ToString() + "";
}
else
{
string Mod = "";
bool MoreThanOne = false;
if (chkCR.Checked)
{
Mod = "Modality='CR'";
MoreThanOne = true;
}
if (chkCT.Checked)
{
if (MoreThanOne)
{
Mod = Mod + " OR Modality='CT'";
}
else
{
Mod = "Modality='CT'";
}
MoreThanOne = true;
}
if (chkDX.Checked)
{
if (MoreThanOne)
{
Mod = Mod + " OR Modality='DX'";
}
else
{
Mod = "Modality='DX'";
}
MoreThanOne = true;
}
if (chkES.Checked)
{
if (MoreThanOne)
{
Mod = Mod + " OR Modality='ES'";
}
else
{
Mod = "Modality='ES'";
}
MoreThanOne = true;
}
if (chkMG.Checked)
{
if (MoreThanOne)
{
Mod = Mod + " OR Modality='MG'";
}
else
{
Mod = "Modality='MG'";
}
MoreThanOne = true;
}
if (chkMR.Checked)
{
if (MoreThanOne)
{
Mod = Mod + " OR Modality='MR'";
}
else
{
Mod = "Modality='MR'";
}
MoreThanOne = true;
}
if (chkNM.Checked)
{
if (MoreThanOne)
{
Mod = Mod + " OR Modality='NM'";
}
else
{
Mod = "Modality='NM'";
}
MoreThanOne = true;
}
if (chkOT.Checked)
{
if (MoreThanOne)
{
Mod = Mod + " OR Modality='OT'";
}
else
{
Mod = "Modality='OT'";
}
MoreThanOne = true;
}
if (chkPT.Checked)
{
if (MoreThanOne)
{
Mod = Mod + " OR Modality='PT'";
}
else
{
Mod = "Modality='PT'";
}
MoreThanOne = true;
}
if (chkRF.Checked)
{
if (MoreThanOne)
{
Mod = Mod + " OR Modality='RF'";
}
else
{
Mod = "Modality='RF'";
}
MoreThanOne = true;
}
if (chkRT.Checked)
{
if (MoreThanOne)
{
Mod = Mod + " OR Modality='RT'";
}
else
{
Mod = "Modality='RT'";
}
MoreThanOne = true;
}
if (chkSC.Checked)
{
if (MoreThanOne)
{
Mod = Mod + " OR Modality='SC'";
}
else
{
Mod = "Modality='SC'";
}
MoreThanOne = true;
}
if (chkUS.Checked)
{
if (MoreThanOne)
{
Mod = Mod + " OR Modality='US'";
}
else
{
Mod = "Modality='US'";
}
MoreThanOne = true;
}
if (chkXA.Checked)
{
if (MoreThanOne)
{
Mod = Mod + " OR Modality='XA'";
}
else
{
Mod = "Modality='XA'";
}
MoreThanOne = true;
}
sqlQuery = "select * from PatientStudyView where datepart(dd,StudyDate)=" + DateTime.Now.AddDays(-1).Day.ToString() + " and datepart(MM,StudyDate)=" + DateTime.Now.AddDays(-1).Month.ToString() + " and datepart(yy,StudyDate)=" + DateTime.Now.AddDays(-1).Year.ToString() + " AND (" + Mod + ")";
}
CN1.OpenConnection();
CMD1 = new System.Data.Odbc.OdbcCommand(sqlQuery, CN1.DBConnection);
//Dim dtst As New DataSet
DicomServerDBDataSet1.PatientStudyViewDataTable table = new DicomServerDBDataSet1.PatientStudyViewDataTable();
System.Data.Odbc.OdbcDataAdapter adptr = new System.Data.Odbc.OdbcDataAdapter();
adptr.SelectCommand = CMD1;
adptr.Fill(table);
DataGridView1.Refresh();
DataGridView1.DataSource = null;
DataGridView1.DataSource = table;
this.Cursor = Cursors.Default;
GridDesign();
CMD1.Dispose();
CN1.closeconnection();
}
private OdbcDataAdapter(OdbcDataAdapter from) : base(from)
{
GC.SuppressFinalize(this);
}
private OdbcDataAdapter(OdbcDataAdapter adapter) : base(adapter) // MDAC 81448
{
}
private void LoadItems(string sCustNo, string sDateStart, string sDateEnd, string sBrandFamily, double dTargetPct)
{
System.Data.Odbc.OdbcConnection odbcConnection = new System.Data.Odbc.OdbcConnection(ConfigurationManager.ConnectionStrings["TurningpointSystem"].ConnectionString);
OdbcDataReader odbcDataReader = null;
string selectCmd =
" SELECT I.ITVSUF, G.GREFDT, 0 AS TOTAL, '' AS PCT, 0 AS SALES, 0 AS CREDITS, " +
" (SELECT SUM(S.WKQTY) FROM FPSLSBRN S" +
" LEFT JOIN FPITMMAS I1 ON S.WKITEM = I1.ITMNUM" +
" WHERE S.WKSHIP = " + sCustNo +
" AND S.WKTYPE = 'S' " +
" AND I1.ITVSUF = I.ITVSUF " +
" AND S.WKDATE BETWEEN " + sDateStart + " AND " + sDateEnd +
" AND I1.ITINVT IN (SELECT GREFKY FROM FPSYGREF WHERE GREFCD = 'WP')) AS SALES_COUNT, " +
" (SELECT SUM(C.WKQTY) FROM FPSLSBRN C" +
" LEFT JOIN FPITMMAS I2 ON C.WKITEM = I2.ITMNUM" +
" WHERE C.WKSHIP = " + sCustNo +
" AND C.WKTYPE = 'C' " +
" AND I2.ITVSUF = I.ITVSUF " +
" AND C.WKDATE BETWEEN " + sDateStart + " AND " + sDateEnd +
" AND I2.ITINVT IN (SELECT GREFKY FROM FPSYGREF WHERE GREFCD = 'WP')) AS CREDITS_COUNT" +
" FROM FPSLSBRN A " +
" LEFT JOIN FPITMMAS I ON A.WKITEM = I.ITMNUM" +
" LEFT JOIN FPSYGREF G ON G.GREFCD = 'BF' AND I.ITVSUF = G.GREFKY" +
" WHERE WKSHIP = " + sCustNo +
" AND WKDATE BETWEEN " + sDateStart + " AND " + sDateEnd +
" AND I.ITINVT IN (SELECT GREFKY FROM FPSYGREF WHERE GREFCD = 'WP')" +
" GROUP BY ITVSUF, GREFDT";
try
{
DataTable dt = new DataTable();
odbcConnection.Open();
OdbcCommand odbcCommand = new OdbcCommand(selectCmd, odbcConnection);
System.Data.Odbc.OdbcDataAdapter da = new System.Data.Odbc.OdbcDataAdapter(odbcCommand);
da.Fill(dt);
double dTotal = 0;
foreach (DataRow dr in dt.Rows)
{
try
{
dr["SALES"] = Convert.ToInt32(dr["SALES_COUNT"]);
}
catch
{
dr["SALES"] = 0;
}
try
{
dr["CREDITS"] = Convert.ToInt32(dr["CREDITS_COUNT"]);
}
catch
{
dr["CREDITS"] = 0;
}
dr["TOTAL"] = Convert.ToInt32(dr["SALES"]) - Convert.ToInt32(dr["CREDITS"]);
dTotal += Convert.ToInt32(dr["TOTAL"]);
}
sales_total.Text = dTotal.ToString("#0");
foreach (DataRow dr in dt.Rows)
{
double dPct = 0.0;
try
{
dPct = (Convert.ToInt32(dr["TOTAL"]) / dTotal) * 100;
}
catch
{
dPct = 0;
}
dr["PCT"] = dPct.ToString("#0.00");
if (Convert.ToInt32(dr["ITVSUF"]) == Convert.ToInt32(brand_family_dropdown.SelectedValue))
{
sales_current_pct.Text = dr["PCT"].ToString();
sales_brand.Text = dr["TOTAL"].ToString();
double dAddUnits = 0;
try
{
dAddUnits = (dTargetPct / 100 * dTotal) - Convert.ToInt32(dr["TOTAL"]);
sales_additional.Text = dAddUnits.ToString("#0");
}
catch
{
}
}
}
DataView dv = new DataView(dt);
item_list.DataSource = dv;
item_list.DataBind();
}
catch (Exception ex)
{
}
finally
{
if (odbcDataReader != null)
{
odbcDataReader.Close();
}
odbcConnection.Close();
}
}
private void LoadView()
{
System.Data.Odbc.OdbcConnection OdbcConnection = new System.Data.Odbc.OdbcConnection(ConfigurationManager.ConnectionStrings["TurningpointSystem"].ConnectionString);
try
{
DataTable dt = new DataTable();
dg = item_list;
System.Data.Odbc.OdbcCommand oCmd = new System.Data.Odbc.OdbcCommand();
OdbcConnection.Open();
oCmd.Connection = OdbcConnection;
string selectCmd;
if (ConfigurationManager.AppSettings["RX"] == "yes")
{
selectCmd = String.Format(
" SELECT distinct A.CRITEM, CRSEQ, '' as CIGITM, A.CRCRTT, A.CRQTSR, B.ITDESC, B.ITSIZE, B.ITINVT, B.ITMUPC" +
" FROM FPCRDDTL A LEFT JOIN FPITMMAS B ON A.CRITEM=B.ITMNUM" +
// " LEFT OUTER JOIN FPCIGTAX Z ON Z.CIGSHP = A.CRITEM" +
" WHERE A.CRARNO = {0} ", creditNo);
}
else
{
selectCmd = String.Format(
" SELECT distinct A.CRITEM, CRSEQ, Z.CIGITM, A.CRCRTT, A.CRQTSR, B.ITDESC, B.ITSIZE, B.ITINVT, B.ITMUPC" +
" FROM FPCRDDTL A LEFT JOIN FPITMMAS B ON A.CRITEM=B.ITMNUM" +
" LEFT OUTER JOIN FPCIGTAX Z ON Z.CIGSHP = A.CRITEM" +
" WHERE A.CRARNO = {0} ", creditNo);
}
oCmd.CommandText = selectCmd;
System.Data.Odbc.OdbcDataAdapter da = new System.Data.Odbc.OdbcDataAdapter(oCmd);
da.Fill(dt);
sCSV = "";
for (int k = 0; k <= dg.Columns.Count - 1; k++)
{
sCSV += dg.Columns[k].HeaderText + ",";
}
sCSV += "\n";
for (int i = 0; i <= dt.Rows.Count - 1; ++i)
{
DataRow dr = dt.Rows[i];
if (dr["CIGITM"].ToString().Trim() != "")
{
dr["CRITEM"] = dr["CIGITM"];
}
sCSV += dr["CRITEM"].ToString() + ",";
sCSV += "\"" + dr["ITDESC"].ToString().Trim() + "\"" + ",";
sCSV += "\"" + dr["ITMUPC"].ToString().Trim() + "\"" + ",";
sCSV += dr["CRCRTT"].ToString() + ",";
sCSV += dr["ITSIZE"].ToString() + ",";
sCSV += dr["CRQTSR"].ToString() + ",";
sCSV += "\n";
}
DataView dv = new DataView(dt);
dg.DataSource = dv;
dg.DataBind();
}
catch (Exception ex)
{
}
finally
{
OdbcConnection.Close();
}
}
public OdbcCommandBuilder(OdbcDataAdapter adapter)
: this()
{
DataAdapter = adapter;
}
}// read tablenames from DB
public bool readTableDetails(string TableName, ref DataTable TableDesc, OdbcConnection conn, bool IsWildCardFilterOn)
{
bool RetValue = true;
try
{
#region get User Id from connection string
int start = conn.ConnectionString.IndexOf("UID=") + 4;
int end = conn.ConnectionString.IndexOf(";", start);
string uID = conn.ConnectionString.Substring((start), (end - start));// User ID
#endregion
string SemanticLenght = "BYTE";
try
{
// dependent on V_$NLS_PARAMETERS.NLS_LENGTH_SEMANTICS (CHAR or BYTE)
// the select statement is different
OdbcCommand odbcCommandSemantic = new OdbcCommand();
odbcCommandSemantic.CommandText = "SELECT VALUE FROM SYS.V_$NLS_PARAMETERS WHERE PARAMETER = 'NLS_LENGTH_SEMANTICS';";
odbcCommandSemantic.Connection = conn;
DataTable SemanticTable = new DataTable();
System.Data.Odbc.OdbcDataAdapter ODBC_ADAPT_SEMANTIC = new System.Data.Odbc.OdbcDataAdapter(odbcCommandSemantic);
ODBC_ADAPT_SEMANTIC.Fill(SemanticTable);
SemanticLenght = SemanticTable.Rows[0]["VALUE"].ToString();
}
catch (OdbcException ex)
{
MessageBox.Show(ex.Message,
"*** ERROR GETTING PARAMETER 'NLS_LENGTH_SEMANTICS'. DEFAULT WILL BE 'BYTE' ***", MessageBoxButtons.OK, MessageBoxIcon.Error);
SemanticLenght = "BYTE";
}
OdbcCommand odbcCommand1 = new OdbcCommand();
if (IsWildCardFilterOn)
{
if (SemanticLenght.Equals("BYTE") || SemanticLenght.Equals("CHAR"))
{
odbcCommand1.CommandText = "SELECT COLUMN_NAME, COLUMN_ID, DATA_TYPE, decode(CHAR_LENGTH, 0, DATA_PRECISION, CHAR_LENGTH) as DATA_LENGTH, NULLABLE, DATA_SCALE, OWNER FROM ALL_TAB_COLUMNS WHERE owner like '" + uID.ToUpper() + "%' and TABLE_NAME = '" + TableName + "' ORDER BY COLUMN_ID;";
}
else// BYTE
{
odbcCommand1.CommandText = "SELECT COLUMN_NAME, COLUMN_ID, DATA_TYPE, DATA_LENGTH, NULLABLE, DATA_SCALE FROM ALL_TAB_COLUMNS WHERE owner like '" + uID.ToUpper() + "%' and TABLE_NAME = '" + TableName + "' ORDER BY COLUMN_ID;";
}
}
else
{
if (SemanticLenght.Equals("BYTE") || SemanticLenght.Equals("CHAR"))
{
odbcCommand1.CommandText = "SELECT COLUMN_NAME, COLUMN_ID, DATA_TYPE, decode(CHAR_LENGTH, 0, DATA_PRECISION, CHAR_LENGTH) as DATA_LENGTH, NULLABLE, DATA_SCALE, OWNER FROM ALL_TAB_COLUMNS WHERE owner like '" + uID.ToUpper() + "' and TABLE_NAME = '" + TableName + "' ORDER BY COLUMN_ID;";
}
else// BYTE
{
odbcCommand1.CommandText = "SELECT COLUMN_NAME, COLUMN_ID, DATA_TYPE, DATA_LENGTH, NULLABLE, DATA_SCALE FROM ALL_TAB_COLUMNS WHERE owner like '" + uID.ToUpper() + "' and TABLE_NAME = '" + TableName + "' ORDER BY COLUMN_ID;";
}
}
odbcCommand1.Connection = conn;
System.Data.Odbc.OdbcDataAdapter adapt = new System.Data.Odbc.OdbcDataAdapter(odbcCommand1);
adapt.Fill(TableDesc);
DataTable all_constraints_tab = new DataTable(); // table for PK select
odbcCommand1.CommandText = "SELECT cols.column_name FROM all_constraints cons, all_cons_columns cols WHERE cols.table_name = '"
+ TableName.ToUpper() + "' AND cons.constraint_type = 'P' AND cons.constraint_name = cols.constraint_name AND cons.owner = cols.owner AND cons.owner like '" + uID.ToUpper() + "%' ORDER BY cols.POSITION;";
//odbcCommand1.CommandText = "SELECT cols.column_name FROM all_constraints cons, all_cons_columns cols WHERE cols.table_name = '"
// + TableName.ToUpper() + "' AND cons.constraint_type = 'P' AND cons.constraint_name = cols.constraint_name AND cons.owner = cols.owner ORDER BY cols.POSITION;";
adapt.SelectCommand = odbcCommand1;
adapt.Fill(all_constraints_tab);
// comments
DataTable user_comment_tab = new DataTable(); // table for comments select
odbcCommand1.CommandText = "select column_name, comments from user_col_comments where table_name like '" + TableName.ToUpper() + "%';";
adapt.SelectCommand = odbcCommand1;
adapt.Fill(user_comment_tab);
// NEW Columns for DataTable
DataColumn PK = new DataColumn("PK"); // column for PK identification
TableDesc.Columns.Add(PK);
DataColumn COLUMN_COMMENT = new DataColumn("COLUMN_COMMENT"); // column for comment
TableDesc.Columns.Add(COLUMN_COMMENT);
int PKNO = 1;
for (int j = 0; j < TableDesc.Rows.Count; j++)
{
TableDesc.Rows[j]["PK"] = "";
TableDesc.Rows[j]["COLUMN_COMMENT"] = "";
for (int i = 0; i < all_constraints_tab.Rows.Count; i++)
{
if (TableDesc.Rows[j]["COLUMN_NAME"].ToString() == all_constraints_tab.Rows[i][0].ToString())
{
TableDesc.Rows[j]["PK"] = "PK " + (PKNO++);
break;
}
}
// comments
for (int i = 0; i < user_comment_tab.Rows.Count; i++)
{
if (TableDesc.Rows[j]["COLUMN_NAME"].ToString() == user_comment_tab.Rows[i][0].ToString())
{
TableDesc.Rows[j]["COLUMN_COMMENT"] = user_comment_tab.Rows[i][1].ToString();
break;
}
}// for (int i = 0; i < user_comment_tab.Rows.Count; i++)
}
}
catch (OdbcException ex)
{
MessageBox.Show(ex.StackTrace + " : \r\n\r\n" + ex.Message,
"OdbcException", MessageBoxButtons.OK, MessageBoxIcon.Error);
RetValue = false;
}
return(RetValue);
}// readTableDetails
private void btnSearch_Click(object sender, EventArgs e)
{
if (txtPatientID.Text.Length <= 0 && txtFirstName.Text.Length <= 0 && txtReferringMD.Text.Length <= 0 && chkSearchDate.Checked == false)
{
return;
}
string strSQL = "";
string varCode = "";
string varName = "";
string varPhys = "";
if (txtPatientID.Text.Length > 0)
{
varCode = "%" + txtPatientID.Text + "%";
}
if (txtFirstName.Text.Length > 0)
{
varName = "%" + txtFirstName.Text + "%";
}
if (txtReferringMD.Text.Length > 0)
{
varPhys = "%" + txtReferringMD.Text + "%";
}
if (chkAll.Checked)
{
if (chkSearchDate.Checked)
{
strSQL = "SELECT * FROM PatientStudyView WHERE (PatientID LIKE '" + varCode + "' OR PatientName_DICOM LIKE '" + varName + "' or ReferringPhysician_DICOM like '" + varPhys + "') and convert(date,StudyDate) BETWEEN '" + dtpDateFrom.Value.ToString("yyyy-MM-dd") + "' and '" + dtpDateTo.Value.ToString("yyyy-MM-dd") + "' order by StudyDate desc";
}
else
{
strSQL = "SELECT * FROM PatientStudyView WHERE PatientID LIKE '" + varCode + "' OR PatientName_DICOM LIKE '" + varName + "' or ReferringPhysician_DICOM like '" + varPhys + "' order by StudyDate desc";
}
if (string.IsNullOrEmpty(varCode) && string.IsNullOrEmpty(varName) && string.IsNullOrEmpty(varPhys) && chkSearchDate.Checked)
{
strSQL = "SELECT * FROM PatientStudyView WHERE convert(date,StudyDate) BETWEEN '" + dtpDateFrom.Value.ToString("yyyy-MM-dd") + "' and '" + dtpDateTo.Value.ToString("yyyy-MM-dd") + "' order by StudyDate desc";
}
}
else
{
string Mod = "";
bool MoreThanOne = false;
if (chkCR.Checked)
{
Mod = "Modality='CR'";
MoreThanOne = true;
}
if (chkCT.Checked)
{
if (MoreThanOne)
{
Mod = Mod + " OR Modality='CT'";
}
else
{
Mod = "Modality='CT'";
}
MoreThanOne = true;
}
if (chkDX.Checked)
{
if (MoreThanOne)
{
Mod = Mod + " OR Modality='DX'";
}
else
{
Mod = "Modality='DX'";
}
MoreThanOne = true;
}
if (chkES.Checked)
{
if (MoreThanOne)
{
Mod = Mod + " OR Modality='ES'";
}
else
{
Mod = "Modality='ES'";
}
MoreThanOne = true;
}
if (chkMG.Checked)
{
if (MoreThanOne)
{
Mod = Mod + " OR Modality='MG'";
}
else
{
Mod = "Modality='MG'";
}
MoreThanOne = true;
}
if (chkMR.Checked)
{
if (MoreThanOne)
{
Mod = Mod + " OR Modality='MR'";
}
else
{
Mod = "Modality='MR'";
}
MoreThanOne = true;
}
if (chkNM.Checked)
{
if (MoreThanOne)
{
Mod = Mod + " OR Modality='NM'";
}
else
{
Mod = "Modality='NM'";
}
MoreThanOne = true;
}
if (chkOT.Checked)
{
if (MoreThanOne)
{
Mod = Mod + " OR Modality='OT'";
}
else
{
Mod = "Modality='OT'";
}
MoreThanOne = true;
}
if (chkPT.Checked)
{
if (MoreThanOne)
{
Mod = Mod + " OR Modality='PT'";
}
else
{
Mod = "Modality='PT'";
}
MoreThanOne = true;
}
if (chkRF.Checked)
{
if (MoreThanOne)
{
Mod = Mod + " OR Modality='RF'";
}
else
{
Mod = "Modality='RF'";
}
MoreThanOne = true;
}
if (chkRT.Checked)
{
if (MoreThanOne)
{
Mod = Mod + " OR Modality='RT'";
}
else
{
Mod = "Modality='RT'";
}
MoreThanOne = true;
}
if (chkSC.Checked)
{
if (MoreThanOne)
{
Mod = Mod + " OR Modality='SC'";
}
else
{
Mod = "Modality='SC'";
}
MoreThanOne = true;
}
if (chkUS.Checked)
{
if (MoreThanOne)
{
Mod = Mod + " OR Modality='US'";
}
else
{
Mod = "Modality='US'";
}
MoreThanOne = true;
}
if (chkXA.Checked)
{
if (MoreThanOne)
{
Mod = Mod + " OR Modality='XA'";
}
else
{
Mod = "Modality='XA'";
}
MoreThanOne = true;
}
if (chkSearchDate.Checked)
{
strSQL = "SELECT * FROM PatientStudyView WHERE (PatientID LIKE '" + varCode + "' OR PatientName_DICOM LIKE '" + varName + "' or ReferringPhysician_DICOM like '" + varPhys + "') and convert(date,StudyDate) BETWEEN '" + dtpDateFrom.Value.ToString("yyyy-MM-dd") + "' and '" + dtpDateTo.Value.ToString("yyyy-MM-dd") + "' AND (" + Mod + ") order by StudyDate desc";
}
else
{
strSQL = "SELECT * FROM PatientStudyView WHERE PatientID LIKE '" + varCode + "' OR PatientName_DICOM LIKE '" + varName + "' or ReferringPhysician_DICOM like '" + varPhys + "' AND (" + Mod + ") order by StudyDate desc";
}
if (varCode.Length <= 0 && varName.Length <= 0 && varPhys.Length <= 0 && chkSearchDate.Checked)
{
strSQL = "SELECT * FROM PatientStudyView WHERE convert(date,StudyDate) BETWEEN '" + dtpDateFrom.Value.ToString("yyyy-MM-dd") + "' and '" + dtpDateTo.Value.ToString("yyyy-MM-dd") + "' AND (" + Mod + ") order by StudyDate desc";
}
}
CN1.OpenConnection();
CMD1 = new System.Data.Odbc.OdbcCommand(strSQL, CN1.DBConnection);
//Dim dtst As New DataSet
DicomServerDBDataSet1.PatientStudyViewDataTable table = new DicomServerDBDataSet1.PatientStudyViewDataTable();
System.Data.Odbc.OdbcDataAdapter adptr = new System.Data.Odbc.OdbcDataAdapter();
adptr.SelectCommand = CMD1;
adptr.Fill(table);
DataGridView1.Refresh();
DataGridView1.DataSource = null;
DataGridView1.DataSource = table;
this.Cursor = Cursors.Default;
GridDesign();
CMD1.Dispose();
CN1.closeconnection();
}
