//public string ExcelFile {private get; set; }
public static DataTable ReadData(string excelFile)
{
if (!System.IO.File.Exists(excelFile))
return null;
OleDbConnection excelConnection = new OleDbConnection();
excelConnection.ConnectionString = string.Format("Provider=Microsoft.Jet.OleDb.4.0;Data Source='{0}';Extended Properties='Excel 8.0;HDR=YES'", excelFile);
excelConnection.Open();
DataTable dtSchema = excelConnection.GetSchema("Tables");
if (dtSchema.Rows.Count == 0)
return null;
string strTableName = dtSchema.Rows[0]["Table_Name"] as string;
string strSQL = string.Format("select * from [{0}]", strTableName);
OleDbCommand cmdSelect = excelConnection.CreateCommand();
cmdSelect.CommandText = strSQL;
OleDbDataAdapter dbAdapter = new OleDbDataAdapter(cmdSelect);
DataTable dtResult=new DataTable();
dbAdapter.Fill(dtResult);
dbAdapter.Dispose();
excelConnection.Close();
excelConnection.Dispose();
return dtResult;
}
public static DataTable GetDataTable(IGTDataContext oDC, string sSQL, GTDiagnostics _diag)
{
DataTable dt = null;
try
{
//IGTDataContext oDC = GTClassFactory.Create<IGTApplication>().DataContext;
Recordset oRS = oDC.OpenRecordset(sSQL, CursorTypeEnum.adOpenStatic, LockTypeEnum.adLockReadOnly, (int)CommandTypeEnum.adCmdText);
OleDbDataAdapter oDA = new System.Data.OleDb.OleDbDataAdapter();
dt = new DataTable();
oDA.Fill(dt, oRS);
oDA.Dispose();
oRS.Close();
}
catch (Exception ex)
{
if (_diag.IsEnabled(GTDiagCat.EE))
{
_diag.LogException("CommandUtilities.GetDataTable", ex);
}
throw ex;
}
return(dt);
}
/// <summary>
/// Executes an inline SQL statement and returns a data table.
/// </summary>
/// <param name="dbStatement">Inline SQL</param>
/// <param name="connectionString">Connection string</param>
/// <returns>Data table containing the return data</returns>
public static DataTable RunQuery(string dbStatement, string connectionString)
{
OleDbConnection dbConnection = null;
OleDbCommand dbCommand = null;
OleDbDataAdapter adapter = null;
DataTable dt = null;
try
{
dbConnection = new OleDbConnection(connectionString);
dbCommand = new OleDbCommand(dbStatement, dbConnection);
dbCommand.CommandType = CommandType.Text;
dbCommand.CommandTimeout = 600;
adapter = new OleDbDataAdapter(dbCommand);
dt = new DataTable();
dbConnection.Open();
adapter.Fill(dt);
return dt;
}
finally
{
if (adapter != null)
adapter.Dispose();
if (dbCommand != null)
dbCommand.Dispose();
if (dbConnection != null)
dbConnection.Dispose();
}
}
public ProcessingResult ProcessAccessTableToJsonFile(string connString, string queryBegin, string queryEnd, string table, string file)
{
var result = new ProcessingResult();
Debug.Print("ProcessAccessTableToJsonFile: Processing " + table);
var last = DateTime.Now;
var sql = queryBegin + " " + table + " " + queryEnd;
var dsView = new DataSet();
var adp = new OleDbDataAdapter(sql, connString);
adp.Fill(dsView, "AccessData");
adp.Dispose();
var tbl = dsView.Tables["AccessData"];
result.toProcess = tbl.Rows.Count;
SaveObjToJsonFile(tbl, _folderPath + file + ".json");
result.modified = tbl.Rows.Count;
Debug.Print("ProcessAccessTableToJsonFile: Processed " + table);
var diffFromLast = DateTime.Now - last;
Debug.Print("TimeToProcess: " + diffFromLast.ToString());
return result;
}
public List<SheetInfo> GetSheetlist()
{
if (excelpath == null||excelpath.Length<=0)
return null;
List<SheetInfo> list = new List<SheetInfo>();
string connStr = "";
string sql_F = "Select * FROM [{0}]";
string fileType = System.IO.Path.GetExtension(excelpath);
if (fileType == ".xls")
connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelpath + ";" + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
else
connStr = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + excelpath + ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";
OleDbConnection conn = new OleDbConnection(connStr);
OleDbDataAdapter da = null;
try
{
conn.Open();
string sheetname = "";
DataTable dtSheetName =
conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
da = new OleDbDataAdapter();
for (int i = 0; i < dtSheetName.Rows.Count;i++ )
{
sheetname = (string)dtSheetName.Rows[i]["TABLE_NAME"];
if (sheetname.Contains("$") )
{
SheetInfo info = new SheetInfo();
info.SheetName = sheetname.Replace("$", "");
da.SelectCommand = new OleDbCommand(String.Format(sql_F, sheetname), conn);
DataSet dsItem = new DataSet();
da.Fill(dsItem, sheetname);
int cnum = dsItem.Tables[0].Columns.Count;
int rnum = dsItem.Tables[0].Rows.Count;
info.StartRange = "A1";
char c = (char)('A' + cnum - 1);
info.EndRange = c + Convert.ToString(rnum);
list.Add(info);
}
}
}
catch (System.Exception ex)
{
MessageBox.Show(ex.ToString(), "错误消息");
return null;
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
if(da!=null)
da.Dispose();
conn.Dispose();
}
}
return list;
}
public static DataTable GetData(string strConn, string strSql)
{
DataTable dt = new DataTable("td");
using (OleDbConnection conn = new OleDbConnection(strConn))
{
conn.Open();
OleDbCommand cmd = null;
OleDbDataAdapter da = null;
try
{
cmd = new OleDbCommand(strSql, conn);
da = new OleDbDataAdapter { 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();
}
}
}
private void button3_Click(object sender, EventArgs e)
{
data3.DataSource = null;
try
{
//System.Data.OleDb.OleDbConnection MyCnn;
System.Data.DataSet DSet;
// '" + sem + "'
System.Data.OleDb.OleDbDataAdapter MyCmd;
MyCmd = new System.Data.OleDb.OleDbDataAdapter("select * from mca3 ", con);
MyCmd.TableMappings.Add("Table", "mca3");
DSet = new System.Data.DataSet();
MyCmd.Fill(DSet);
data3.DataSource = DSet.Tables[0];
MyCmd.Dispose();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
private DataTable LoadWorksheet(string worksheetName)
{
OleDbConnection connection = new System.Data.OleDb.OleDbConnection(_ConnectionString);
OleDbDataAdapter cmd = null;
try
{
cmd = new System.Data.OleDb.OleDbDataAdapter(string.Format("SELECT * FROM [{0}$]", worksheetName), connection);
connection.Open();
DataTable dt = new DataTable();
cmd.Fill(dt);
return(dt);
}
catch
{
return(null);
}
finally
{
if (connection != null)
{
try { connection.Dispose(); }
catch { }
}
if (cmd != null)
{
try { cmd.Dispose(); }
catch { }
}
}
}
/// <summary>
/// Parses a csv file.
/// http://tech.pro/tutorial/803/csharp-tutorial-using-the-built-in-oledb-csv-parser
/// </summary>
/// <param name="path"></param>
/// <returns></returns>
private static DataTable ParseCSV(string path)
{
if (!File.Exists(path))
return null;
string full = Path.GetFullPath(path);
string file = Path.GetFileName(full);
string dir = Path.GetDirectoryName(full);
//create the "database" connection string
string connString = "Provider=Microsoft.Jet.OLEDB.4.0;"
+ "Data Source=\"" + dir + "\\\";"
+ "Extended Properties=\"text;HDR=No;FMT=Delimited\"";
//create the database query
string query = "SELECT * FROM " + file;
//create a DataTable to hold the query results
DataTable dTable = new DataTable();
//create an OleDbDataAdapter to execute the query
OleDbDataAdapter dAdapter = new OleDbDataAdapter(query, connString);
try
{
//fill the DataTable
dAdapter.Fill(dTable);
}
catch (InvalidOperationException /*e*/)
{ }
dAdapter.Dispose();
return dTable;
}
private void btnNew_Click( object sender, EventArgs e )
{
if( Program.UserType == UserType.Customer )
return;
AddDlg dlg = new AddDlg();
if( dlg.ShowDialog( this ) != DialogResult.OK )
return;
RefillComboBoxes();
OleDbCommand cmd = new OleDbCommand();
cmd.CommandText = "select top 1 * from WedDress order by [ID] desc";
cmd.Connection = Program.Database;
cmd.CommandType = CommandType.Text;
OleDbDataAdapter oda = new OleDbDataAdapter( cmd);
DataTable dt = new DataTable();
oda.Fill( dt );
dsDress.Tables["WedDress"].Merge( dt );
dt.Dispose();
oda.Dispose();
cmd.Dispose();
}
private void frmRConc_Load(object sender, EventArgs e)
{
string appPath = ConfigurationSettings.AppSettings["PathBd"];
if (appPath == "Local")
appPath = AppDomain.CurrentDomain.BaseDirectory;
//DataSet ds = new DataSet();
//ds.Tables.Add("conciliacion");
OleDbConnection conAcc = new OleDbConnection(capaDb.dbPath());
DataSet ds = new DataSet();
OleDbDataAdapter da = new OleDbDataAdapter("select * from conciliacion", conAcc);
ds.Tables.Add("conciliacion");
da.Fill(ds.Tables[0]);
da.Dispose();
int c = dt.Rows.Count;
string num = "", name = "", horaChk = "", horaV = "", coord = "";
//ds.Tables[0].Columns.Count
foreach (DataRow dr in dt.Rows)
{
num = dr[0].ToString();
name = dr[1].ToString();
horaChk = dr[2].ToString();
horaV = dr[3].ToString();
coord = dr[4].ToString();
ds.Tables[0].Rows.Add(new object[] { num, name, horaChk, horaV, coord });
}
ReportParameter pOH = new ReportParameter("fechas",_fecha);//"0.00"));
this.reportViewer1.LocalReport.SetParameters(new ReportParameter[] { pOH });
conciliacionBindingSource.DataSource = ds;
ds.Dispose();
this.reportViewer1.RefreshReport();
dt.Dispose();
}
public void UpdateChart()
{
try
{
OleDbConnection connection = new OleDbConnection();
connection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + reference.Getdb();
connection.Open();
OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT print_report.[ID], print_report.[printer_id], print_report.[overall_pagecount], print_report.[color_pagecount], print_report.[mono_pagecount], print_report.[date_time], base_count.[printer_id], base_count.[overall_pagecount] AS baseo, base_count.[color_pagecount] AS basec, base_count.[mono_pagecount] AS basem, print_report.[overall_pagecount] - [baseo] AS totalo, print_report.[color_pagecount] - [basec] AS totalc, print_report.[mono_pagecount] - [basem] AS totalm FROM print_report INNER JOIN base_count ON format(DateAdd('h',1,base_count.[date_time]),'mm/dd/yyyy hh') = format(print_report.[date_time], 'mm/dd/yyyy hh') WHERE print_report.[printer_id] = " + currentchart + " and base_count.[printer_id] = " + currentchart + " and print_report.[overall_pagecount] > 0 and base_count.[overall_pagecount] > 0 and Format(TimeSerial(Hour(print_report.[date_time]),0,0),'Short Time') <= '" + stop_time + "' and INT(print_report.date_time) >= DateValue('" + start + "') and INT(print_report.date_time) <= DateValue('" + stop + "') and Format(TimeSerial(Hour(base_count.[date_time]),0,0),'Short Time') >= '" + start_time + "';", connection);
DataTable table = new DataTable();
adapter.Fill(table);
adapter.Dispose();
if (live_tracking_checkbox.Checked)
{
adapter = new OleDbDataAdapter("SELECT print_report.[ID], print_report.[printer_id], print_report.[overall_pagecount], print_report.[color_pagecount], print_report.[mono_pagecount], print_report.[date_time], base_count.[printer_id], base_count.[overall_pagecount] AS baseo, base_count.[color_pagecount] AS basec, base_count.[mono_pagecount] AS basem, print_report.[overall_pagecount] - [baseo] AS totalo, print_report.[color_pagecount] - [basec] AS totalc, print_report.[mono_pagecount] - [basem] AS totalm FROM print_report INNER JOIN base_count ON format(DateAdd('h',1,base_count.[date_time]),'mm/dd/yyyy hh') = format(print_report.[date_time], 'mm/dd/yyyy hh') WHERE print_report.[printer_id] = " + currentchart + " and base_count.[printer_id] = " + currentchart + " and print_report.[overall_pagecount] > 0 and Format(TimeSerial(Hour(print_report.[date_time]),0,0),'Short Time') = '" + DateTime.Now.AddHours(-1).ToString("HH:") + "00" + "' and print_report.date_time > DateValue('" + DateTime.Now + "') and Format(TimeSerial(Hour(base_count.[date_time]),0,0),'Short Time') = '" + start_time + "';", connection);
DataTable table2 = new DataTable();
adapter.Fill(table2);
table.Merge(table2);
adapter.Dispose();
}
connection.Close();
printed_pages_chart.DataSource = table;
printed_pages_chart.ChartAreas[0].AxisX.LabelStyle.Format = "M/d/yy htt";
printed_pages_chart.Series.Clear();
printed_pages_chart.Series.Add("Total");
printed_pages_chart.Series["Total"].XValueMember = "date_time";
printed_pages_chart.Series["Total"].YValueMembers = "totalo";
printed_pages_chart.Series["Total"].XValueType = System.Windows.Forms.DataVisualization.Charting.ChartValueType.DateTime;
printed_pages_chart.Series["Total"].IsValueShownAsLabel = true;
printed_pages_chart.Series.Add("Color");
printed_pages_chart.Series["Color"].XValueMember = "date_time";
printed_pages_chart.Series["Color"].YValueMembers = "totalc";
printed_pages_chart.Series["Color"].XValueType = System.Windows.Forms.DataVisualization.Charting.ChartValueType.DateTime;
printed_pages_chart.Series["Color"].IsValueShownAsLabel = true;
printed_pages_chart.Series.Add("Mono");
printed_pages_chart.Series["Mono"].XValueMember = "date_time";
printed_pages_chart.Series["Mono"].YValueMembers = "totalm";
printed_pages_chart.Series["Mono"].XValueType = System.Windows.Forms.DataVisualization.Charting.ChartValueType.DateTime;
printed_pages_chart.Series["Mono"].IsValueShownAsLabel = true;
foreach(System.Windows.Forms.DataVisualization.Charting.Series s in printed_pages_chart.Series)
{
foreach (System.Windows.Forms.DataVisualization.Charting.DataPoint dp in s.Points)
{
if (dp.YValues[0] == 0)
{
dp.IsValueShownAsLabel = false;
}
}
}
printed_pages_chart.DataBind();
}
catch (Exception e1)
{
MessageBox.Show(e1.Message);
}
}
//返回dataset类型数据
public DataSet GetDataSet(string sql)
{
OleDbConnection cn = get();
OleDbDataAdapter dr = new OleDbDataAdapter(sql, cn);
DataSet ds = new DataSet();
dr.Fill(ds);
dr.Dispose();
cn.Close();
return ds;
}
public DataTable GetDataTable(string SqlMetin)
{
DataTable dt = new DataTable();
OleDbConnection Baglanti = this.Baglan();
OleDbDataAdapter adapter = new OleDbDataAdapter(SqlMetin, Baglanti);
adapter.Fill(dt);
adapter.Dispose();
Baglanti.Dispose();
Baglanti.Close();
return dt;
}
public DataSet SelectOleDbTable(OleDbConnection conn, string query)
{
OleDbDataAdapter oleDbDataAdapter = new OleDbDataAdapter();
DataSet ds = new DataSet();
oleDbDataAdapter.SelectCommand = new OleDbCommand(query, conn);
oleDbDataAdapter.Fill(ds);
oleDbDataAdapter.Dispose();
return ds;
}
public string GetDataCell(string SqlMetin)
{
DataTable dt = new DataTable();
OleDbConnection Baglanti = this.Baglan();
OleDbDataAdapter adapter = new OleDbDataAdapter(SqlMetin, Baglanti);
adapter.Fill(dt);
adapter.Dispose();
Baglanti.Dispose();
Baglanti.Close();
return dt.Rows[0][0].ToString();
}
public DataSet GetDataSet(string SqlMetin)
{
DataSet ds = new DataSet();
OleDbConnection Baglanti = this.Baglan();
OleDbDataAdapter adapter = new OleDbDataAdapter(SqlMetin, Baglanti);
adapter.Fill(ds);
adapter.Dispose();
Baglanti.Dispose();
Baglanti.Close();
return ds;
}
DataSet GetDataSet(string sql)
{
DBConnection dbconn = new DBConnection();
OleDbConnection connection = dbconn.getConnection();
connection.Open();
OleDbCommand cmd = new OleDbCommand(sql, connection);
OleDbDataAdapter dataAdapter = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
dataAdapter.Fill(ds);
cmd.Dispose();
dataAdapter.Dispose();
return ds;
}
public string FillLegal()
{
string tbl = "admin";
string sqlStr = "SELECT * FROM " + tbl;
string legal = string.Empty;
try
{
OleDbConnection cnn = new OleDbConnection(Base.cnnStr);
OleDbDataAdapter oda = new OleDbDataAdapter(sqlStr, cnn);
OleDbCommand cmd = new OleDbCommand(sqlStr, cnn);
cnn.Open();
OleDbDataReader drr = cmd.ExecuteReader();
DataSet ds = new DataSet();
oda.Fill(ds, "admin");
while (drr.Read())
{
legal = EncDec.Decrypt(drr["legal"].ToString(), Base.hashKey);
break;
}
drr.Close();
cnn.Close();
cmd.Dispose();
drr.Dispose();
ds.Dispose();
oda.Dispose();
cnn.Dispose();
cmd = null;
drr = null;
ds = null;
oda = null;
cnn = null;
}
catch
{
legal = errInvalidLegal;
}
finally
{
tbl = null;
sqlStr = null;
}
return legal;
}
protected void Page_Load(object sender, EventArgs e)
{
string sql = "SELECT * FROM [user_details] where [ID] = " + Request.QueryString["ID"];
DataSet ds = new DataSet();
DBConnection dbconn = new DBConnection();
OleDbConnection connection = dbconn.getConnection();
connection.Open();
OleDbCommand cmd = new OleDbCommand(sql, connection);
OleDbDataAdapter dataAdapter = new OleDbDataAdapter(cmd);
dataAdapter.Fill(ds);
cmd.Dispose();
dataAdapter.Dispose();
showuser.DataSource = ds;
showuser.DataBind();
}
public DataSet GetWorkplace()
{
DataSet workplace;
OleDbConnection connection = new OleDbConnection(strConnection);
OleDbDataAdapter adaptor = new OleDbDataAdapter("SELECT * FROM *", connection);
workplace = new DataSet();
adaptor.FillSchema(workplace, SchemaType.Source);
adaptor.Fill(workplace);
adaptor.Dispose();
connection.Close();
return workplace;
}
//파일에서 불러오기
private void buttonAddByFile_Click(object sender, EventArgs e)
{
//xls파일 불러와서 테이블에 저장
string strConnection = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Data.xls;Extended Properties='Excel 8.0;HDR=No'";
DataTable dTable = new DataTable();
int rCnt=0;
//SQL문을 통한 OleDb로 자료 읽기
try
{
OleDbConnection oleDbCon = new OleDbConnection(strConnection);
oleDbCon.Open();
string strSQL = "SELECT * FROM [Course$]";
OleDbCommand dbCommand = new OleDbCommand(strSQL, oleDbCon);
OleDbDataAdapter dataAdapter = new OleDbDataAdapter(dbCommand);
dataAdapter.Fill(dTable);
dTable.Dispose();
dataAdapter.Dispose();
dbCommand.Dispose();
oleDbCon.Close();
oleDbCon.Dispose();
}
catch (SystemException ex)
{
MessageBox.Show(ex.Message);
}
//데이터그리드뷰에 데이터 입력
try
{
foreach (DataRow row in dTable.Rows)
{
int cnt = 0;
pRO1COURSESBindingSource.AddNew();
foreach (DataColumn col in dTable.Columns)
{
dataGridViewMakeCrs.Rows[rCnt].Cells[cnt].Value = dTable.Rows[rCnt].ItemArray[cnt];
cnt++;
}
rCnt++;
}
}
catch (NoNullAllowedException ex)
{
MessageBox.Show("완성되지 않은 행이 있습니다. 삭제 후 다시 시도해주세요.");
}
}
public static DataSet GetDataExcel(string fPath, string sheetname)
{
DataSet ds = new DataSet();
string connectionstring = OpenExcelFile(fPath);
string query = "SELECT * FROM [" + sheetname + "$]";
using (OleDbConnection cnn = new OleDbConnection(connectionstring))
{
cnn.Open();
OleDbDataAdapter oleAdapter = new OleDbDataAdapter(query, cnn);
oleAdapter.Fill(ds, sheetname);
oleAdapter.Dispose();
cnn.Close();
cnn.Dispose();
}
return ds;
}
public static DataTable GetDataTable(Recordset oRS)
{
try
{
OleDbDataAdapter oDA = new System.Data.OleDb.OleDbDataAdapter();
DataTable oDT = new DataTable();
oDA.Fill(oDT, oRS);
oDA.Dispose();
oRS.Close();
return(oDT);
}
catch (Exception oEx)
{
throw oEx;
}
}
public DataTable getData(string foxFile)
{
OleDbConnection cnn = null;
OleDbDataAdapter dataadapter = null;
OleDbCommand cmd = null;
DataSet dataset = null;
DataTable T =null;
try
{
cnn = new System.Data.OleDb.OleDbConnection();
cmd = new OleDbCommand();
dataadapter = new System.Data.OleDb.OleDbDataAdapter();
dataset = new DataSet();
cnn.ConnectionString = "Provider=VFPOLEDB.1;Data Source=" + foxFile.Trim();
if (cnn.State == System.Data.ConnectionState.Closed)
{
cnn.Open();
}
cmd.Connection = cnn;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "select * from " + foxFile.Trim();
dataadapter.SelectCommand = cmd;
dataadapter.Fill(dataset);
T = dataset.Tables[0];
dataset.Dispose();
dataadapter.Dispose();
cmd.Dispose();
cnn.Close();
Message = "Done...";
}
catch (Exception e)
{
Message = e.Message;
}
finally
{
dataadapter = null;
dataset = null;
cnn = null;
cmd = null;
}
return T;
}
private DataTable GetDataTable(IGTDataContext oDC, Recordset oRS)
{
try
{
// Recordset oRS = oDC.OpenRecordset(sSQL, CursorTypeEnum.adOpenStatic, LockTypeEnum.adLockReadOnly, (int)CommandTypeEnum.adCmdText);
OleDbDataAdapter oDA = new System.Data.OleDb.OleDbDataAdapter();
DataTable oDT = new DataTable();
oDA.Fill(oDT, oRS);
oDA.Dispose();
oRS.Close();
return(oDT);
}
catch (Exception oEx)
{
throw oEx;
}
}
public DataSet GetDataSet(string sql)
{
OleDbConnection baglanti = this.baglan();
OleDbDataAdapter adapter = new OleDbDataAdapter(sql, baglanti);
DataSet ds = new DataSet();
try
{
adapter.Fill(ds);
}
catch (OleDbException ex)
{
throw new Exception(ex.Message + " (" + sql + ")");
}
adapter.Dispose();
baglanti.Close();
baglanti.Dispose();
return ds;
}
public static System.Data.DataSet DataSet(string sqlText, OleDbParameter[] parms)
{
System.Data.DataSet dataSet = new System.Data.DataSet();
OleDbDataAdapter adapter = new OleDbDataAdapter(sqlText, connectionString)
{
SelectCommand = { CommandType = CommandType.Text }
};
if (parms != null)
{
foreach (OleDbParameter parameter in parms)
{
adapter.SelectCommand.Parameters.Add(parameter);
}
}
adapter.Fill(dataSet, "DataList");
adapter.SelectCommand.Parameters.Clear();
adapter.Dispose();
return dataSet;
}
private void button3_Click(object sender, EventArgs e)
{
DatabaseManager dbManager = DatabaseManager.dataBaseManager();
dbManager.BookConn = new OleDbConnection(dbManager.getConnParam());
dbManager.BookConn.Open();
dataGridView2.DataSource = null;
dataGridView2.Rows.Clear();
dataGridView2.Refresh();
OleDbDataAdapter dAdapter = new OleDbDataAdapter("select * from Rezervasyon WHERE RezervasyonKodu = '" + textBox3.Text + "';", dbManager.BookConn);
OleDbCommandBuilder cBuilder = new OleDbCommandBuilder(dAdapter);
DataTable dataTable = new DataTable();
DataSet ds = new DataSet();
dAdapter.Fill(dataTable);
dataGridView2.DataSource = dataTable;
dAdapter.Dispose();
dbManager.BookConn.Close();
}
public static void ExecuteQuery(string query)
{
try
{
DataTable dt = new DataTable();
OleDbConnection conn = new OleDbConnection(ConnectionString);
OleDbCommand cmd = new OleDbCommand(query, conn);
OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
conn.Open();
adapter.Fill(dt);
adapter.Dispose();
cmd.Dispose();
conn.Close();
}
catch (Exception e)
{
MessageBox.Show("DbError: " + e.Message + "");
}
}
private void btnSearch_Click(object sender, EventArgs e)
{
OleDbConnection conAcc = new OleDbConnection(capaDb.dbPath());
try
{
string fechas = "", turno = "", empleado = "", depto = "", codOp = "",voucher="",linea="";
if (!cbNoDate.Checked)
fechas = "and (IndiceVoucher.Fecha Between #" + date1.Value.ToShortDateString() +
"# And #" + date2.Value.ToShortDateString() + "#) ";
if (tbVoucher.Text != "")
voucher = " AND IndiceVoucher.novoucher= " + tbVoucher.Text;
//if (tbTurno.Text != "")
// turno = " AND IndiceVoucher.Turno= " +tbTurno.Text;
if (tbEmp.Text != "")
empleado = " AND EmpVoucher.Empleado="+ tbEmp.Text;
//if (tbLinea.Text != "")
// linea = " AND DetVoucher.linea='" + tbLinea.Text+"'";
if (tbCo.Text != "")
codOp = " AND DetVoucher.CodigoOperacion='" + tbCo.Text+"'";
string sql = "SELECT IndiceVoucher.novoucher,IndiceVoucher.Fecha, IndiceVoucher.Turno, EmpVoucher.Empleado, " +
"DetVoucher.Empezo,DetVoucher.Termino,DetVoucher.Horas,DetVoucher.CodigoOperacion,DetVoucher.Linea," +
"DetVoucher.Eficiencia " +
"FROM (IndiceVoucher INNER JOIN EmpVoucher ON IndiceVoucher.NoVoucher = EmpVoucher.NoVoucher) INNER JOIN DetVoucher ON IndiceVoucher.NoVoucher = DetVoucher.NoVoucher " +
"WHERE IndiceVoucher.operadores > 0 " + fechas + turno + empleado + linea + codOp+voucher;
conAcc.Open();
OleDbDataAdapter da2 = new OleDbDataAdapter(sql, conAcc);
DataTable dt2 = new DataTable();
da2.Fill(dt2);
dgResult.DataSource = dt2;
dt2.Dispose();
da2.Dispose();
}
catch(Exception err)
{
MessageBox.Show("Error en la busqueda:"+err.Message);
}
finally
{
conAcc.Close();
conAcc.Dispose();
}
}
protected void Page_Load(object sender, EventArgs e)
{
OleDbConnection Connection = new OleDbConnection();
Connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Page.Server.MapPath("../App_Data/db.mdb") + ";";
Connection.Open();
if (Page.IsPostBack)
{
//
// Here one can save postbacked HTML content (editor.Content)
//
OleDbCommand myCommand;
string sqlString = "Update tbTest Set fldContent=? Where id=?";
myCommand = new OleDbCommand(sqlString, Connection);
myCommand.Parameters.Add(new OleDbParameter("@fldContent", OleDbType.VarChar));
myCommand.Parameters["@fldContent"].Value = editor.Content;
myCommand.Parameters.Add(new OleDbParameter("@id", OleDbType.Integer));
myCommand.Parameters["@id"].Value = 1;
myCommand.ExecuteNonQuery();
}
else
{
//
// Here one can set initial HTML content of Editor
//
string sqlString = "Select fldContent from tbTest Where id=1";
OleDbDataAdapter eAdapter = new OleDbDataAdapter(sqlString, Connection);
DataTable eTable = new DataTable();
OleDbCommandBuilder CommandBuilder = new OleDbCommandBuilder(eAdapter);
eAdapter.Fill(eTable);
editor.Content = (string)eTable.Rows[0][0];
eAdapter.Dispose();
eTable.Dispose();
}
Connection.Close();
Connection.Dispose();
}
public static DataTable GetTable(string sql)
{
using (var conn = new OleDbConnection(_dbConnectionString))
{
var da = new OleDbDataAdapter(sql, conn);
var dt = new DataTable();
try
{
da.Fill(dt);
return dt;
}
finally
{
da.Dispose();
conn.Dispose();
}
}
}
public static DataTable readData()
{
try {
string _connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + GlobalData.initSetting.BOSAREPORT + ";" + "Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1;MAXSCANROWS=0'";
using (OleDbConnection conn = new OleDbConnection(_connectionString)) {
DataTable dt = new DataTable();
conn.Open();
OleDbDataAdapter objDA = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$A8:H]", conn);
DataSet excelDataSet = new DataSet();
objDA.Fill(excelDataSet);
dt = excelDataSet.Tables[0];
excelDataSet.Dispose();
objDA.Dispose();
conn.Dispose();
return(dt);
}
} catch (Exception ex) {
System.Windows.MessageBox.Show(ex.ToString(), "Error", System.Windows.MessageBoxButton.OK, System.Windows.MessageBoxImage.Error);
return(null);
}
}
public static bool ExportToXLSX(string sheetToCreate, List <DataRow> selectedRows, System.Data.DataTable origDataTable, string tableName)
{
bool status = false;
System.Data.OleDb.OleDbConnection cn = new OleDbConnection();
try
{
char Space = ' ';
string dest = sheetToCreate;
if (File.Exists(dest))
{
File.Delete(dest);
}
sheetToCreate = dest;
if (tableName == null)
{
tableName = string.Empty;
}
tableName = tableName.Trim().Replace(Space, '_');
if (tableName.Length == 0)
{
tableName = origDataTable.TableName.Replace(Space, '_');
}
if (tableName.Length == 0)
{
tableName = "NoTableName";
}
if (tableName.Length > 30)
{
tableName = tableName.Substring(0, 30);
}
//Excel names are less than 31 chars
string queryCreateExcelTable = "CREATE TABLE [" + tableName + "] (";
Dictionary <string, string> colNames = new Dictionary <string, string>();
foreach (DataColumn dc in origDataTable.Columns)
{
//Cause the query to name each of the columns to be created.
string modifiedcolName = dc.ColumnName;//.Replace(Space, '_').Replace('.', '#');
string origColName = dc.ColumnName;
colNames.Add(modifiedcolName, origColName);
queryCreateExcelTable += "[" + modifiedcolName + "]" + " text,";
}
queryCreateExcelTable = queryCreateExcelTable.TrimEnd(new char[] { Convert.ToChar(",") }) + ")";
//adds the closing parentheses to the query string
if (selectedRows.Count > 65000 && sheetToCreate.ToLower().EndsWith(".xls"))
{
//use Excel 2007 for large sheets.
sheetToCreate = sheetToCreate.ToLower().Replace(".xls", string.Empty) + ".xlsx";
}
string strCn = string.Empty;
string ext = System.IO.Path.GetExtension(sheetToCreate).ToLower();
if (ext == ".xls")
{
strCn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sheetToCreate + "; Extended Properties='Excel 8.0;HDR=YES'";
}
if (ext == ".xlsx")
{
strCn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sheetToCreate + ";Extended Properties='Excel 12.0 Xml;HDR=YES' ";
}
if (ext == ".xlsb")
{
strCn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sheetToCreate + ";Extended Properties='Excel 12.0;HDR=YES' ";
}
if (ext == ".xlsm")
{
strCn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sheetToCreate + ";Extended Properties='Excel 12.0 Macro;HDR=YES' ";
}
cn = new System.Data.OleDb.OleDbConnection(strCn);
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(queryCreateExcelTable, cn);
cn.Open();
cmd.ExecuteNonQuery();
System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter("SELECT * FROM [" + tableName + "]", cn);
System.Data.OleDb.OleDbCommandBuilder cb = new System.Data.OleDb.OleDbCommandBuilder(da);
//creates the INSERT INTO command
cb.QuotePrefix = "[";
cb.QuoteSuffix = "]";
cmd = cb.GetInsertCommand();
//gets a hold of the INSERT INTO command.
foreach (DataRow row in selectedRows)
{
foreach (System.Data.OleDb.OleDbParameter param in cmd.Parameters)
{
param.Value = row[colNames[param.SourceColumn.Replace('#', '.')]];
}
cmd.ExecuteNonQuery(); //INSERT INTO command.
}
cn.Close();
cn.Dispose();
da.Dispose();
GC.Collect();
GC.WaitForPendingFinalizers();
status = true;
}
catch (Exception ex)
{
status = false;
if (cn.State == ConnectionState.Open)
{
cn.Close();
}
}
return(status);
}
public static int SaveRecord(string sql)
{
const int rv = 0;
try
{
string connectionString = ConfigurationManager.ConnectionStrings["LA3Access"].ConnectionString;
using (var conn = new OleDbConnection(connectionString))
{
conn.Open();
var cmGetID = new OleDbCommand("SELECT @@IDENTITY", conn);
var comm = new OleDbCommand(sql, conn) { CommandType = CommandType.Text };
comm.ExecuteNonQuery();
var ds = new DataSet();
var adapt = new OleDbDataAdapter(cmGetID);
adapt.Fill(ds);
adapt.Dispose();
cmGetID.Dispose();
return int.Parse(ds.Tables[0].Rows[0][0].ToString());
}
}
catch (Exception)
{
}
return rv;
}
