private String[] GetExcelSheetNames(string excelFile, bool blnXlsx = false)
{
System.Data.OleDb.OleDbConnection objConn = null;
System.Data.DataTable dt = null;
try
{
String connString = null;
if (blnXlsx)
{
connString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + excelFile + ";Extended Properties=\"Excel 12.0 Xml;HDR=NO;IMEX=1\"";
}
else
{
connString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelFile + ";Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1\"";
}
objConn = new System.Data.OleDb.OleDbConnection(connString);
objConn.Open();
dt = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
if (dt == null)
{
return null;
}
String[] excelSheets = new String[dt.Rows.Count];
int i = 0;
foreach (DataRow row in dt.Rows)
{
excelSheets[i] = row["TABLE_NAME"].ToString();
i += 1;
}
return excelSheets;
}
catch (Exception ex)
{
throw (new Exception("Cannot Read Excel Sheet Names -" + ex.Message));
}
finally
{
if (objConn != null)
{
objConn.Close();
objConn.Dispose();
}
if (dt != null)
{
dt.Dispose();
}
}
}
/// <summary>
/// Recebe o caminho do arquivo e o nome da Planilha excel e retorna em um datatable
/// </summary>
/// <param name="Path">Caminho do Arquivo</param>
/// <param name="PlanName">Nome da Planilha</param>
public static DataTable ImportaExel(string Path, string PlanName)
{
string cnnString = string.Empty;
if (Path.Substring(Path.Length - 1, 1).ToUpper() == "X")
{
cnnString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0 Xml;HDR=YES';", Path);
}
else
{
cnnString = String.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;""", Path);
}
string sql = "select * from [{0}$]";
System.Data.OleDb.OleDbConnection cnn = new System.Data.OleDb.OleDbConnection(cnnString);
System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(String.Format(sql, PlanName), cnn);
DataSet ds = new DataSet();
var dt = new DataTable();
try
{
cnn.Open();
da.Fill(ds);
dt = ds.Tables[0];
}
finally
{
cnn.Close();
cnn.Dispose();
da.Dispose();
ds.Dispose();
}
return(dt);
}
public DataSet navigate(string sortfield)
{
System.Data.OleDb.OleDbConnection conn = GetConnection();
DataSet ds = new DataSet();
try
{
int inc;
int MaxRows;
string sql = "select * from table1";
System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(sql, conn);
try
{
da.Fill(ds, "grade1");
MaxRows = ds.Tables["grade1"].Rows.Count;
inc = -1;
}
finally
{
da.Dispose();
}
return(ds);
}
finally
{
conn.Close();
conn.Dispose();
}
}
public System.Data.DataTable CreateDataTable(string connection, string query)
{
//create vars
var dataTable = new DataTable();
var oleConnection = new System.Data.OleDb.OleDbConnection(connection);
var oleCommand = new System.Data.OleDb.OleDbCommand(query, oleConnection);
//get data
System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter(oleCommand);
oleConnection.Open();
adapter.Fill(dataTable);
oleConnection.Close();
//clean up
oleConnection.Dispose();
adapter.Dispose();
oleCommand.Dispose();
//foreach (var rw in dataTable.Rows)
//{
//}
return(dataTable);
}
private void button5_Click(object sender, EventArgs e)
{
OpenFileDialog ofd = new OpenFileDialog();
ofd.DefaultExt = "*.xls;*.xlsx";
ofd.Filter = "Microsoft Excel (*.xls*)|*.xls*";
ofd.Title = "Выберите документ для загрузки данных";
if (ofd.ShowDialog() != DialogResult.OK)
{
MessageBox.Show("Вы не выбрали файл для открытия", "Загрузка данных...", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
String constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
ofd.FileName + ";Extended Properties='Excel 12.0 XML;HDR=YES;IMEX=1';";
System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(constr);
con.Open();
DataSet ds = new DataSet();
DataTable schemaTable = con.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
string sheet1 = (string)schemaTable.Rows[0].ItemArray[2];
string select = String.Format("SELECT * FROM [{0}]", sheet1);
System.Data.OleDb.OleDbDataAdapter ad = new System.Data.OleDb.OleDbDataAdapter(select, con);
ad.Fill(ds);
DataTable dt = ds.Tables[0];
con.Close();
con.Dispose();
dataGridView1.DataSource = dt;
}
public int isHomologyNote(string Table, string term1, string term2, string Value1, string Value2)
{
string tem_sql = "";//定义字符串变量
System.Data.OleDb.OleDbConnection tem_conn =
new System.Data.OleDb.OleDbConnection(connstr); //连接数据库
System.Data.OleDb.OleDbCommand tem_comm; //定义OleDbCommand类
tem_conn.Open(); //打开数据库的连接
//设置SQL语句，查找要添加的记录
tem_sql = "select top 1 * From " + Table + " where " + term1 + " = '" +
Value1 + "' and " + term2 + " = '" + Value2 + "'";
tem_comm = new System.Data.OleDb.OleDbCommand(tem_sql, tem_conn); //执行SQL语句
int RecordCount = 0; //定义变量
if (tem_comm.ExecuteScalar() == null) //如果查询为空
{
RecordCount = 0;
}
else
{
RecordCount = (int)tem_comm.ExecuteScalar(); //返回查找结果的个数
}
tem_conn.Close(); //关闭连接
tem_comm.Dispose(); //释放资源
tem_conn.Dispose(); //释放资源
return(RecordCount); //返回查询记录数量
}
public DataSet GetLoad(string sortfield)
{
System.Data.OleDb.OleDbConnection conn = GetConnection();
DataSet ds = new DataSet();
try
{
// Dim sql As String = "SELECT ID data1 as [Firstname],data2 as [Lastname],data3 as [20],data4 as [EG],data5 as [1st-10%],data6 as [50pt],data7 as [EG1],data8 as [2nd-10%],data9 as [50pts],data10 as [EG2],data11 as [20%],data12 as [100pt],data13 as [EG3],data14 as [30%],data15 as [100],data16 as [EG4],data17 as [2nd-30%],data18 as [PrelimGrade],data19 as [Remarks]FROM Table1 order by " + sortfield
string sql = "SELECT STUDENTID,data1 AS Firstname, data2 AS Lastname," + "data3 AS 20, data4 AS EG, data5 AS [1st-10%], data6 AS 50pt," + "data7 AS EG1, data8 AS [2nd-10%], data9 AS 50pts, data10 AS EG2," + "data11 AS [20%], data12 AS 100pt, data13 AS EG3, data14 AS [30%]," + "data15 AS 100, data16 AS EG4, data17 AS [2nd-30%], data18 AS PrelimGrade," + "data19 AS Remarks FROM Table1 ORDER BY ID";
System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(sql, conn);
try
{
da.Fill(ds, "grade1");
}
finally
{
da.Dispose();
}
return(ds);
}
finally
{
conn.Close();
conn.Dispose();
}
}
private void InstantiateConnectionObject(ado_data_access p_oAdo)
{
if (_oConn == null)
{
}
else
{
p_oAdo.CloseConnection(_oConn);
_oConn.Dispose();
_oConn = null;
}
_oConn = new System.Data.OleDb.OleDbConnection();
}
public static System.Data.DataTable GetDataByExcel(string FileName, string FileSheet)
{
System.Data.DataTable dt = new System.Data.DataTable();
//System.Data.OleDb.OleDbConnection ocon = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.12.0;Data Source=" + FileName + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";");
System.Data.OleDb.OleDbConnection ocon = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";");
System.Data.OleDb.OleDbDataAdapter oda = new System.Data.OleDb.OleDbDataAdapter("select * from [" + FileSheet + "$]", ocon);
oda.Fill(dt);
if (ocon.State == ConnectionState.Open)
{
ocon.Close();
}
ocon.Dispose();
return(dt);
}
public System.Data.DataSet ReadExcelFile(String filename)
{
// Create connection string variable.
String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + Server.MapPath("~/Images/" + filename) + ";" +
"Extended Properties=Excel 8.0;";
// Create connection object
System.Data.OleDb.OleDbConnection objConn = new System.Data.OleDb.OleDbConnection(sConnectionString);
// Open connection with the database.
objConn.Open();
// The code to follow uses a SQL SELECT command to display the data from the worksheet.
// Create new OleDbCommand to return data from worksheet.
System.Data.OleDb.OleDbCommand objCmdSelect = new System.Data.OleDb.OleDbCommand("SELECT * FROM [Products$]", objConn);
// Create new OleDbDataAdapter that is used to build a DataSet
// based on the preceding SQL SELECT statement.
System.Data.OleDb.OleDbDataAdapter objAdapter1 = new System.Data.OleDb.OleDbDataAdapter();
// Pass the Select command to the adapter.
objAdapter1.SelectCommand = objCmdSelect;
// Create new DataSet to hold information from the worksheet.
System.Data.DataSet objDataset1 = new System.Data.DataSet();
try
{
// Fill the DataSet with the information from the worksheet.
objAdapter1.Fill(objDataset1, "Products");
}
catch (Exception exp)
{
objConn.ResetState();
objConn.Dispose();
objConn.Close();
}
//objConn.Dispose();
// Clean up objects.
objConn.Close();
return(objDataset1);
}
public DataSet deldata(string sortfield)
{
System.Data.OleDb.OleDbConnection conn = GetConnection();
DataSet ds = new DataSet();
try
{
int inc;
int MaxRows;
//Dim sql As String = "select * from table1"
//Dim da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(sql, conn)
try
{
string sqldelete = default(string);
sqldelete = "DELETE * FROM table1 WHERE STUDENTID=\'" + Form1.Default.STUDID.Text + "\'";
System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(sqldelete, conn);
// Gets the records from the table and fills our adapter with those.
DataTable dt = new DataTable("grade1");
da.Fill(dt);
MessageBox.Show("Data has been deleted");
clearall();
Form1.Default.DataGridView1.DataSource = dt;
RefreshDGV();
}
finally
{
//da.Dispose()
}
return(ds);
}
finally
{
conn.Close();
conn.Dispose();
}
}
private static void CreateTable()
{
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection( );
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dbPath + ";user id=;password=;";
conn.Open( );
System.Data.OleDb.OleDbCommand cmd = conn.CreateCommand( );
try
{
Create_BASEDOCTOR(cmd);
Create_BASE_MZFP_ITEM(cmd);
Create_BASE_PATIENTTYPE(cmd);
Create_BASE_STAT_ITEM(cmd);
Create_BASE_TEMPLATE_DETAIL(cmd);
Create_BASE_EMPLOYEE_PROPERTY(cmd);
Create_BASE_DEPT_PROPERTY(cmd);
Create_BASE_USER(cmd);
Create_BASE_PATIENTTYPE_COST(cmd);
Create_BASE_ITEM_FAVORABLE(cmd);
Create_BASE_ITEMMX_FAVORABLE(cmd);
Create_BASE_WORK_UNIT(cmd);
Create_MZ_INVOICE(cmd);
Create_VI_MZ_SHOWCARD(cmd);
Create_MZ_PRESMASTER(cmd);
Create_MZ_PRESORDER(cmd);
Create_MZ_COSTMASTER(cmd);
Create_MZ_COSTORDER(cmd);
Create_MZ_PATLIST(cmd);
Create_MZ_ACCOUNT(cmd);
}
catch (Exception err)
{
throw err;
}
finally
{
conn.Close( );
}
cmd.Dispose( );
conn.Dispose( );
}
#pragma warning restore 1591
//------------------------------------------------------------------------------
//
// Method: Dispose
//
//------------------------------------------------------------------------------
/// <summary>
/// Provides a method to free unmanaged resources used by this class.
/// </summary>
/// <param name="disposing">Whether the method is being called as part of an explicit Dispose routine, and hence whether managed resources should also be freed.</param>
protected virtual void Dispose(bool disposing)
{
if (!disposed)
{
if (disposing)
{
// Free other state (managed objects).
}
// Free your own state (unmanaged objects).
if (oleDbConnection != null)
{
oleDbConnection.Close();
oleDbConnection.Dispose();
}
// Set large fields to null.
oleDbConnection = null;
disposed = true;
}
}
public int isHomologyNote(string Table, string term1, string term2, string Value1, string Value2)
{
string tem_sql = "";//定义字符串变量
System.Data.OleDb.OleDbConnection tem_conn =
new System.Data.OleDb.OleDbConnection(connstr);//连接数据库
System.Data.OleDb.OleDbCommand tem_comm;//定义OleDbCommand类
tem_conn.Open();//打开数据库的连接
//设置SQL语句，查找要添加的记录
tem_sql = "select top 1 * From " + Table + " where " + term1 + " = '" +
Value1 + "' and " + term2 + " = '" + Value2 + "'";
tem_comm = new System.Data.OleDb.OleDbCommand(tem_sql, tem_conn);//执行SQL语句
int RecordCount = 0;//定义变量
if (tem_comm.ExecuteScalar() == null)//如果查询为空
RecordCount = 0;
else
RecordCount = (int)tem_comm.ExecuteScalar();//返回查找结果的个数
tem_conn.Close();//关闭连接
tem_comm.Dispose();//释放资源
tem_conn.Dispose();//释放资源
return RecordCount;//返回查询记录数量
}
private void Main_Load(object sender, EventArgs e)
{
label_username.Text = username;
n = 312; remainingcard.Text = Convert.ToString(n);
for (int i = 0; i < 52; i++)
{
card[i, 0] = i + 1; card[i, 1] = 6;
}
//List<Image> list = new List<Image>();
wager_amount = 0;
String m_conn_str = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Application.StartupPath + @"\21.mdb;";
System.Data.OleDb.OleDbConnection m_conn = new System.Data.OleDb.OleDbConnection(m_conn_str);
m_conn.Open();
String query1 = "select * from BlackJack where ID = " + this.login_id;
System.Data.OleDb.OleDbDataAdapter dal = new System.Data.OleDb.OleDbDataAdapter(query1, m_conn);
DataTable dt = new DataTable();
dal.Fill(dt);
wager_amount = Convert.ToInt32(dt.Rows[0]["wager"].ToString());
total = Convert.ToInt32(dt.Rows[0]["total"].ToString());
win = Convert.ToInt32(dt.Rows[0]["win"].ToString());
draw = Convert.ToInt32(dt.Rows[0]["draw"].ToString());
lose = Convert.ToInt32(dt.Rows[0]["lose"].ToString());
winingpercent = Convert.ToDouble(dt.Rows[0]["winp"].ToString());
m_conn.Close();
m_conn.Dispose();
show();
hit.Enabled = false;
stand.Enabled = false;
split.Enabled = false;
Double.Enabled = false;
}
void end(int p)//结束，清屏，做收尾工作的函数
{
int i;
if (p == 0)//输
{
lose++; MessageBox.Show("you lose!");
}
else if (p == 1)//赢
{
win++;
wager_amount += 2 * Convert.ToInt32(tbx_wager.Text);
MessageBox.Show("you win!");
}
else if (p == 2)//平局
{
draw++;
wager_amount += Convert.ToInt32(tbx_wager.Text);
MessageBox.Show("draw!");
}
else if (p == 3)
{
pictureBox11.Image = null; pictureBox12.Image = null; pictureBox13.Image = null;
pictureBox14.Image = null; pictureBox15.Image = null;
for (int qwe = 0; qwe < 10; qwe++)//清空牌
{
player_card2[qwe] = 0;
}
}
total++;
winingpercent = Convert.ToDouble(win) / total;
splited = 0; end_1 = 0; wager2 = 0; card_sum1 = 0; card_sum2 = 0;
for (i = 0; i < 5; i++)
{
end_player(player_card, i); //清空桌面
}
for (i = 0; i < 5; i++)
{
end_dealer(dealer_card, i);
}
for (int j = 0; j < 10; j++)//清空牌
{
dealer_card[j] = 0;
player_card[j] = 0;
}
if (n <= 20)
{
MessageBox.Show("Shuffle!");
n = 312;
for (int k = 0; k < 52; k++)
{
card[k, 1] = 6;
}
}
if (wager_amount <= 0)
{
wager_amount = 500;
MessageBox.Show("$500 extra.Value your money!", "hahahameiqianleba");
}
remainingcard.Text = Convert.ToString(n);
show(); //显示数据
String t = Convert.ToString(total); //上传数据
String w = Convert.ToString(win);
String l = Convert.ToString(lose);
String wp = Convert.ToString(winingpercent);
String wa = Convert.ToString(wager_amount);
String d = Convert.ToString(draw);
String m_conn_str = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Application.StartupPath + @"\21.mdb;";
System.Data.OleDb.OleDbConnection m_conn = new System.Data.OleDb.OleDbConnection(m_conn_str);
m_conn.Open();
//string query1 = "insert into BlackJack([ID],[wager],[total],[win],[lose,winp]) values('" + a + "'," + wa +"','" + t + "','" + w + "','" + l + "','" + wp + "')";
string query1 = "update BlackJack set total='" + t + "',draw='" + d + "',wager='" + wa + "',win='" + w + "',lose='" + l + "',winp='" + wp + "' where [ID] =" + this.login_id;
System.Data.OleDb.OleDbCommand m_comm = new System.Data.OleDb.OleDbCommand(query1, m_conn);
m_comm.ExecuteNonQuery();
m_conn.Close();
m_conn.Dispose();
//准备下一局
hit.Enabled = false;
stand.Enabled = false;
split.Enabled = false;
Double.Enabled = false;
start.Enabled = true;
}
public void ExecImportDataToOracle(string tabname, string connectionAccess)
{
using (System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(connectionAccess)) {
#region 定义Access数据连接对象
string select = string.Format("SELECT AXISNUM,OVERLOADRATE,TOTALWEIGHT,TOTALTOLL,DISTANCE,VEHICLELICENSE,ENTRYSTATION,ENTRYSTATIONNAME,ENTRYTIME,EXITSTATION,EXITSTATIONNAME,EXITTIME,PAYTYPE FROM {0}", tabname);
connection.Open();
System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand(select, connection);
System.Data.OleDb.OleDbDataReader dataRead = command.ExecuteReader();
//获取记录总数
int size = 0;
int count = 0;
DataTable dtCount = new DataTable();
System.Data.OleDb.OleDbDataAdapter oleDa = new System.Data.OleDb.OleDbDataAdapter(
string.Format("select count(1) from {0}", tabname),
connectionAccess);
oleDa.Fill(dtCount);
count = Convert.ToInt32(dtCount.Rows[0][0].ToString());
#endregion
#region Oracle
#region Insert语句
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into BASE_BUS_OVERRUN(");
strSql.Append("AXISNUM,OVERLOADRATE,TOTALWEIGHT,TOTALTOLL,DISTANCE,VEHICLELICENSE,ENTRYSTATION,ENTRYSTATIONNAME,ENTRYTIME,EXITSTATION,EXITSTATIONNAME,EXITTIME,PAYTYPE");
strSql.Append(") values (");
strSql.Append(":AXISNUM,:OVERLOADRATE,:TOTALWEIGHT,:TOTALTOLL,:DISTANCE,:VEHICLELICENSE,:ENTRYSTATION,:ENTRYSTATIONNAME,:ENTRYTIME,:EXITSTATION,:EXITSTATIONNAME,:EXITTIME,:PAYTYPE");
strSql.Append(") ");
#endregion
#region Oracle参数对象
OracleParameter[] parameters =
{
new OracleParameter(":AXISNUM", OracleType.Number, 4),
new OracleParameter(":OVERLOADRATE", OracleType.Number, 4),
new OracleParameter(":TOTALWEIGHT", OracleType.Number, 4),
new OracleParameter(":TOTALTOLL", OracleType.Number, 4),
new OracleParameter(":DISTANCE", OracleType.Number, 4),
new OracleParameter(":VEHICLELICENSE", OracleType.VarChar, 200),
new OracleParameter(":ENTRYSTATION", OracleType.Number, 4),
new OracleParameter(":ENTRYSTATIONNAME", OracleType.VarChar, 200),
new OracleParameter(":ENTRYTIME", OracleType.DateTime),
new OracleParameter(":EXITSTATION", OracleType.Number, 4),
new OracleParameter(":EXITSTATIONNAME", OracleType.VarChar, 200),
new OracleParameter(":EXITTIME", OracleType.DateTime),
new OracleParameter(":PAYTYPE", OracleType.VarChar, 200)
};
#endregion
OracleConnection OraConnection = new OracleConnection(OracleHelper.ConnectionString);
OraConnection.Open();
OracleCommand Oracommand = OraConnection.CreateCommand();
Oracommand.CommandType = CommandType.Text;
Oracommand.CommandText = strSql.ToString();
#endregion
#region Begin
try {
while (dataRead.Read())
{
parameters[0].Value = dataRead["AXISNUM"];
parameters[1].Value = dataRead["OVERLOADRATE"];
parameters[2].Value = dataRead["TOTALWEIGHT"];
parameters[3].Value = dataRead["TOTALTOLL"];
parameters[4].Value = dataRead["DISTANCE"];
parameters[5].Value = dataRead["VEHICLELICENSE"];
parameters[6].Value = dataRead["ENTRYSTATION"];
parameters[7].Value = dataRead["ENTRYSTATIONNAME"];
parameters[8].Value = dataRead["ENTRYTIME"];
parameters[9].Value = dataRead["EXITSTATION"];
parameters[10].Value = dataRead["EXITSTATIONNAME"];
parameters[11].Value = dataRead["EXITTIME"];
parameters[12].Value = dataRead["PAYTYPE"];
foreach (OracleParameter pm in parameters)
{
Oracommand.Parameters.Add(pm);
}
Oracommand.ExecuteNonQuery();
Oracommand.Parameters.Clear();
size++;
if (On_CompleteSingle != null)
{
On_CompleteSingle(size, count);
}
}
} catch (Exception ex) {
throw ex;
} finally {
connection.Close();
connection.Dispose();
}
#endregion
}
}
public ActionResult Create(FormCollection collection, HttpPostedFileBase ArchAuto, HttpPostedFileBase ArchManual)
{
try
{
if (ArchAuto != null &&
ArchAuto.ContentLength > 0 &&
ArchAuto.FileName.EndsWith("txt") &&
ArchManual != null &&
ArchManual.ContentLength > 0 &&
(ArchManual.FileName.EndsWith("xls") || ArchManual.FileName.EndsWith("xlsx"))
)
{
string pathArchManual = "";
if (!Directory.Exists(Server.MapPath("Excels")))
{
Directory.CreateDirectory(Server.MapPath("Excels"));
}
var readerArchAuto = new BinaryReader(ArchAuto.InputStream);
string resultArchAuto = System.Text.Encoding.UTF8.GetString(readerArchAuto.ReadBytes(ArchAuto.ContentLength));
string[] lineasArchAuto = Regex.Split(resultArchAuto, "\r\n");
Relevamientos objRelevamiento = new Relevamientos();
objRelevamiento.Observaciones = collection["Observaciones"];
objRelevamiento.FechaCarga = DateTime.Now;
objRelevamiento.FechaInicio = DateTime.Parse(lineasArchAuto[0].Split(';')[0].ToString());
objRelevamiento.FechaFinal = DateTime.Parse(lineasArchAuto[lineasArchAuto.Length - 1].Split(';')[0].ToString());
objRelevamiento.IdEstado = 1;
objRelevamiento.IdTrampa = int.Parse(lineasArchAuto[0].Split(';')[2].ToString());
db.Relevamientos.Add(objRelevamiento);
//db.SaveChanges();
pathArchManual = Server.MapPath("Excels") + @"\" + ArchManual.FileName;
if (System.IO.File.Exists(pathArchManual))
{
System.IO.File.Delete(pathArchManual);
}
ArchManual.SaveAs(pathArchManual);
string cnnStr = "";
if (pathArchManual.EndsWith(".xlsx"))
{
//Excel 2007
cnnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=Yes;IMEX=1'";
cnnStr += ";Data Source=" + pathArchManual + ";";
}
else
{
//Excel 97-2003
//http://www.connectionstrings.com/excel (leer sobre la clave de registro TypeGuessRows)
cnnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
cnnStr += ";Data Source=" + pathArchManual + ";";
}
System.Data.OleDb.OleDbConnection oCnn = new System.Data.OleDb.OleDbConnection(cnnStr);
System.Data.OleDb.OleDbDataAdapter oDa = null;
DataTable dtArchManual = new DataTable();
try
{
oCnn.Open();
//Obtenemos los nombres de las hojas del Excel.
DataTable dtHojas = oCnn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
if (dtHojas.Rows.Count > 0)
{
string firstSheet = dtHojas.Rows[0]["TABLE_NAME"].ToString().Trim();
string selectCmd = "select * from [" + firstSheet + "]";
oDa = new System.Data.OleDb.OleDbDataAdapter(selectCmd, oCnn);
oDa.Fill(dtArchManual);
}
oCnn.Close();
dtArchManual.Columns.Add("Fecha");
foreach (DataRow drFila in dtArchManual.Rows)
{
int iAño = 0;
int iMes = 0;
int iDia = 0;
int.TryParse(drFila[0].ToString(), out iAño);
int.TryParse(drFila[1].ToString(), out iMes);
int.TryParse(drFila[2].ToString(), out iDia);
if (iAño > 0 && iMes > 0 && iDia > 0)
{
DateTime tFecha = new DateTime(iAño, iMes, iDia);
drFila["Fecha"] = tFecha.Date.ToShortDateString();
}
if (drFila["Fecha"].ToString() == objRelevamiento.FechaFinal.ToShortDateString())
{
foreach (DataColumn dcColumna in dtArchManual.Columns)
{
if (dcColumna.Ordinal > 2)
{
//CORREGIR SELECCION DE INSECTO
Insectos objInsecto = (from obj in db.Insectos where obj.NombreCientifico == dcColumna.ColumnName select obj).FirstOrDefault();
if (objInsecto != null)
{
int Cantidad = 0;
int.TryParse(drFila[dcColumna.Ordinal].ToString(), out Cantidad);
if (Cantidad > 0)
{
LecturasManuales objLecturasManuales = new LecturasManuales();
objLecturasManuales.IdRelevamiento = objRelevamiento.IdRelevamiento;
objLecturasManuales.IdInsecto = objInsecto.IdInsecto;
objLecturasManuales.Cantidad = Cantidad;
objLecturasManuales.IdEstado = 1;
db.LecturasManuales.Add(objLecturasManuales);
}
}
}
}
//db.SaveChanges();
break;
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (oCnn.State == ConnectionState.Open)
{
oCnn.Close();
}
}
if (oDa != null)
{
oDa.Dispose();
}
if (oCnn != null)
{
oCnn.Dispose();
}
//var objRelevamiento2 = (from obj in db.Relevamientos select obj).OrderByDescending(i => i.IdRelevamiento).First();
foreach (string Linea in lineasArchAuto)
{
string[] arrDatos = Linea.Split(';');
if (arrDatos.Length > 1 && arrDatos[1].ToString() == "LECTURA")
{
Lecturas objLecturas = new Lecturas();
objLecturas.IdEstado = 1;
objLecturas.IdRelevamiento = objRelevamiento.IdRelevamiento;
objLecturas.Frecuencia = double.Parse(arrDatos[2], System.Globalization.CultureInfo.InvariantCulture);
objLecturas.Aleteos = int.Parse(arrDatos[3]);
objLecturas.FechaLectura = DateTime.Parse(arrDatos[0]);
db.Lecturas.Add(objLecturas);
}
else if (arrDatos.Length > 1 && arrDatos[1].ToString() == "ESTADO")
{
Monitoreos objMonitoreos = new Monitoreos();
objMonitoreos.IdEstado = 1;
objMonitoreos.IdRelevamiento = objRelevamiento.IdRelevamiento;
objMonitoreos.Humedad = double.Parse(arrDatos[3], System.Globalization.CultureInfo.InvariantCulture);
objMonitoreos.Temperatura = double.Parse(arrDatos[2], System.Globalization.CultureInfo.InvariantCulture);
objMonitoreos.Bateria = double.Parse(arrDatos[4], System.Globalization.CultureInfo.InvariantCulture);
objMonitoreos.FechaMonitoreo = DateTime.Parse(arrDatos[0]);
db.Monitoreos.Add(objMonitoreos);
}
}
db.SaveChanges();
return(Redirect("~/Alarmas/GenerarAlarmas"));
}
else
{
return(View());
}
}
catch (Exception ex)
{
throw ex;
}
}
public List <DataTable> ReadXLSFile(List <string> listFiles)
{
listTables = new List <DataTable>();
DataSet dataSet1 = new DataSet();
DataTable dt = new DataTable();
String[] sheetName = null;
System.Data.OleDb.OleDbDataAdapter dataAdapter1;
try
{
foreach (var item in listFiles)
{
if (System.IO.Path.GetExtension(item).ToLower().Equals(".csv"))
{
dt = CSVReader.GetDataTable(item);
if (null == dt)
{
//log
return(null);
}
listTables.Add(dt);
}
else
{ //not cvs file -> XLSX file
//reading Excel sheets names
if (InitConnectionString(item) == false)
{
Log.Write(" InitConnectionString(item) == false", LogMessageType.Warning);
// Easynet.Edge.Services.FileImport.Classes.Convertors.Log.Write(@"D:\log.txt", "InitConnectionString(item) == false");
return(null);
}
if (OpenConnection() == false)
{
Log.Write("OpenConnection() == false", LogMessageType.Warning);
// Easynet.Edge.Services.FileImport.Classes.Convertors.Log.Write(@"D:\log.txt", "Cannot open connection !");
//write.WriteLine("Cannot open connection !");
return(null);
}
//write.WriteLine(" open connection !");
dt = _connection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
sheetName = new String[dt.Rows.Count];
int i = 0;
foreach (DataRow row in dt.Rows)
{
sheetName[i] = row["TABLE_NAME"].ToString();
i++;
}
dataAdapter1 = new System.Data.OleDb.OleDbDataAdapter("select * from [" + sheetName[0] + "]", _connection);
dataSet1 = new DataSet();
dataAdapter1.Fill(dataSet1);
// write.WriteLine(dataSet1.Tables[0].Rows.Count);
listTables.Add(dataSet1.Tables[0]);
Log.Write(" dataSet1.Tables[0]: rows count: " + dataSet1.Tables[0].Rows.Count, LogMessageType.Information);
_connection.Close();
}
}
_connection.Close();
// Easynet.Edge.Services.FileImport.Classes.Convertors.Log.Write(@"D:\log.txt", "_connection.Close();");
return(listTables);
}
catch (Exception ex)
{
Log.Write("error ReadXLSFile: ", ex);
// Easynet.Edge.Services.FileImport.Classes.Convertors.Log.Write(@"D:\log.txt", "error ReadXLSFile: " + ex.Message);
//WriteToEventLog("Converter: \n" + ex.ToString());
_connection.Close();
_connection.Dispose();
dt.Dispose();
dataSet1.Dispose();
return(null);
}
}
public DataSet SaveItem(string sortfield)
{
System.Data.OleDb.OleDbConnection conn = GetConnection();
try
{
string SQL = default(string);
SQL = "INSERT INTO TABLE1 (STUDENTID, DATA1, DATA2, DATA3, DATA4, DATA5,DATA6, DATA7," + "DATA8, DATA9, DATA10, DATA11, DATA12, DATA13, DATA14, DATA15, DATA16, DATA17, DATA18," + "DATA19) VALUES(@STUDENTID, @DATA1, @DATA2, @DATA3, @DATA4, @DATA5,@DATA6, @DATA7,DATA8, @DATA9," + "@DATA10, @DATA11, @DATA12, @DATA13, @DATA14, @DATA15, @DATA16, @DATA17, @DATA18, @DATA19)";
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(SQL, conn);
cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@STUDENTID", Form1.Default.STUDID.Text));
cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA1", Form1.Default.TextBox1.Text));
cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA2", Form1.Default.TextBox2.Text));
cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA3", Form1.Default.TextBox3.Text));
cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA4", Form1.Default.TextBox4.Text));
cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA5", Form1.Default.TextBox5.Text));
cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA6", Form1.Default.TextBox6.Text));
cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA7", Form1.Default.TextBox7.Text));
cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA8", Form1.Default.TextBox8.Text));
cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA9", Form1.Default.TextBox9.Text));
cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA10", Form1.Default.TextBox10.Text));
cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA11", Form1.Default.TextBox11.Text));
cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA12", Form1.Default.TextBox12.Text));
cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA13", Form1.Default.TextBox13.Text));
cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA14", Form1.Default.TextBox14.Text));
cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA15", Form1.Default.TextBox15.Text));
cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA16", Form1.Default.TextBox16.Text));
cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA17", Form1.Default.TextBox17.Text));
cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA18", Form1.Default.TextBox18.Text));
cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA19", Form1.Default.TextBox19.Text));
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
MessageBox.Show("New data is Added!");
Form1.Default.TextBox1.Text = "";
Form1.Default.TextBox2.Text = "";
Form1.Default.TextBox3.Text = "";
Form1.Default.TextBox4.Text = "";
Form1.Default.TextBox5.Text = "";
Form1.Default.TextBox6.Text = "";
Form1.Default.TextBox7.Text = "";
Form1.Default.TextBox8.Text = "";
Form1.Default.TextBox9.Text = "";
Form1.Default.TextBox10.Text = "";
Form1.Default.TextBox11.Text = "";
Form1.Default.TextBox12.Text = "";
Form1.Default.TextBox13.Text = "";
Form1.Default.TextBox14.Text = "";
Form1.Default.TextBox15.Text = "";
Form1.Default.TextBox16.Text = "";
Form1.Default.TextBox17.Text = "";
Form1.Default.TextBox18.Text = "";
Form1.Default.TextBox19.Text = "";
Form1.Default.STUDID.Text = "";
RefreshDGV();
}
finally
{
conn.Close();
conn.Dispose();
}
return(default(DataSet));
}
/// <summary>
/// InsertData
/// </summary>
/// <param name="fileName">模板文件在服务器端的完全路径</param>
/// <param name="theData"></param>
private bool InsertData(string fileName, DataTable theData)
{
// connection use oledb
DataTable dt = theData;
string strConn = string.Format(CONNECTION_STRING_WRITE, fileName);
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn);
System.Data.OleDb.OleDbCommand cmd = null;
bool bRet = false;
try
{
conn.Open();
//cmd = new System.Data.OleDb.OleDbCommand("Create table [sheet1]([name] Text, [age] int, [phone] Text)", conn);
//cmd.ExecuteNonQuery();
string strSQL = InsertSQL;
cmd = new System.Data.OleDb.OleDbCommand(strSQL, conn);
for (int i = 0; i < dt.Columns.Count; i++)
{
cmd.Parameters.Add(i.ToString(), System.Data.OleDb.OleDbType.VarChar);
}
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
cmd.Parameters[j.ToString()].Value = dt.Rows[i][j].ToString();
}
cmd.ExecuteNonQuery();
}
bRet = true;
}
catch (Exception e2)
{
bRet = false;
}
finally
{
if (cmd != null)
{
cmd.Dispose();
}
conn.Dispose();
}
return bRet;
}
public static bool DoExport(ExcelXMLLayout layout)
{
try
{
System.Data.OleDb.OleDbConnection conn =
new System.Data.OleDb.OleDbConnection(DB.ExcelFile.MakeConnectionString(layout.solution.path));
StringBuilder sb = new StringBuilder(1024 * 1024);
try
{
conn.Open();
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(
DB.ExcelFile.MakeSelectString(layout), conn);
try
{
System.Data.OleDb.OleDbDataReader r = cmd.ExecuteReader();
sb.Append("<?xml version='1.0' encoding='utf-8'?>");
sb.Append("<dataroot>");
while (r.Read())
{
if (IsHeadRow(r, layout))
{
continue;
}
sb.AppendFormat("<{0}>", layout.name);
for (int i = 0; i < r.FieldCount; ++i)
{
if (layout.typed)
{
sb.AppendFormat("<{0} type='{1}'>{2}</{0}>",
layout[i].name, ExcelXMLLayout.Type2Int(layout[i].type), r[i].ToString());
}
else
{
sb.AppendFormat("<{0}>{1}</{0}>", layout[i].name, r[i].ToString());
}
}
sb.AppendFormat("</{0}>", layout.name);
}
sb.Append("</dataroot>");
r.Dispose();
}
catch (Exception rErr)
{
lastError = string.Format("表格查询命令\r\n{0}", rErr.ToString());
cmd.Dispose();
conn.Close();
conn.Dispose();
return false;
}
cmd.Dispose();
conn.Close();
conn.Dispose();
}
catch (Exception connOpenErr)
{
lastError = string.Format("表格打开失败\r\n{0}", connOpenErr.ToString());
conn.Dispose();
return false;
}
//System.Diagnostics.Debug.WriteLine(sb.ToString());
try
{
System.IO.TextWriter w = System.IO.File.CreateText(layout.path);
w.Write(sb.ToString());
w.Close();
}
catch (Exception wErr)
{
lastError = string.Format("目标XML文件无法写入\r\n{0}", wErr.ToString());
return false;
}
return true;
}
catch (Exception outErr)
{
lastError = string.Format("Excel 无法打开\r\n{0}", outErr.ToString());
System.Diagnostics.Debug.WriteLine(outErr.ToString());
}
return false;
}
private void btnRun_Click(object sender, EventArgs e)
{
string sDataSourceFox = txtDBName.Text.Trim();
string sCnnStrJet = "Provider=" + sProviderJet + "; Data Source=" + sDataSourceJet;
string sCnnStrFox = "Provider=" + sProviderFox + "; Data Source=" + sDataSourceFox;
// Fox
System.Data.OleDb.OleDbConnection cnnFox = null;
System.Data.OleDb.OleDbCommand cmdFox = null;
System.Data.OleDb.OleDbDataAdapter daFox = null;
System.Data.DataSet daSet = null;
//MDB
System.Data.OleDb.OleDbConnection cnnJet = null;
System.Data.OleDb.OleDbCommand cmdJet = null;
string sSqlFox = "select * from " + System.IO.Path.GetFileName(sDataSourceFox.Trim());
bool is99 = false;
string sPagina = null;
int iTotalImg = 0;
int iTotalExp = 0;
string INSUREDID = string.Empty;
string PROVIDERNPI = string.Empty;
string SERVICEDATE = string.Empty;
string NOMBREIMAGEN = string.Empty;
try
{
//fox
cnnFox = new System.Data.OleDb.OleDbConnection();
cmdFox = new System.Data.OleDb.OleDbCommand();
daFox = new System.Data.OleDb.OleDbDataAdapter();
daSet = new DataSet();
//mdb
cnnJet = new System.Data.OleDb.OleDbConnection();
cmdJet = new System.Data.OleDb.OleDbCommand();
if (cnnFox.State == ConnectionState.Closed)
{
cnnFox.ConnectionString = sCnnStrFox;
cnnFox.Open();
}
cmdFox.Connection = cnnFox;
cmdFox.CommandType = CommandType.Text;
cmdFox.CommandText = sSqlFox;
daFox.SelectCommand = cmdFox;
daFox.Fill(daSet);
if (cnnJet.State == ConnectionState.Closed)
{
cnnJet.ConnectionString = sCnnStrJet;
cnnJet.Open();
}
cmdJet.Connection = cnnJet;
cmdJet.CommandType = CommandType.Text;
cmdJet.CommandText = "delete from OHCINDEX";
cmdJet.ExecuteNonQuery();
foreach (DataTable T in daSet.Tables)
{
foreach (DataRow R in T.Rows)
{
iTotalImg++;
sPagina = R["V1PG"].ToString().Trim();
if (sPagina != "99")
{
is99 = false;
iTotalExp++;
}
else
{
is99 = true;
}
if (is99 == false)
{
INSUREDID = R["V1INSUREID"].ToString().Trim();
PROVIDERNPI = RB1500.Checked ? R["V133ANPI"].ToString().Trim() : R["V156NPI"].ToString().Trim();
SERVICEDATE = R["V1DATEFROM"].ToString().Trim();
NOMBREIMAGEN = "D:\\OHC\\" + R["V0IFNAME01"].ToString().Trim();
}
else
{
NOMBREIMAGEN = "D:\\OHC\\" + R["V0IFNAME01"].ToString().Trim();
}
/*
* InsuredId
ProviderNpi
ServiceDate
ImageFile
*/
string sSqlJet = "insert into OHCINDEX VALUES('" + INSUREDID + "','" +
PROVIDERNPI + "','" +
SERVICEDATE + "','" +
NOMBREIMAGEN + "')";
cmdJet.CommandText = sSqlJet.Trim();
cmdJet.ExecuteNonQuery();
}
}//end Loop
//Close
cnnFox.Close();
cnnJet.Close();
cnnJet.Dispose();
cnnFox.Dispose();
// Copy MDB .... //
Program.copyImg(this.txtOutPut.Text.Trim(), sMdbPath, true, "*.mdb");
MessageBox.Show("Total de Imagenes:\r" + iTotalImg.ToString(), "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
MessageBox.Show("Total de Record:\r" + iTotalExp.ToString(), "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
MessageBox.Show("Done .. \r:-) Lindo Dia ", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public static List <TableInfo> GetShemaTables(DbConfig config = null)
{
if (config == null)
{
config = DbConfig.Default;
}
List <TableInfo> tables = new List <TableInfo>();
DataTable shemaTables = null;
int tabNameIndex = 0, tabRowCount = 0;
if (config.DbType == "System.Data.OleDb")
{
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection();
conn.ConnectionString = config.ConnectionString;
conn.Open();
shemaTables = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
tabNameIndex = shemaTables.Columns.IndexOf("TABLE_NAME");
tabRowCount = shemaTables.Rows.Count;
for (int i = 0; i < tabRowCount; i++)
{
DataRow tabDataRow = shemaTables.Rows[i];
string strTable = tabDataRow.ItemArray.GetValue(tabNameIndex).ToString();
DataTable dtColumns = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Columns, new object[] { null, null, strTable, null });
DataTable dtPrimarys = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Primary_Keys, new string[] { null, null, strTable });
int tColumnsCount = dtColumns.Rows.Count;
int tColumnNameIndex = dtColumns.Columns.IndexOf("COLUMN_NAME");
int tColumnTypeNameIndex = dtColumns.Columns.IndexOf("DATA_TYPE");
TableInfo tInfo = new TableInfo();
tInfo.Name = strTable;
tInfo.ColList = new List <ColumnInfo>();
for (int j = 0; j < tColumnsCount; j++)
{
DataRow drColumn = dtColumns.Rows[j];
ColumnInfo cInfo = new ColumnInfo();
cInfo.Name = drColumn.ItemArray.GetValue(tColumnNameIndex).ToString();
cInfo.TypeName = GetCSharpTypeByAccess(drColumn.ItemArray.GetValue(tColumnTypeNameIndex).ToString());
cInfo.Description = string.Empty;
foreach (DataRow row in dtPrimarys.Rows)
{
if (row["COLUMN_NAME"].ToString() == cInfo.Name)
{
cInfo.IsPrimaryKey = true;
cInfo.IsIdentity = true;
break;
}
}
tInfo.ColList.Add(cInfo);
}
tables.Add(tInfo);
}
conn.Close();
conn.Dispose();
conn = null;
}
else
{
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
conn.ConnectionString = config.ConnectionString;
conn.Open();
shemaTables = conn.GetSchema("Tables");
DataTable dtColumns = conn.GetSchema("Columns");
int colIndex = dtColumns.Columns.IndexOf("COLUMN_NAME");
int typIndex = dtColumns.Columns.IndexOf("DATA_TYPE");
tabNameIndex = shemaTables.Columns.IndexOf("TABLE_NAME");
tabRowCount = shemaTables.Rows.Count;
string sqlColumns = "SELECT OBJECT_NAME(c.object_id) [TableName],c.name [ColumnName],ISNULL(ex.value,'') [DescriptionName] FROM sys.columns c LEFT OUTER JOIN sys.extended_properties ex ON ex.major_id = c.object_id AND ex.minor_id = c.column_id AND ex.name = 'MS_Description' WHERE OBJECTPROPERTY(c.object_id, 'IsMsShipped')=0";
DataTable dtColumnDescriptions = new DataTable();
var adapter = new System.Data.SqlClient.SqlDataAdapter(sqlColumns, conn);
adapter.Fill(dtColumnDescriptions);
sqlColumns = "select o.name as [TableName],c.name as [ColumnName] from sysindexes i join sysindexkeys k on i.id = k.id and i.indid = k.indid join sysobjects o on i.id = o.id join syscolumns c on i.id=c.id and k.colid = c.colid where o.xtype = 'U' and exists(select 1 from sysobjects where xtype = 'PK' and name = i.name)";
DataTable dtColumnPrimaryKeys = new DataTable();
adapter = new System.Data.SqlClient.SqlDataAdapter(sqlColumns, conn);
adapter.Fill(dtColumnPrimaryKeys);
sqlColumns = "select b.name as [TableName],a.name as [ColumnName],a.is_identity as [IsIdentity] from sys.columns a inner join sys.objects b on a.object_id=b.object_id ";
DataTable dtColumnIdentitys = new DataTable();
adapter = new System.Data.SqlClient.SqlDataAdapter(sqlColumns, conn);
adapter.Fill(dtColumnIdentitys);
for (int i = 0; i < tabRowCount; i++)
{
DataRow itemDataRow = shemaTables.Rows[i];
string strTable = itemDataRow.ItemArray.GetValue(tabNameIndex).ToString();
DataRow[] drColumns = dtColumns.Select("TABLE_NAME='" + strTable + "'");
int drColumnsLength = drColumns.Length;
DataRow[] drDescriptions = dtColumnDescriptions.Select("TableName='" + strTable + "'");
DataRow[] drPrimaryKeys = dtColumnPrimaryKeys.Select("TableName='" + strTable + "'");
DataRow[] drIdentitys = dtColumnIdentitys.Select("TableName='" + strTable + "'");
TableInfo tInfo = new TableInfo();
tInfo.Name = strTable;
tInfo.ColList = new List <ColumnInfo>();
for (int j = 0; j < drColumnsLength; j++)
{
DataRow tmpDataRow = drColumns[j];
ColumnInfo cInfo = new ColumnInfo();
cInfo.Name = tmpDataRow.ItemArray.GetValue(colIndex).ToString();
cInfo.TypeName = GetCSharpTypeBySqlServer(tmpDataRow.ItemArray.GetValue(typIndex).ToString());
var tmpDescription = drDescriptions.FirstOrDefault(m => m["ColumnName"].ToString() == cInfo.Name);
cInfo.Description = (tmpDescription != null) ? tmpDescription["DescriptionName"].ToString() : string.Empty;
var tmpPrimaryKey = drPrimaryKeys.FirstOrDefault(m => m["ColumnName"].ToString() == cInfo.Name);
cInfo.IsPrimaryKey = (tmpPrimaryKey != null);
var tmpIsIdentity = drIdentitys.FirstOrDefault(m => m["ColumnName"].ToString() == cInfo.Name);
cInfo.IsIdentity = (tmpIsIdentity != null ? (tmpIsIdentity["IsIdentity"].ToString().ToLower() == "true" || tmpIsIdentity["IsIdentity"].ToString() == "1") : false);
tInfo.ColList.Add(cInfo);
}
tables.Add(tInfo);
}
conn.Close();
conn.Dispose();
conn = null;
}
return(tables);
}
public System.Data.DataSet GetDataSet_Excel(HttpPostedFileBase file, out string ErrorMessage)
{
using (iGst_Svc.GSTServiceClient iGstSvc = new iGst_Svc.GSTServiceClient())
{
System.Data.DataSet ds = null;
ErrorMessage = "";
try
{
System.Data.OleDb.OleDbConnection conn = null;
System.Data.OleDb.OleDbCommand cmd = null;
System.Data.OleDb.OleDbDataAdapter da = null;
string TableName = "", ConnectionString = "";
var fileName = System.IO.Path.GetFileName(file.FileName);
var path = System.IO.Path.Combine(Server.MapPath(System.Configuration.ConfigurationManager.AppSettings["UploadDataPath"].ToString()), fileName);
switch (System.IO.Path.GetExtension(path).Trim().ToUpper())
{
case ".XLS":
ConnectionString = string.Format(System.Configuration.ConfigurationManager.ConnectionStrings["Excel03ConnStr"].ConnectionString, path);
break;
case ".XLSX":
ConnectionString = string.Format(System.Configuration.ConfigurationManager.ConnectionStrings["Excel07ConnStr"].ConnectionString, path);
break;
default:
ErrorMessage = "Please select a valid excel file";
break;
}
if (ErrorMessage.Trim().Length == 0)
{
try
{
try
{
file.SaveAs(path);
}
catch (Exception ex)
{
Common.ErrorLog.LogErrors_Comments(ex.Message, "Master Page Controller", "Excel upload - Saveas", "Error");
ErrorMessage = "Problem in saving file:" + ex.Message;
return(null);
}
try
{
conn = new System.Data.OleDb.OleDbConnection(string.Format(System.Configuration.ConfigurationManager.ConnectionStrings["Excel15ConnStr"].ConnectionString, path));
conn.Open();
}
catch
{
try
{
conn = new System.Data.OleDb.OleDbConnection(string.Format(System.Configuration.ConfigurationManager.ConnectionStrings["Excel14ConnStr"].ConnectionString, path));
conn.Open();
}
catch
{
try
{
conn = new System.Data.OleDb.OleDbConnection(string.Format(System.Configuration.ConfigurationManager.ConnectionStrings["Excel13ConnStr"].ConnectionString, path));
conn.Open();
}
catch
{
try
{
conn = new System.Data.OleDb.OleDbConnection(string.Format(System.Configuration.ConfigurationManager.ConnectionStrings["Excel07ConnStr"].ConnectionString, path));
conn.Open();
}
catch
{
try
{
conn = new System.Data.OleDb.OleDbConnection(string.Format(System.Configuration.ConfigurationManager.ConnectionStrings["Excel03ConnStr"].ConnectionString, path));
conn.Open();
}
catch (Exception ex)
{
Common.ErrorLog.LogErrors_Comments(ex.Message, "Master Page Controller", "Excel upload - Connection", "Error");
ErrorMessage = "Problem in saving file:" + ex.Message;
return(null);
}
}
}
}
}
try
{
TableName = conn.GetSchema("Tables").Rows[0]["TABLE_NAME"].ToString();
cmd = new System.Data.OleDb.OleDbCommand(@"SELECT * FROM [" + TableName + "]", conn);
da = new System.Data.OleDb.OleDbDataAdapter(cmd);
ds = new System.Data.DataSet();
da.Fill(ds);
da.Dispose();
cmd.Dispose();
conn.Dispose();
return(ds);
}
catch (Exception ex)
{
Common.ErrorLog.LogErrors_Comments(ex.Message, "Master Page Controller", "Excel upload - GetDataSet", "Error");
ErrorMessage = "Inner Problem in reading file";
return(null);
}
}
catch (Exception ex)
{
Common.ErrorLog.LogErrors_Comments(ex.Message, "Master Page Controller", "Excel upload - GetDataSet", "Error");
ErrorMessage = "Problem in reading file";
return(null);
}
}
if (System.IO.File.Exists(path))
{
System.IO.File.Delete(path);
}
}
catch (Exception ex)
{
ErrorMessage = "Problem in reading file" + ex.Message;
return(null);
}
return(null);
}
}
private void ExportToXL(string sheetToCreate, List <DataRow> selectedRows, DataTable origDataTable, string tableName)
{
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' ";
}
System.Data.OleDb.OleDbConnection 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();
}
private void PrepareDatabase()
{
string sConnection = @"Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=" + TempBurnPath + @"\Data\RODB.DAT;Mode=Read|Write|Share Deny None;";
System.Data.OleDb.OleDbConnection AC = new System.Data.OleDb.OleDbConnection(sConnection);
try
{
AC.Open();
}
catch(System.Data.OleDb.OleDbException Err)
{
MessageBox.Show(Err.Message);
string peek = Err.Message;
return;
}
catch(Exception Err)
{
MessageBox.Show(Err.Message);
string sPeek = Err.Message;
return;
}
#region TheWork
System.Data.OleDb.OleDbCommand CM = new System.Data.OleDb.OleDbCommand();
CM.Connection = AC;
System.Data.OleDb.OleDbDataAdapter DA = new System.Data.OleDb.OleDbDataAdapter();
System.Data.DataTable DT = new System.Data.DataTable();
foreach(OCL.RecordingSession RS in SelectedRecordingSessions)
{
string sSQL = @"INSERT INTO tblSession(Description,IsPresentation,OwnerId,CreatedDate,IsOysterSession) VALUES('" +
RS.Description + "'" +
"," +Convert.ToInt32(RS.IsPresentation) +
"," + 23 +
",'" + RS.CreatedDate +"'" +
"," + Convert.ToInt32(true) + ")";
int SessionID = 0;
try
{
CM.CommandText = sSQL;
CM.ExecuteNonQuery();
CM.CommandText = "SELECT @@IDENTITY";
SessionID = (int)CM.ExecuteScalar();
}
catch(System.Data.OleDb.OleDbException Err)
{
MessageBox.Show(Err.Message);
string peek = Err.Message;
return;
}
catch(Exception Err)
{
MessageBox.Show(Err.Message);
string sPeek = Err.Message;
return;
}
foreach(OCL.Recording R in RS.CurrentRecordings(frmParent.LUser))
{
string FileName = "";
string FileExt = "";
string[] sFileName = R.Description.Split(".".ToCharArray());
if(sFileName.Length == 1)
{
FileExt = "";
}
else if(sFileName.Length == 2)
{
FileExt = "." + sFileName[1];
}
FileName = RS.Description + "_" + R.DisplayName;
string NewDescription = RenameFile(FileName,FileExt);
sSQL = @"INSERT INTO tblRecording(Name,Created,VideoStorageServerId," +
"DisplayName, StreamingEncoderId,SessionId," +
"IsDesktopCapture,IsPrimaryCamera,DiskSize,Playlength," +
"HasAudio,HasVideo) " +
"VALUES ('" + NewDescription +
"', '" + R.CreatedDate +
"', 9" +
", '" + R.DisplayName +
"', 15" +
"," + SessionID +
"," + Convert.ToInt32(R.IsDesktopCapture) +
"," + Convert.ToInt32(R.IsPrimaryCamera) +
"," + R.SizeOnDisk +
"," + R.PlayLength +
"," + Convert.ToInt32(R.HasAudio) +
"," + Convert.ToInt32(R.HasVideo) +
")";
try
{
CM.CommandText = sSQL;
CM.ExecuteNonQuery();
}
catch(System.Data.OleDb.OleDbException Err)
{
MessageBox.Show(Err.Message);
string peek = Err.Message;
return;
}
catch(Exception Err)
{
MessageBox.Show(Err.Message);
string sPeek = Err.Message;
return;
}
}
foreach(OCL.Note N in RS.AllVisibleNotes(frmParent.LUser))
{
int NoteID = 0;
sSQL = "INSERT INTO tblNotes(SyncTime,Description,SessionId,Created) VALUES('" +
N.SyncTime + "','" + N.Description + "'," + SessionID + ",'" +
N.Created +"'" +
")";
CM.CommandText = sSQL;
try
{
CM.CommandText = sSQL;
CM.ExecuteNonQuery();
CM.CommandText = "SELECT @@IDENTITY";
NoteID = (int)CM.ExecuteScalar();
}
catch(System.Data.OleDb.OleDbException Err)
{
MessageBox.Show(Err.Message);
string peek = Err.Message;
return;
}
catch(Exception Err)
{
MessageBox.Show(Err.Message);
string sPeek = Err.Message;
return;
}
OCL.Attachments FileAttachments = N.AllVisibleAttachments(frmParent.LUser);
foreach(OCL.Attachment A in FileAttachments)
{
sSQL = "INSERT INTO tblAttachments(OriginalName,StoredName,NoteId,Created,FileSize) VALUES(" +
" '" + A.OriginalName + "','" + A.StoredName +
"'," + NoteID.ToString() +
",'" + A.Created + "'" +
"," + A.FileSize +
")";
CM.CommandText = sSQL;
try
{
CM.ExecuteNonQuery();
}
catch(System.Data.OleDb.OleDbException Err)
{
MessageBox.Show(Err.Message);
string peek = Err.Message;
return;
}
catch(Exception Err)
{
MessageBox.Show(Err.Message);
string sPeek = Err.Message;
return;
}
}
}
}
DA.Dispose();
DT.Dispose();
CM.Dispose();
#endregion
AC.Close();
AC.Dispose();
System.Threading.Thread.SpinWait(500000);
}
private void btnImport_Click(object sender, EventArgs e)
{
Excel.Workbook WB = Globals.ThisAddIn.Application.ActiveWorkbook;
Excel.Worksheet activeSheet = (Excel.Worksheet)WB.ActiveSheet;
List <int> deletes = new List <int>();
string connString = Utilities.Constants.EXCEL_CONNSTRING;
for (int i = 0; i < checkedListBox2.Items.Count; i++)
{
if (checkedListBox2.GetItemChecked(i))
{
SourceFile fl = (SourceFile)checkedListBox2.Items[i];
string flnm = fl.FileName.Substring(fl.FileName.LastIndexOf("\\")).Replace("\\", "");
// create new worksheet
string newSheetNm = ExcelHelpers.CreateValidWorksheetName(WB, fl.ShortName, 0);
Excel.Worksheet newSheet = (Excel.Worksheet)WB.Sheets.Add(System.Type.Missing, WB.Sheets[WB.Sheets.Count], System.Type.Missing, System.Type.Missing);
newSheet.Name = newSheetNm;
// add custom properties
ExcelHelpers.addWorksheetCustomProperty(newSheet, Utilities.Constants.WS_ROLLUP, "TRUE");
ExcelHelpers.addWorksheetCustomProperty(newSheet, Utilities.Constants.WS_SRCFILE, flnm);
// import the data
DataTable dt = new DataTable();
connString = connString.Replace("{0}", fl.FileName);
System.Data.OleDb.OleDbConnection aConn = new System.Data.OleDb.OleDbConnection(connString);
aConn.Open();
System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(fl.SQLStatement, aConn);
try
{
da.Fill(dt);
}
catch
{
MessageBox.Show("An error was encountered while attempting to import sheets from workbook " + this.openFileDialog1.FileNames.GetValue(i).ToString() + ". Please re-run the data in a new workbook before including it in the corporate roll-up.");
break;
}
aConn.Close();
aConn.Dispose();
// copy the data to the new sheet
Excel.Range rng = newSheet.Rows.get_Resize(dt.Rows.Count + 1, dt.Columns.Count + 1).get_Offset(3, 0);
//rng.get_Offset(0, 0).get_Resize(1, 1).Value2 = "Name";
for (int c = 0; c < dt.Columns.Count; c++)
{
rng.get_Offset(0, c).get_Resize(1, 1).Value2 = dt.Columns[c].ColumnName;
}
rng.get_Offset(1, 0).get_Resize(dt.Rows.Count, dt.Columns.Count).Value2 = DataHelper.dataTableArrayObject(dt);
//rng.get_Offset(1, 0).get_Resize(dt.Rows.Count, 1).Formula = fl.ShortName;//"=IFERROR(RIGHT(CELL(\"filename\",$A$1), LEN(CELL(\"filename\",$A$1)) - FIND(\"]\",CELL(\"filename\",$A$1),1)),\"\")";
object[,] tmp1 = (object[, ])rng.Value2;
bool hasProd = false;
bool hasBuildSqFt = false;
fromEnergyCost = false;
foreach (DataRow dr in dt.Rows)
{
if (dr[0].ToString().Contains("Total Production Output") || dr[0].ToString().Contains("Production Energy Intensity (MMBtu/unit production)"))
{
hasProd = true;
}
if (dr[0].ToString().Contains("Building Energy Intensity"))
{
hasBuildSqFt = true;
}
if (dr[0].ToString().Contains("Estimated Cost Savings"))
{
fromEnergyCost = true;
}
}
//-----------------------------------------------
newSheet.ListObjects.Add(Microsoft.Office.Interop.Excel.XlListObjectSourceType.xlSrcRange, rng, System.Type.Missing, Excel.XlYesNoGuess.xlYes, System.Type.Missing);//.AddEx(Excel.XlListObjectSourceType.xlSrcRange, rng, System.Type.Missing, Excel.XlYesNoGuess.xlYes, System.Type.Missing, System.Type.Missing);
DetailTable newdt = new DetailTable(newSheet.ListObjects[1], fl.ShortName, fl.numOfSources, fl.fromActual, hasProd, hasBuildSqFt, true, fromEnergyCost);
newdt.DisplayName = newSheetNm + " (" + flnm + ")";
tables.Add(newdt);
}
}
files.Clear();
checkedListBox2.Refresh();
checkedListBox2.Visible = false;
btnImport.Visible = false;
// reload the tables box
AddTablesToList();
activeSheet.Activate();
// // copy the data to the new sheet
// int jslkdf = dt.Rows.Count;
// Excel.Range rng = newSheet.get_Range("A1").get_Resize(((dt.Rows.Count + 1)/2)+1, dt.Columns.Count + 1);
// rng.get_Offset(0, 0).get_Resize(1, 1).Value2 = "Name";
// for (int c = 0; c < dt.Columns.Count; c++)
// {
// rng.get_Offset(0, c + 1).get_Resize(1,1).Value2 = dt.Columns[c].ColumnName;
// }
// rng.get_Offset(1, 1).get_Resize(dt.Rows.Count, dt.Columns.Count).Value2 = DataHelper.dataTableArrayObject(dt);
// rng.get_Offset(1, 0).get_Resize(dt.Rows.Count, 1).Formula = "=IFERROR(RIGHT(CELL(\"filename\",$A$1), LEN(CELL(\"filename\",$A$1)) - FIND(\"]\",CELL(\"filename\",$A$1),1)),\"\")";
// object[,] tmp1 = (object[,])rng.Value2;
// newSheet.ListObjects.AddEx(Excel.XlListObjectSourceType.xlSrcRange, rng, System.Type.Missing, Excel.XlYesNoGuess.xlYes, System.Type.Missing, System.Type.Missing);
// DetailTable newdt = new DetailTable(newSheet.ListObjects[1], fl.ShortName);
// newdt.DisplayName = newSheetNm + " (" + flnm + ")";
// tables.Add(newdt);
// }
//}
//files.Clear();
//checkedListBox2.Refresh();
//checkedListBox2.Visible = false;
//btnImport.Visible = false;
//// reload the tables box
//AddTablesToList();
//activeSheet.Activate();
}
public ActionResult Create(FormCollection collection, HttpPostedFileBase ArchAuto, HttpPostedFileBase ArchManual)
{
try
{
if (ArchAuto != null &&
ArchAuto.ContentLength > 0 &&
ArchAuto.FileName.EndsWith("txt") &&
ArchManual != null &&
ArchManual.ContentLength > 0 &&
(ArchManual.FileName.EndsWith("xls") || ArchManual.FileName.EndsWith("xlsx"))
)
{
string pathArchManual = "";
if (!Directory.Exists(Server.MapPath("Excels"))) Directory.CreateDirectory(Server.MapPath("Excels"));
var readerArchAuto = new BinaryReader(ArchAuto.InputStream);
string resultArchAuto = System.Text.Encoding.UTF8.GetString(readerArchAuto.ReadBytes(ArchAuto.ContentLength));
string[] lineasArchAuto = Regex.Split(resultArchAuto, "\r\n");
Relevamientos objRelevamiento = new Relevamientos();
objRelevamiento.Observaciones = collection["Observaciones"];
objRelevamiento.FechaCarga = DateTime.Now;
objRelevamiento.FechaInicio = DateTime.Parse(lineasArchAuto[0].Split(';')[0].ToString());
objRelevamiento.FechaFinal = DateTime.Parse(lineasArchAuto[lineasArchAuto.Length - 1].Split(';')[0].ToString());
objRelevamiento.IdEstado = 1;
objRelevamiento.IdTrampa = int.Parse(lineasArchAuto[0].Split(';')[2].ToString());
db.Relevamientos.Add(objRelevamiento);
//db.SaveChanges();
pathArchManual = Server.MapPath("Excels") + @"\" + ArchManual.FileName;
if (System.IO.File.Exists(pathArchManual)) System.IO.File.Delete(pathArchManual);
ArchManual.SaveAs(pathArchManual);
string cnnStr = "";
if (pathArchManual.EndsWith(".xlsx"))
{
//Excel 2007
cnnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=Yes;IMEX=1'";
cnnStr += ";Data Source=" + pathArchManual + ";";
}
else
{
//Excel 97-2003
//http://www.connectionstrings.com/excel (leer sobre la clave de registro TypeGuessRows)
cnnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
cnnStr += ";Data Source=" + pathArchManual + ";";
}
System.Data.OleDb.OleDbConnection oCnn = new System.Data.OleDb.OleDbConnection(cnnStr);
System.Data.OleDb.OleDbDataAdapter oDa = null;
DataTable dtArchManual = new DataTable();
try
{
oCnn.Open();
//Obtenemos los nombres de las hojas del Excel.
DataTable dtHojas = oCnn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
if (dtHojas.Rows.Count > 0)
{
string firstSheet = dtHojas.Rows[0]["TABLE_NAME"].ToString().Trim();
string selectCmd = "select * from [" + firstSheet + "]";
oDa = new System.Data.OleDb.OleDbDataAdapter(selectCmd, oCnn);
oDa.Fill(dtArchManual);
}
oCnn.Close();
dtArchManual.Columns.Add("Fecha");
foreach (DataRow drFila in dtArchManual.Rows)
{
int iAño = 0;
int iMes = 0;
int iDia = 0;
int.TryParse(drFila[0].ToString(), out iAño);
int.TryParse(drFila[1].ToString(), out iMes);
int.TryParse(drFila[2].ToString(), out iDia);
if (iAño > 0 && iMes > 0 && iDia > 0)
{
DateTime tFecha = new DateTime(iAño, iMes, iDia);
drFila["Fecha"] = tFecha.Date.ToShortDateString();
}
if (drFila["Fecha"].ToString() == objRelevamiento.FechaFinal.ToShortDateString())
{
foreach (DataColumn dcColumna in dtArchManual.Columns)
{
if (dcColumna.Ordinal > 2)
{
//CORREGIR SELECCION DE INSECTO
Insectos objInsecto = (from obj in db.Insectos where obj.NombreCientifico == dcColumna.ColumnName select obj).FirstOrDefault();
if (objInsecto != null)
{
int Cantidad = 0;
int.TryParse(drFila[dcColumna.Ordinal].ToString(), out Cantidad);
if (Cantidad > 0)
{
LecturasManuales objLecturasManuales = new LecturasManuales();
objLecturasManuales.IdRelevamiento = objRelevamiento.IdRelevamiento;
objLecturasManuales.IdInsecto = objInsecto.IdInsecto;
objLecturasManuales.Cantidad = Cantidad;
objLecturasManuales.IdEstado = 1;
db.LecturasManuales.Add(objLecturasManuales);
}
}
}
}
//db.SaveChanges();
break;
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (oCnn.State == ConnectionState.Open) oCnn.Close();
}
if (oDa != null) { oDa.Dispose(); }
if (oCnn != null) { oCnn.Dispose(); }
//var objRelevamiento2 = (from obj in db.Relevamientos select obj).OrderByDescending(i => i.IdRelevamiento).First();
foreach (string Linea in lineasArchAuto)
{
string[] arrDatos = Linea.Split(';');
if (arrDatos.Length > 1 && arrDatos[1].ToString() == "LECTURA")
{
Lecturas objLecturas = new Lecturas();
objLecturas.IdEstado = 1;
objLecturas.IdRelevamiento = objRelevamiento.IdRelevamiento;
objLecturas.Frecuencia = double.Parse(arrDatos[2], System.Globalization.CultureInfo.InvariantCulture);
objLecturas.Aleteos = int.Parse(arrDatos[3]);
objLecturas.FechaLectura = DateTime.Parse(arrDatos[0]);
db.Lecturas.Add(objLecturas);
}
else if (arrDatos.Length > 1 && arrDatos[1].ToString() == "ESTADO")
{
Monitoreos objMonitoreos = new Monitoreos();
objMonitoreos.IdEstado = 1;
objMonitoreos.IdRelevamiento = objRelevamiento.IdRelevamiento;
objMonitoreos.Humedad = double.Parse(arrDatos[3], System.Globalization.CultureInfo.InvariantCulture);
objMonitoreos.Temperatura = double.Parse(arrDatos[2], System.Globalization.CultureInfo.InvariantCulture);
objMonitoreos.Bateria = double.Parse(arrDatos[4], System.Globalization.CultureInfo.InvariantCulture);
objMonitoreos.FechaMonitoreo = DateTime.Parse(arrDatos[0]);
db.Monitoreos.Add(objMonitoreos);
}
}
db.SaveChanges();
return Redirect("~/Alarmas/GenerarAlarmas");
}
else
{
return View();
}
}
catch (Exception ex)
{
throw ex;
}
}
public void SaveScenarioProperties()
{
bool bOptimizer;
string strDesc = "";
string strSQL = "";
System.Text.StringBuilder strFullPath;
m_intError = 0;
//validate the input
//
//Optimization id
//
if (this.txtScenarioId.Text.Length == 0)
{
MessageBox.Show("Enter A Unique Optimization scenario Id");
this.txtScenarioId.Focus();
m_intError = -1;
return;
}
//
//check for duplicate scenario id
//
System.Data.OleDb.OleDbConnection oConn = new System.Data.OleDb.OleDbConnection();
string strProjDir = frmMain.g_oFrmMain.getProjectDirectory();
string strScenarioDir = strProjDir + "\\" + ScenarioType + "\\db";
string strFile = "scenario_" + ScenarioType + "_rule_definitions.mdb";
strFullPath = new System.Text.StringBuilder(strScenarioDir);
strFullPath.Append("\\");
strFullPath.Append(strFile);
ado_data_access oAdo = new ado_data_access();
string strConn = oAdo.getMDBConnString(strFullPath.ToString(), "admin", "");
oAdo.SqlQueryReader(strConn, "select scenario_id from scenario");
if (oAdo.m_OleDbDataReader.HasRows)
{
while (oAdo.m_OleDbDataReader.Read())
{
if (oAdo.m_OleDbDataReader["scenario_id"] != System.DBNull.Value)
{
if (this.txtScenarioId.Text.Trim().ToUpper() ==
Convert.ToString(oAdo.m_OleDbDataReader["scenario_id"]).Trim().ToUpper())
{
this.m_intError = -1;
MessageBox.Show("Cannot have a duplicate Optimization scenario id");
oAdo.m_OleDbDataReader.Close();
oAdo.m_OleDbDataReader = null;
oAdo = null;
this.txtScenarioId.Focus();
return;
}
}
}
}
else
{
}
oAdo.m_OleDbDataReader.Close();
oAdo.m_OleDbDataReader = null;
//
//create the scenario path if it does not exist and
//copy the scenario_results.mdb to it
//
try
{
if (!System.IO.Directory.Exists(this.txtScenarioPath.Text))
{
System.IO.Directory.CreateDirectory(this.txtScenarioPath.Text);
System.IO.Directory.CreateDirectory(this.txtScenarioPath.Text.ToString() + "\\db");
//copy default processor scenario_results database to the new project directory
if (this.ScenarioType == "processor")
{
dao_data_access oDao = new dao_data_access();
string strDestFile = this.txtScenarioPath.Text + "\\" + Tables.ProcessorScenarioRun.DefaultHarvestCostsTableDbFile;
oDao.CreateMDB(strDestFile);
oDao.m_DaoWorkspace.Close();
oDao = null;
string strScenarioResultsConn = oAdo.getMDBConnString(strDestFile, "", "");
System.Data.OleDb.OleDbConnection OleDbScenarioResultsConn = new System.Data.OleDb.OleDbConnection();
oAdo.OpenConnection(strScenarioResultsConn, ref OleDbScenarioResultsConn);
frmMain.g_oTables.m_oProcessor.CreateHarvestCostsTable(
oAdo,
OleDbScenarioResultsConn,
Tables.ProcessorScenarioRun.DefaultHarvestCostsTableName);
frmMain.g_oTables.m_oProcessor.CreateTreeVolValSpeciesDiamGroupsTable(
oAdo,
OleDbScenarioResultsConn,
Tables.ProcessorScenarioRun.DefaultTreeVolValSpeciesDiamGroupsTableName);
OleDbScenarioResultsConn.Close();
OleDbScenarioResultsConn.Dispose();
}
}
}
catch
{
MessageBox.Show("Error Creating Folder");
m_intError = -1;
return;
}
//
//copy the project data source values to the scenario data source
//
string strProjDBDir = frmMain.g_oFrmMain.frmProject.uc_project1.txtRootDirectory.Text.Trim() + "\\db";
string strProjFile = "project.mdb";
StringBuilder strProjFullPath = new StringBuilder(strProjDBDir);
strProjFullPath.Append("\\");
strProjFullPath.Append(strProjFile);
string strProjConn = oAdo.getMDBConnString(strProjFullPath.ToString(), "admin", "");
System.Data.OleDb.OleDbConnection p_OleDbProjConn = new System.Data.OleDb.OleDbConnection();
oAdo.OpenConnection(strProjConn, ref p_OleDbProjConn);
string strScenarioDBDir = frmMain.g_oFrmMain.frmProject.uc_project1.txtRootDirectory.Text.Trim() + "\\" + ScenarioType + "\\db";
string strScenarioFile = "scenario_" + ScenarioType + "_rule_definitions.mdb";
StringBuilder strScenarioFullPath = new StringBuilder(strScenarioDBDir);
strScenarioFullPath.Append("\\");
strScenarioFullPath.Append(strScenarioFile);
string strScenarioConn = oAdo.getMDBConnString(strScenarioFullPath.ToString(), "admin", "");
oAdo.OpenConnection(strScenarioConn);
if (oAdo.m_intError == 0)
{
if (this.txtDescription.Text.Trim().Length > 0)
{
strDesc = oAdo.FixString(this.txtDescription.Text.Trim(), "'", "''");
}
strSQL = "INSERT INTO scenario (scenario_id,description,Path,File) VALUES " + "('" + this.txtScenarioId.Text.Trim() + "'," +
"'" + strDesc + "'," +
"'" + this.txtScenarioPath.Text.Trim() + "','scenario_" + ScenarioType + "_rule_definitions.mdb');";
oAdo.SqlNonQuery(oAdo.m_OleDbConnection, strSQL);
oAdo.SqlQueryReader(p_OleDbProjConn, "select * from datasource");
if (oAdo.m_intError == 0)
{
try
{
while (oAdo.m_OleDbDataReader.Read())
{
bOptimizer = false;
switch (oAdo.m_OleDbDataReader["table_type"].ToString().Trim().ToUpper())
{
case "PLOT":
bOptimizer = true;
break;
case "CONDITION":
bOptimizer = true;
break;
//case "FIRE AND FUEL EFFECTS":
// bCore = true;
// break;
//case "HARVEST COSTS":
// bCore = true;
// break;
case "ADDITIONAL HARVEST COSTS":
bOptimizer = true;
break;
case "TREATMENT PRESCRIPTIONS":
bOptimizer = true;
break;
//case "TREE VOLUMES AND VALUES BY SPECIES AND DIAMETER GROUPS":
// bCore = true;
// break;
case "TRAVEL TIMES":
bOptimizer = true;
break;
case "PROCESSING SITES":
bOptimizer = true;
break;
//case "TREE SPECIES AND DIAMETER GROUPS DOLLAR VALUES":
// bCore = true;
// break;
case "PLOT AND CONDITION RECORD AUDIT":
if (ScenarioType == "optimizer")
{
bOptimizer = true;
}
break;
case "PLOT, CONDITION AND TREATMENT RECORD AUDIT":
if (ScenarioType == "optimizer")
{
bOptimizer = true;
}
break;
case "TREE":
if (ScenarioType == "processor")
{
bOptimizer = true;
}
break;
case "HARVEST METHODS":
if (ScenarioType == "processor")
{
bOptimizer = true;
}
break;
case "TREATMENT PACKAGES":
bOptimizer = true;
break;
//case "FVS TREE LIST FOR PROCESSOR":
// if (ScenarioType=="processor") bCore=true;
// break;
case "TREE SPECIES":
if (ScenarioType == "processor")
{
bOptimizer = true;
}
break;
case "TREATMENT PRESCRIPTIONS HARVEST COST COLUMNS":
if (ScenarioType == "processor")
{
bOptimizer = true;
}
break;
case "FIA TREE SPECIES REFERENCE":
if (ScenarioType == "processor")
{
bOptimizer = true;
}
break;
default:
break;
}
if (bOptimizer == true)
{
strSQL = "INSERT INTO scenario_datasource (scenario_id,table_type,Path,file,table_name) VALUES " + "('" + this.txtScenarioId.Text.Trim() + "'," +
"'" + oAdo.m_OleDbDataReader["table_type"].ToString().Trim() + "'," +
"'" + oAdo.m_OleDbDataReader["path"].ToString().Trim() + "'," +
"'" + oAdo.m_OleDbDataReader["file"].ToString().Trim() + "'," +
"'" + oAdo.m_OleDbDataReader["table_name"].ToString().Trim() + "');";
oAdo.SqlNonQuery(oAdo.m_OleDbConnection, strSQL);
}
}
}
catch (Exception caught)
{
m_intError = -1;
m_strError = caught.Message;
MessageBox.Show(strError);
}
oAdo.m_OleDbDataReader.Close();
oAdo.m_OleDbDataReader = null;
oAdo.m_OleDbCommand = null;
p_OleDbProjConn.Close();
p_OleDbProjConn = null;
}
if (ScenarioType.Trim().ToUpper() == "OPTIMIZER")
{
string strTemp = oAdo.FixString("SELECT @@PlotTable@@.* FROM @@PlotTable@@ WHERE @@PlotTable@@.plot_accessible_yn='Y'", "'", "''");
strSQL = "INSERT INTO scenario_plot_filter (scenario_id,sql_command,current_yn) VALUES " + "('" + this.txtScenarioId.Text.Trim() + "'," +
"'" + strTemp + "'," +
"'Y');";
oAdo.SqlNonQuery(oAdo.m_OleDbConnection, strSQL);
strTemp = oAdo.FixString("SELECT @@CondTable@@.* FROM @@CondTable@@", "'", "''");
strSQL = "INSERT INTO scenario_cond_filter (scenario_id,sql_command,current_yn) VALUES " + "('" + this.txtScenarioId.Text.Trim() + "'," +
"'" + strTemp + "'," +
"'Y');";
oAdo.SqlNonQuery(oAdo.m_OleDbConnection, strSQL);
}
}
oAdo.m_OleDbConnection.Close();
oAdo.m_OleDbConnection = null;
oAdo = null;
}
private void button1_Click(object sender, EventArgs e)
{
String m_conn_str = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Application.StartupPath + @"\21.mdb;";
System.Data.OleDb.OleDbConnection m_conn = new System.Data.OleDb.OleDbConnection(m_conn_str);
m_conn.Open();
if (textBox_name.Text.Trim() == "" || textBox_password.Text.Trim() == "" || textBox_password_repeat.Text.Trim() == "")
{
if (textBox_name.Text.Trim() == "")
{
MessageBox.Show("name cannot be empty!", "wrong");
}
if (textBox_password.Text.Trim() == "")
{
MessageBox.Show("password cannot be empty!", "wrong");
}
if (textBox_password_repeat.Text.Trim() == "")
{
MessageBox.Show("comfirm password cannot be empty!", "wrong");
}
goto end;
}//非空判断
String name = textBox_name.Text.Trim();
String password = textBox_password.Text.Trim();
String confirm_pass = textBox_password_repeat.Text.Trim();
String query2 = "select count(*) from BlackJack where user_id = '" + name + "'";
System.Data.OleDb.OleDbCommand m_comm2 = new System.Data.OleDb.OleDbCommand(query2, m_conn);
int check = Convert.ToInt32(m_comm2.ExecuteScalar());
if (check != 0)
{
MessageBox.Show("this username has already existed!", "warning");
goto end;
}
if (password == confirm_pass)
{
string query1 = "insert into BlackJack (user_id,wager,[password],total,win,draw,lose,winp) values('" + name + "','" + 30000 + "','" + password + "','" + 0 + "','" + 0 + "','" + 0 + "','" + 0 + "','" + 0 + "')";
System.Data.OleDb.OleDbCommand m_comm = new System.Data.OleDb.OleDbCommand(query1, m_conn);
m_comm.ExecuteNonQuery();
MessageBox.Show("success!");
m_conn.Close();
m_conn.Dispose();
Login login1 = new Login();
this.Hide();
Dispose();
login1.Show();
}
else
{
MessageBox.Show("Your new and confirm password are different. Please enter your passwords again.", "wrong");
textBox_password_repeat.Clear();
}
end : m_conn.Close();
m_conn.Dispose();
}