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();
}
}
}
protected void btnGo_Click(object sender, EventArgs e)
{
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection();
string sql = "";
System.Data.DataSet ds = new System.Data.DataSet();
System.Data.OleDb.OleDbDataReader dr;
System.Data.OleDb.OleDbCommand comm = new System.Data.OleDb.OleDbCommand();
//http://www.c-sharpcorner.com/UploadFile/dchoksi/transaction02132007020042AM/transaction.aspx
//get this from connectionstrings.com/access
conn.ConnectionString = txtConnString.Text;
conn.Open();
//here I can talk to my db...
System.Data.OleDb.OleDbTransaction Trans;
Trans = conn.BeginTransaction(System.Data.IsolationLevel.Chaos);
try
{
comm.Connection = conn;
comm.Transaction = Trans;
// Trans.Begin();
//Console.WriteLine(conn.State);
sql = txtSQL.Text;
comm.CommandText = sql;
if (sql.ToLower().IndexOf("select") == 0)
{
dr = comm.ExecuteReader();
while (dr.Read())
{
txtresults.Text = dr.GetValue(0).ToString();
}
}
else
{
txtresults.Text = comm.ExecuteNonQuery().ToString();
}
Trans.Commit();
}
catch (Exception ex)
{
txtresults.Text = ex.Message;
Trans.Rollback();
}
finally
{
comm.Dispose();
conn.Close();
conn = null;
}
}
public DataTable SelectToDataTable(string sql)
{
//set connection
System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection();
con.ConnectionString = connectString;
//set commandtext
System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand();
command.CommandText = sql;
command.Connection = con;
//set adapter
System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter();
adapter.SelectCommand = command;
//creat a datatable
DataTable dt = new DataTable();
try
{
//open this connection
con.Open();
adapter.Fill(dt);
}
catch (Exception ex)
{
//throw new Exception
con.Close();
}
finally
{
//close this connection
con.Close();
}
//return a datatable
return dt;
}
protected void btnInsert_Click(object sender, EventArgs e)
{
System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection();
Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("app_data/products.mdb");
Conn.Open();
//Response.Write(Conn.State);
string strSQL = "insert into [users] ([username], firstname, lastname, email, [password]) values (?,?,?,?,?)";
object returnVal;
System.Data.OleDb.OleDbCommand Comm = new System.Data.OleDb.OleDbCommand();
Comm.Connection = Conn;
Comm.CommandText = "select [username] from [users] where [username] = ?";
Comm.Parameters.AddWithValue("[username]", txtUserName.Text);
returnVal = Comm.ExecuteScalar();
if (returnVal == null)
{
Comm.Parameters.Clear();
Comm.CommandText = strSQL;
Comm.Parameters.AddWithValue("username", txtUserName.Text);
Comm.Parameters.AddWithValue("firstname", txtFName.Text);
Comm.Parameters.AddWithValue("lastname", txtLName.Text);
Comm.Parameters.AddWithValue("email", txtEmail.Text);
Comm.Parameters.AddWithValue("password", txtPassword.Text);
Comm.ExecuteNonQuery();
}
else {
Response.Write("Username already exists.");
}
Conn.Close();
Conn = null;
}
public Form1()
{
InitializeComponent();
comboBox2.Text = "Column";
comboBox2.Items.Add("Column");
comboBox2.Items.Add("Lines");
comboBox2.Items.Add("Pie");
comboBox2.Items.Add("Bar");
comboBox2.Items.Add("Funnel");
comboBox2.Items.Add("PointAndFigure");
comboBox1.Items.Clear();
if (System.IO.File.Exists("your_base.mdb"))
{
string conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=your_base.mdb;Jet OLEDB:Engine Type=5";
System.Data.OleDb.OleDbConnection connectDb = new System.Data.OleDb.OleDbConnection(conStr);
connectDb.Open();
DataTable cbTb = connectDb.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
foreach (DataRow row in cbTb.Rows)
{
string tbName = row["TABLE_NAME"].ToString();
comboBox1.Items.Add(tbName);
}
connectDb.Close();
}
}
public int AddEvent(String eventName, String eventRoom,
String eventAttendees, String eventDate)
{
System.Data.OleDb.OleDbConnection oleDbConnection1;
System.Data.OleDb.OleDbDataAdapter daEvents;
DataSet ds;
oleDbConnection1 = new System.Data.OleDb.OleDbConnection();
oleDbConnection1.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=C:\\Inetpub\\wwwroot\\PCSWebApp3\\PCSWebApp3.mdb";
String oleDbCommand = "INSERT INTO Events (Name, Room, AttendeeList," +
" EventDate) VALUES ('" + eventName + "', '" +
eventRoom + "', '" + eventAttendees + "', '" +
eventDate + "')";
System.Data.OleDb.OleDbCommand insertCommand =
new System.Data.OleDb.OleDbCommand(oleDbCommand,
oleDbConnection1);
oleDbConnection1.Open();
int queryResult = insertCommand.ExecuteNonQuery();
if (queryResult == 1)
{
daEvents = new System.Data.OleDb.OleDbDataAdapter(
"SELECT * FROM Events", oleDbConnection1);
ds = (DataSet)Application["ds"];
ds.Tables["Events"].Clear();
daEvents.Fill(ds, "Events");
Application.Lock();
Application["ds"] = ds;
Application.UnLock();
oleDbConnection1.Close();
}
return queryResult;
}
/// <summary>
/// Create access database file
/// </summary>
public static void create_mdb_file(DataSet ds, string destination, string template)
{
if(SqlConvert.ToString(template) == "")
throw new Err("You must specify the location of a template mdb file inherit from.");
if(!System.IO.File.Exists(template))
throw new Err("The specified template file \"" + template + "\" could not be found.");
if(SqlConvert.ToString(destination) == "")
throw new Err("You must specify the destination location and filename of the mdb file to create.");
//COPY THE TEMPLATE AND CREATE DESTINATION FILE
System.IO.File.Copy(template,destination,true);
//CONNECT TO THE DESTINATION FILE
string sconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + destination + ";";
System.Data.OleDb.OleDbConnection oconn = new System.Data.OleDb.OleDbConnection(sconn);
oconn.Open();
System.Data.OleDb.OleDbCommand ocmd = new System.Data.OleDb.OleDbCommand();
ocmd.Connection = oconn;
//WRITE TO THE DESTINATION FILE
try
{
oledb.insert_dataset(ds,ocmd);
}
catch(Exception ex)
{
//System.Web.HttpContext.Current.Response.Write(ex.ToString());
General.Debugging.Report.SendError("Error exporting to MS Access", ex.ToString() + "\r\n\r\n\r\n" + ocmd.CommandText);
}
finally
{
// Close the connection.
oconn.Close();
}
}
public void write_log_file(Form1 f,String type_of_network)
{
this.f = f;
int time = f.time;
Console.Write("Time in excel : " + time);
Console.Write("Selected network : " + type_of_network);
String network_type = make_network_string(type_of_network);
nodes = Convert.ToString(f.no_of_nodes);
String connections = make_connections_string();
if (System.IO.File.Exists("log.xls"))
file_exists = true;
// MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Users\\ABHISHEK\\ExcelData1.xls;;Extended Properties=Excel 8.0;");
MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source = log.xls;;Extended Properties=Excel 8.0;");
MyConnection.Open();
myCommand.Connection = MyConnection;
if (!file_exists)
{
Console.Write("does not exist");
sql = "CREATE TABLE Simulation (TypeofNetwork String, Nodes int, Packets int,Seeder_Nodes int,Seeder_Packets int,Download_edges int,Upload_edges int,Ratio float,Thresh_Probability float,Nodes_row int,Connections String,Alturists int,Traders int,Parasites int,Time_taken int)";
myCommand.CommandText = sql;
myCommand.ExecuteNonQuery();
}
sql = "INSERT INTO [Simulation$] (TypeofNetwork, Nodes, Packets,Seeder_Nodes,Seeder_Packets,Download_edges,Upload_edges,Ratio,Thresh_Probability,Nodes_row,Connections,Alturists,Traders,Parasites,Time_taken) values (" + " ' " + network_type + " ' " + "," + nodes + "," + f.no_of_packets.ToString() + "," + f.seeder_nodes.ToString() + "," + f.seeder_packets.ToString() + "," + f.no_of_download_edges.ToString() + "," + f.no_of_upload_edges.ToString() + "," + f.ratio.ToString() + "," + (f.threshold_probability / 100.0).ToString() + "," + f.widthstep.ToString() + "," + "'" + connections +"'" + "," +f.alturists.ToString() + "," + f.traders.ToString() + "," + f.parasites.ToString()+"," +time.ToString()+ ")";
myCommand.CommandText = sql;
myCommand.ExecuteNonQuery();
MyConnection.Close();
}
protected void Application_Start(Object sender, EventArgs e)
{
System.Data.DataSet ds;
System.Data.OleDb.OleDbConnection oleDbConnection1;
System.Data.OleDb.OleDbDataAdapter daAttendees;
System.Data.OleDb.OleDbDataAdapter daRooms;
System.Data.OleDb.OleDbDataAdapter daEvents;
oleDbConnection1 = new System.Data.OleDb.OleDbConnection();
oleDbConnection1.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=C:\Inetpub\wwwroot\PCSWebApp3\PCSWebApp3.mdb;Mode=ReadWrite|Share Deny None;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False";
oleDbConnection1.Open();
ds = new DataSet();
daAttendees = new System.Data.OleDb.OleDbDataAdapter(
"SELECT * FROM Attendees", oleDbConnection1);
daRooms = new System.Data.OleDb.OleDbDataAdapter(
"SELECT * FROM Rooms", oleDbConnection1);
daEvents = new System.Data.OleDb.OleDbDataAdapter(
"SELECT * FROM Events", oleDbConnection1);
daAttendees.Fill(ds, "Attendees");
daRooms.Fill(ds, "Rooms");
daEvents.Fill(ds, "Events");
oleDbConnection1.Close();
Application["ds"] = ds;
}
/// <summary>
/// 取得 OLEDB 的连接字符串.
/// 优先启动 ACE 驱动，
/// 假如 ACE 失败，再尝试启动 JET
///
/// 该方法可能用不上。
/// 因为 在 Office 2010 上面，Jet 与 ACE 都能正常运作
///
/// 唯一需要注意的是， 如果目标机器的操作系统，是64位的话。
/// 项目需要 编译为 x86， 而不是简单的使用默认的 Any CPU.
/// </summary>
/// <param name="excelFileName"></param>
/// <returns></returns>
public static string GetOleDbConnectionString(string excelFileName)
{
// Office 2007 以及 以下版本使用.
string jetConnString =
String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0;", excelFileName);
// xlsx 扩展名 使用.
string aceConnXlsxString =
String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES\"", excelFileName);
// xls 扩展名 使用.
string aceConnXlsString =
String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=YES\"", excelFileName);
// 默认非 xlsx
string aceConnString = aceConnXlsString;
if (excelFileName.EndsWith(".xlsx", StringComparison.CurrentCultureIgnoreCase))
{
// 如果扩展名为 xlsx.
// 那么需要将 驱动切换为 xlsx 扩展名 的.
aceConnString = aceConnXlsxString;
}
// 尝试使用 ACE. 假如不发生错误的话，使用 ACE 驱动.
try
{
System.Data.OleDb.OleDbConnection cn = new System.Data.OleDb.OleDbConnection(aceConnString);
cn.Open();
cn.Close();
// 使用 ACE
return aceConnString;
}
catch (Exception)
{
// 启动 ACE 失败.
}
// 尝试使用 Jet. 假如不发生错误的话，使用 Jet 驱动.
try
{
System.Data.OleDb.OleDbConnection cn = new System.Data.OleDb.OleDbConnection(jetConnString);
cn.Open();
cn.Close();
// 使用 Jet
return jetConnString;
}
catch (Exception)
{
// 启动 Jet 失败.
}
// 假如 ACE 与 JET 都失败了，默认使用 JET.
return jetConnString;
}
public static bool AnalyzeExcel(ExcelXMLLayout layout)
{
System.Data.OleDb.OleDbConnection conn = null;
try
{
conn = new System.Data.OleDb.OleDbConnection(MakeConnectionString(layout.solution.path));
conn.Open();
System.Data.DataTable table = conn.GetOleDbSchemaTable(
System.Data.OleDb.OleDbSchemaGuid.Columns,
new object[] { null, null, layout.sheet + "$", null });
layout.Clear();
System.Diagnostics.Debug.WriteLine("Start Analyze [" + table.Rows.Count + "]");
foreach (System.Data.DataRow row in table.Rows)
{
string name = row["Column_Name"].ToString();
System.Diagnostics.Debug.WriteLine(name);
// 测试数据类型
ExcelXMLLayout.KeyType testType = ExcelXMLLayout.KeyType.Unknown;
{
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(
string.Format("select [{0}] from [{1}$]", name, layout.sheet), conn
);
System.Data.OleDb.OleDbDataReader r = cmd.ExecuteReader();
while (r.Read())
{
System.Diagnostics.Debug.WriteLine(r[0].GetType());
if (r[0].GetType() == typeof(System.Double))
{
testType = ExcelXMLLayout.KeyType.Integer;
break;
}
if (testType == ExcelXMLLayout.KeyType.String)
{
break;
}
testType = ExcelXMLLayout.KeyType.String;
}
r.Close();
cmd.Dispose();
}
layout.Add(name, testType);
}
table.Dispose();
conn.Close();
return true;
}
catch (Exception outErr)
{
lastError = string.Format("无法分析，Excel 无法打开\r\n{0}", outErr.Message);
}
return false;
}
static void Main(string[] args)
{
using (var cn = new System.Data.OleDb.OleDbConnection(new Connection().ConnectionString))
{
cn.Open();
// 手抜き：第一引数に以下の文字列を渡すことで処理分岐
switch (args.FirstOrDefault().ToLower())
{
case "select":
SelectAll(cn);
SelectWithoutClass(cn);
SelectWithClass(cn);
break;
case "insert":
Insert(cn);
SelectAll(cn);
break;
case "update":
Update(cn);
SelectAll(cn);
break;
case "commit":
RunTransaction(cn, hasError: false);
SelectAll(cn);
break;
case "rollback":
RunTransaction(cn, hasError: true);
SelectAll(cn);
break;
case "tableadapter":
GetIdentityWithTableAdapter();
break;
case "identity":
SelectAll(cn);
GetIdentityWithDapper(cn);
SelectAll(cn);
break;
default:
break;
}
cn.Close();
}
}
public System.Data.DataTable Read(string filename)
{
var excelDataAdapter = new System.Data.OleDb.OleDbDataAdapter();
_connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + filename + "; Extended Properties = Excel 8.0;";
var excelConn = new System.Data.OleDb.OleDbConnection(_connectionString);
excelConn.Open();
var dtProtocol = new System.Data.DataTable { TableName = "Protocol" };
var excelCommand = new System.Data.OleDb.OleDbCommand("SELECT * FROM [протокол плавки$]", excelConn);
excelDataAdapter.SelectCommand = excelCommand;
excelDataAdapter.Fill(dtProtocol);
excelConn.Close();
return dtProtocol;
}
public void ConvertExcelToCsv(int worksheetNumber = 1)
{
if (!System.IO.File.Exists(excelFilePath)) throw new FileNotFoundException(excelFilePath);
if (File.Exists(csvOutputFile)) throw new ArgumentException("File exists: " + csvOutputFile);
// connection string
var cnnStr = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;IMEX=1;HDR=NO\"", excelFilePath);
var cnn = new System.Data.OleDb.OleDbConnection(cnnStr);
// get schema, then data
var dt = new DataTable();
try
{
cnn.Open();
var schemaTable = cnn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
if (schemaTable.Rows.Count < worksheetNumber) throw new ArgumentException("The worksheet number provided cannot be found in the spreadsheet");
string worksheet = schemaTable.Rows[worksheetNumber - 1]["table_name"].ToString().Replace("'", "");
string sql = String.Format("select * from [{0}]", worksheet);
var da = new System.Data.OleDb.OleDbDataAdapter(sql, cnn);
da.Fill(dt);
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
throw e;
}
finally
{
// free resources
cnn.Close();
}
// write out CSV data
using (var wtr = new StreamWriter(csvOutputFile))
{
foreach (DataRow row in dt.Rows)
{
bool firstLine = true;
foreach (DataColumn col in dt.Columns)
{
if (!firstLine) { wtr.Write(","); } else { firstLine = false; }
var data = row[col.ColumnName].ToString().Replace("\"", "\"\"");
wtr.Write(String.Format("\"{0}\"", data));
}
wtr.WriteLine();
}
}
}
private void btnGo_Click(object sender, EventArgs e)
{
lblError.Text = "";
//open connection to database
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection();
conn.ConnectionString = txtConnString.Text;
conn.Open();
// MessageBox.Show(conn.State.ToString());
//create my command
System.Data.OleDb.OleDbCommand comm = new System.Data.OleDb.OleDbCommand();
comm.Connection = conn;
//send the sql through the command
comm.CommandText = txtSQL.Text;
//receive the result into a data container.
System.Data.OleDb.OleDbDataReader dr;
try
{
if (txtSQL.Text.ToUpper().StartsWith("SELECT"))
{
dr = comm.ExecuteReader();
System.Data.DataTable dt = new DataTable();
dt.Load(dr);
Grid1.AutoGenerateColumns = true;
//bind the result to the grid
Grid1.DataSource = dt;
}
else
{
MessageBox.Show(comm.ExecuteNonQuery().ToString());
}
}
catch (Exception ex)
{
lblError.Text = ex.Message;
}
finally
{
conn.Close();
}
}
static void Main(string[] args)
{
string connstring =
@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\DropBox\My Dropbox\Devry\CIS407\SU10B\day5\NorthWind.mdb;";
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection();
string sql = "";
System.Data.DataSet ds = new System.Data.DataSet();
System.Data.OleDb.OleDbDataReader dr;
System.Data.OleDb.OleDbCommand comm = new System.Data.OleDb.OleDbCommand();
try
{
//get this from connectionstrings.com/access
conn.ConnectionString = connstring;
conn.Open();
//here I can talk to my db...
comm.Connection = conn;
//Console.WriteLine(conn.State);
sql = Console.ReadLine();
comm.CommandText = sql;
if (sql.ToLower().IndexOf("select") == 0)
{
dr = comm.ExecuteReader();
while (dr.Read())
{
Console.WriteLine(dr.GetString(0));
}
}
else
{
Console.WriteLine(comm.ExecuteNonQuery().ToString());
}
}
catch ( Exception ex)
{
Console.WriteLine(ex.Message);
Console.Read();
}
finally
{
Console.ReadLine();
comm.Dispose();
conn.Close();
conn = null;
}
}
protected void btnGo_Click(object sender, EventArgs e)
{
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection();
string sql = "";
System.Data.DataSet ds = new System.Data.DataSet();
System.Data.OleDb.OleDbDataReader dr;
System.Data.OleDb.OleDbCommand comm = new System.Data.OleDb.OleDbCommand();
try
{
//get this from connectionstrings.com/access
conn.ConnectionString = txtConnString.Text;
conn.Open();
//here I can talk to my db...
comm.Connection = conn;
//Console.WriteLine(conn.State);
sql = txtSQL.Text;
comm.CommandText = sql;
if (sql.ToLower().IndexOf("select") == 0)
{
dr = comm.ExecuteReader();
while (dr.Read())
{
txtresults.Text = dr.GetValue(0).ToString();
}
}
else
{
txtresults.Text = comm.ExecuteNonQuery().ToString();
}
}
catch (Exception ex)
{
txtresults.Text = ex.Message;
}
finally
{
comm.Dispose();
conn.Close();
conn = null;
}
}
public void Querry(string Que, string DataBasePath)
{
string conn = "provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + DataBasePath;
System.Data.OleDb.OleDbConnection connect = new System.Data.OleDb.OleDbConnection(conn);
connect.Open();
using (System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand(Que, connect))
{
try
{
command.ExecuteNonQuery();
}
catch (System.Data.OleDb.OleDbException ex)
{
MessageBox.Show("Произошла ошибка при создании таблицы\n" + ex.Message);
}
}
connect.Close();
}
private void button1_Click(object sender, EventArgs e)
{
try
{
System.Data.OleDb.OleDbConnection MyConnection ;
System.Data.DataSet DtSet ;
System.Data.OleDb.OleDbDataAdapter MyCommand ;
MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\\csharp.net-informations.xls';Extended Properties=Excel 8.0;");
MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection);
MyCommand.TableMappings.Add("Table", "TestTable");
DtSet = new System.Data.DataSet();
MyCommand.Fill(DtSet);
dataGridView1.DataSource = DtSet.Tables[0];
MyConnection.Close();
}
catch (Exception ex)
{
MessageBox.Show (ex.ToString());
}
}
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 button1_Click(object sender, EventArgs e)
{
if (System.IO.File.Exists("your_base.mdb"))
{
comboBox1.Items.Clear();
string conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=your_base.mdb;Jet OLEDB:Engine Type=5";
System.Data.OleDb.OleDbConnection connectDb = new System.Data.OleDb.OleDbConnection(conStr);
connectDb.Open();
DataTable cbTb = connectDb.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
foreach (DataRow row in cbTb.Rows)
{
string tbName = row["TABLE_NAME"].ToString();
comboBox1.Items.Add(tbName);
}
connectDb.Close();
}
else
{
MessageBox.Show("База данных еще не создана. Воспользуйтесь кнопкой 'загрузить данные' для создания БД.", "Внимание!", MessageBoxButtons.OK,
MessageBoxIcon.Warning);
}
}
public bool SaveAs(string fileName, System.Data.DataTable dataTable)
{
if (dataTable == null || dataTable.Rows.Count == 0) return false;
if (xlApp == null) return false;
_workbook = xlApp.Workbooks.Add(Type.Missing);
_worksheet = (Worksheet)_workbook.Worksheets[1];
for (var i = 0; i < dataTable.Columns.Count; i++)
{
_worksheet.Cells[1, i + 1] = dataTable.Columns[i].ColumnName;
//range = (Range)worksheet.Cells[1, i + 1];
//range.Interior.ColorIndex = 15;
//range.Font.Bold = true;
}
_workbook.SaveAs(fileName,XlFileFormat.xlWorkbookNormal, "", "", false, false, XlSaveAsAccessMode.xlExclusive,XlPlatform.xlWindows, false, false, false, false);
_workbook.Close(true, fileName, false);
xlApp.Quit();
_connectionString = "provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + fileName + "';Extended Properties=Excel 8.0;"; // строка подключения
using (var excelConn = new System.Data.OleDb.OleDbConnection(_connectionString)) // используем OleDb
{
var queryValues = String.Empty;
excelConn.Open();
for (var i = 0; i < dataTable.Rows.Count; i++)
{
for (var c = 0; c < dataTable.Columns.Count; c++)
{
queryValues += dataTable.Rows[i][c] + "','";
}
queryValues = queryValues.Substring(0, queryValues.Length - 3);
var writeCmd = new System.Data.OleDb.OleDbCommand("INSERT INTO [Лист1$] VALUES ('" + queryValues + "')", excelConn);
writeCmd.ExecuteNonQuery(); // вставляем данные в лист1 файла - filename
writeCmd.Dispose();
queryValues = String.Empty;
}
excelConn.Close();
}
return System.IO.File.Exists(fileName);
}
private Licenza SonoLicenziato_pri(string Utente, string Codice)
{
var l = Licenza.Fallito;
var p = System.IO.Path.Combine(Server.MapPath("public"), "/files/files.mdb");
using (var c = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + p))
{
try
{
c.Open();
var s = "SELECT top 1 * FROM Licenze where Chiave = @Chiave";
var dl = new DatiLicenza(false);
using (var cm = new System.Data.OleDb.OleDbCommand(s, c))
{
var p1 = new System.Data.OleDb.OleDbParameter("Chiave", Codice);
cm.Parameters.Add(p1);
using (var dr = cm.ExecuteReader())
{
while (dr.HasRows && dr.Read())
{
dl.Chiave = ObjectToString(dr["Chiave"]);
dl.Stato = ObjectToString(dr["Stato"]);
dl.Utente = ObjectToString(dr["Utente"]);
dl.Ok = true;
}
dr.Close();
}
}
if (dl.Ok)
{
if (dl.Utente.Equals(Utente, StringComparison.OrdinalIgnoreCase))
{
l = Licenza.GiaRegistrato;
if (dl.Stato == "OK")
{
l = Licenza.RegistratoOK;
}
}
else
{
if (dl.Stato == "OK")
{
l = Licenza.GiaRegistrato;
}
else
{
var i = 0;
var z = "update Licenze set Utente = @Utente, Stato = @Stato where Chiave = @Chiave";
var p3 = new System.Data.OleDb.OleDbParameter[] {
new System.Data.OleDb.OleDbParameter("Utente", Utente),
new System.Data.OleDb.OleDbParameter("Stato", "OK"),
new System.Data.OleDb.OleDbParameter("Chiave", Codice)
};
using (var cm2 = new System.Data.OleDb.OleDbCommand(z, c))
{
cm2.Parameters.AddRange(p3);
try
{
i = cm2.ExecuteNonQuery();
}
catch
{
i = 0;
}
}
if (i > 0)
{
l = Licenza.RegistratoOK;
}
}
}
}
else
{
l = Licenza.CodiceInesistente;
}
}
catch
{
//error
}
if (c.State == System.Data.ConnectionState.Open)
{
c.Close();
}
}
return(l);
}
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)
{
MyLogger.Instance.Write(" InitConnectionString(item) == false");
// Easynet.Edge.UI.WebPages.Classes.Convertors.MyLogger.Instance.Write(@"D:\log.txt", "InitConnectionString(item) == false");
return(null);
}
if (OpenConnection() == false)
{
MyLogger.Instance.Write("OpenConnection() == false");
// Easynet.Edge.UI.WebPages.Classes.Convertors.MyLogger.Instance.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]);
MyLogger.Instance.Write(" dataSet1.Tables[0]: rows count: " + dataSet1.Tables[0].Rows.Count);
_connection.Close();
}
}
_connection.Close();
// Easynet.Edge.UI.WebPages.Classes.Convertors.MyLogger.Instance.Write(@"D:\log.txt", "_connection.Close();");
return(listTables);
}
catch (Exception ex)
{
MyLogger.Instance.Write("error ReadXLSFile: " + ex.Message);
// Easynet.Edge.UI.WebPages.Classes.Convertors.MyLogger.Instance.Write(@"D:\log.txt", "error ReadXLSFile: " + ex.Message);
//WriteToEventLog("Converter: \n" + ex.ToString());
_connection.Close();
_connection.Dispose();
dt.Dispose();
dataSet1.Dispose();
return(null);
}
}
///-------------------------------------------------
#region
//private void set_headers_rec_order()
//{
// int count_headers = 0; string condition = "";
// Lb_Headers.Items.Add("RecDateTime"); count_headers++;
// headers = new string[G_Variables.No_GWs_Availble];
// for (int i = 0; i < G_Variables.No_GWs_Availble; i++)
// {
// condition = "";
// if (Rb_All_Types.Checked)
// condition = "( Select title, id, root_id, pin_id from device where gateway_id=" + G_Variables.Gateways_ID[i] + " )";
// else //if ((Rb_DeviceType.Checked) && (!Ch_additionalSensors.Checked))
// condition = "( Select distinct title, id , root_id , pin_id ,type from device where gateway_id=" + G_Variables.Gateways_ID[i] + " and type='" + com_monitor_type.Text + "' )";
// if ((Rb_DeviceType.Checked) && (Ch_additionalSensors.Checked))
// {
// condition += " union (SELECT DISTINCT device_1.title, device_1.id , device_1.root_id, device_1.pin_id ,device_1.type " +
// " FROM dbo.device INNER JOIN " +
// " dbo.Dependencies ON dbo.device.id = dbo.Dependencies.device INNER JOIN " +
// " dbo.device AS device_1 ON dbo.Dependencies.dependon_device = device_1.id " +
// " WHERE (device_1.gateway_id =" + G_Variables.Gateways_ID[i] + ") AND (dbo.device.type = '" + com_monitor_type.Text + "'))";
// }
// condition += " order by type,pin_id ";
// con2db.Close();
// con2db.Open();
// SqlCommand cmd = new SqlCommand(condition, con2db);
// SqlDataAdapter adaptor = new SqlDataAdapter(cmd);
// DataSet DTt = new DataSet();
// adaptor.Fill(DTt);
// DataRowCollection drgoods = DTt.Tables[0].Rows;
// foreach (DataRow dtdep in drgoods)
// {
// string s = dtdep[0].ToString() + "(" + dtdep[1].ToString() + "/" + dtdep[2].ToString() + ":" + dtdep[3].ToString() + "/" + G_Variables.Gateways_ID[i] + ")";
// Lb_Headers.Items.Add(s); headers[i] += (s + ",");
// count_headers++;
// }
// }
// con2db.Close();
// if (Lb_Headers.Items.Count == 1)
// {
// Lb_Headers.Items.Clear(); count_headers = 0;
// MessageBox.Show("Ensure that Network was discovered and There were Devices, that already have been connected on them !", "Info", MessageBoxButtons.OK, MessageBoxIcon.Information);
// }
// lab_headers.Text = "=" + count_headers;
//}
///------------------------------------------------
//private void set_headers()
//{
// // bool add_sensor=false;
// int count_headers = 0;
// string condition = " Select id, title, root_id,gateway_id,pin_id from device ";
// if ((Rb_Whole_Network.Checked) && (Rb_All_Types.Checked))
// { condition += ""; rec_state = "Network"; }
// else if ((Rb_Whole_Network.Checked) && (Rb_DeviceType.Checked))
// { condition += " where type='" + com_monitor_type.Text + "'"; rec_state = "Network"; }
// else if ((Rb_Gateway.Checked) && (Rb_All_Types.Checked))
// { condition += " where gateway_id= " + com_gateway.SelectedValue + ""; rec_state = com_gateway.SelectedValue.ToString(); }
// else
// {
// condition += " where gateway_id= " + com_gateway.SelectedValue + " and type='" + com_monitor_type.Text + "'"; rec_state = com_gateway.SelectedValue.ToString();
// // if((Ch_additionalSensors.Checked) && (!Rb_All_Types.Checked))
// // add_sensor = true;
// }
// condition += " order by type, id ,gateway_id";
// con2db.Close();
// con2db.Open();
// SqlCommand cmd = new SqlCommand(condition, con2db);
// SqlDataAdapter adaptor = new SqlDataAdapter(cmd);
// DataSet DTt = new DataSet();
// adaptor.Fill(DTt);
// DataRowCollection drgoods = DTt.Tables[0].Rows;
// count_headers = DTt.Tables[0].Rows.Count;// +1; //for time we add 1
// //lab_headers.Text = "=" + count_headers;
// Lb_Headers.Items.Add("RecDateTime");
// foreach (DataRow dtdep in drgoods)
// {
// string s = dtdep[1].ToString() + "(" + dtdep[0].ToString() + "/" + dtdep[2].ToString() + ":" + dtdep[4].ToString() + "/" + dtdep[3].ToString() + ")";
// Lb_Headers.Items.Add(s);
// }
// if ((Rb_Whole_Network.Checked)&&(Rb_DeviceType.Checked) && (Ch_additionalSensors.Checked))
// {
// condition = " SELECT DISTINCT device_1.id, device_1.title, device_1.root_id, device_1.gateway_id, dbo.device.type" +
// " FROM dbo.device INNER JOIN" +
// " dbo.Dependencies ON dbo.device.id = dbo.Dependencies.device INNER JOIN" +
// " dbo.device AS device_1 ON dbo.Dependencies.dependon_device = device_1.id" +
// " WHERE (dbo.device.type = '" + com_monitor_type.Text + "')";
// }
// else if ((Rb_Gateway.Checked) && (Rb_DeviceType.Checked) && (Ch_additionalSensors.Checked))
// {
// condition = " SELECT DISTINCT device_1.id, device_1.title, device_1.root_id, device_1.gateway_id, dbo.device.type" +
// " FROM dbo.device INNER JOIN" +
// " dbo.Dependencies ON dbo.device.id = dbo.Dependencies.device INNER JOIN" +
// " dbo.device AS device_1 ON dbo.Dependencies.dependon_device = device_1.id" +
// " WHERE (dbo.device.gateway_id = "+int.Parse(com_gateway.SelectedValue.ToString())+") AND (dbo.device.type = '"+com_monitor_type.Text+"')";
// }
// if ((Rb_DeviceType.Checked) && (Ch_additionalSensors.Checked))
// {
// cmd = new SqlCommand(condition, con2db);
// adaptor = new SqlDataAdapter(cmd);
// DTt = new DataSet();
// adaptor.Fill(DTt);
// drgoods = DTt.Tables[0].Rows;
// count_headers+=DTt.Tables[0].Rows.Count;
// // lab_headers.Text = "=" + count_headers;
// foreach (DataRow dtdep in drgoods)
// {
// string s = dtdep[1].ToString() + "(" + dtdep[0].ToString() + "/" + dtdep[2].ToString() + ":" + dtdep[3].ToString() + ")";
// Lb_Headers.Items.Add(s);
// }
// }
// con2db.Close();
// if (Lb_Headers.Items.Count == 1)
// Lb_Headers.Items.Clear();
// else
// lab_headers.Text = "=" + (++count_headers);
//}
#endregion
///-------------------------------------------------
private void create_datasetfile()
{
System.Data.OleDb.OleDbConnection MyConnection;
string s="";
string t = "_"+DateTime.Now.Year+"-"+DateTime.Now.Month+"-"+DateTime.Now.Day+"-h" + DateTime.Now.ToLocalTime().Hour.ToString();
txt_FileName.Text = G_Variables.Log_Title + t;
s = G_Variables.Log_Path + "\\" + G_Variables.Log_Title + t;
bool continue_work = true; bool append = false;
if(File.Exists(s + ".XLS") == true)
{
DialogResult dr = MessageBox.Show(" File was Exist at that path with the same title, because another file was created within the same hour with the same criteria \n Do you want to continue process replacing existing file with that new file ?", "Warning", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
if (dr == DialogResult.Yes)
{ File.Delete(s + ".XLS"); continue_work = true; }
else
{
dr = MessageBox.Show(" Do You want to continue using the same file to append data on it?", "info.", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (dr == DialogResult.No)
{
dr = MessageBox.Show(" Do You want to continue using an alternative title which will be (prev. titel plus minutes)?", "info.", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
if (dr == DialogResult.OK)
{
s += "-m" + DateTime.Now.ToLocalTime().Minute.ToString();
txt_FileName.Text = G_Variables.Log_Title + t + "-m" + DateTime.Now.ToLocalTime().Minute.ToString(); ;
continue_work = true;
}
else
continue_work = false;
}
else append = true;
}
}
if(continue_work)
{
try
{
columns = Lb_Headers.Items.Count;
if (Lb_Rules.Items[1].ToString().Contains('#'))
Cur_Type = "Network";
else
Cur_Type = "Gateway";
Cur_Ds_File = s;
Cur_File_Sheet = G_Variables.Log_Title;
string header = "";
MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + Cur_Ds_File + "';Extended Properties='Excel 8.0;HDR=Yes'");
MyConnection.Open();
if( Lb_Headers.Items.Count>0)
header += "[" + Lb_Headers.Items[0] + "] datetime,";
for (int i = 1; i < Lb_Headers.Items.Count - 1; i++)
header += "[" + Lb_Headers.Items[i] + "] int,";
header += "[" + Lb_Headers.Items[Lb_Headers.Items.Count - 1] + "] int";
if (!append)
{
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand("create table [" + Cur_File_Sheet + "] (" + header + " ) ", MyConnection);
cmd.ExecuteNonQuery();
}
MyConnection.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
}
private bool SaveCheck()
{
bool bFlag = true;
if (lblUploadFile.Text.Length == 0)
{
bFlag = false;
Dialog.OpenDialogInAjax(txtOrganName, "请选择Excel工资文件！");
}
else if (GetOpCodeFieldName().Length == 0)
{
bFlag = false;
Dialog.OpenDialogInAjax(txtOrganName, "请选择工号字段！");
}
else if (GetOpNameFieldName().Length == 0)
{
bFlag = false;
Dialog.OpenDialogInAjax(txtOrganName, "请选择姓名字段！");
}
else
{
string _OpCodeFieldName = GetOpCodeFieldName();
string _OpNameFieldName = GetOpNameFieldName();
string strConn, sheetname = "Sheet1$";
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + lblUploadFile.Text + ";Extended Properties=Excel 8.0;";
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn);
conn.Open();
DataTable dtExcelSchema = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
if (dtExcelSchema.Rows.Count > 0)
{
sheetname = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
}
System.Data.OleDb.OleDbDataAdapter oada = new System.Data.OleDb.OleDbDataAdapter("select * from [" + sheetname + "]", strConn);
DataSet ds = new DataSet();
oada.Fill(ds);
oada = new System.Data.OleDb.OleDbDataAdapter("select * from [" + sheetname + "] Where 1<>1", strConn);
DataSet ds1 = new DataSet();
oada.Fill(ds1);
conn.Close();
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
string _SQL = "Select Top 1 1 From SysUser_Info Where OpCode='" + ds.Tables[0].Rows[i][_OpCodeFieldName].ToString() + "' And OpName<>'" + ds.Tables[0].Rows[i][_OpNameFieldName].ToString() + "'";
if (SysClass.SysGlobal.GetExecSqlIsExist(_SQL))
{
DataRow row = ds1.Tables[0].NewRow();
for (int k = 0; k < ds1.Tables[0].Columns.Count; k++)
{
row[ds1.Tables[0].Columns[k].ColumnName] = ds.Tables[0].Rows[i][ds1.Tables[0].Columns[k].ColumnName].ToString();
}
ds1.Tables[0].Rows.Add(row);
}
}
gvAbnormal.DataSource = ds1.Tables[0].DefaultView;
gvAbnormal.DataBind();
gvAbnormal.Visible = gvAbnormal.Rows.Count > 0;
if (gvAbnormal.Rows.Count > 0)
{
bFlag = false;
Dialog.OpenDialogInAjax(txtOrganName, "下列工号和姓名与系统内有异，请检查！");
}
}
return(bFlag);
}
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);
}
//driver download from:https://www.microsoft.com/en-us/download/details.aspx?id=13255
public static void Save(this System.Data.DataSet dataSet, string fileName = null, string tableName = null, IWin32Window owner = null)
{
if (string.IsNullOrEmpty(fileName))
{
fileName = UI.FileIO.GetSaveFileName(owner, fileName ?? ".xls", "Comma Separated Values Files (*.csv)|*.csv|Microsoft Excel Files (*.xls)|*.xls|Microsoft Access Files (*.mdb)|*.mdb|All files (*.*)|*.*");
}
if (fileName == null)
{
return;
}
string providerName = IntPtr.Size == 8 ? "Microsoft.ACE.OLEDB.12.0" : "Microsoft.Jet.OLEDB.4.0";
string providerProds = null;
string tableNameFormat = "{0}";
string fileExtention = System.IO.Path.GetExtension(fileName).ToLower();
var dataSource = fileName;
switch (fileExtention)
{
case ".csv":
tableNameFormat = System.IO.Path.GetFileNameWithoutExtension(fileName) + "_{0}.csv";
dataSource = System.IO.Path.GetDirectoryName(fileName);
providerProds = "text;HDR=YES;FMT=Delimited";
break;
case ".xls": providerProds = "Excel 8.0;HDR=YES"; break;
case ".mdb": providerProds = "Access 8.0;HDR=YES"; break;
default: throw new Exception("unknown file extention");
}
using (var con = new System.Data.OleDb.OleDbConnection())
{
con.ConnectionString = "Provider=" + providerName + ";Data Source=" + dataSource + ";Extended Properties='" + providerProds + "'";
con.Open();
foreach (System.Data.DataTable t in dataSet.Tables)
{
if (tableName != null && tableName != t.TableName)
{
continue;
}
var exportName = string.Format(tableNameFormat, t.TableName);
using (var command = new System.Data.OleDb.OleDbCommand(null, con))
{
string sqlCreate = string.Format("Create Table {0} (", exportName);
string sqlInsert = string.Format("Insert Into {0} (", exportName);
string sqlInsertValues = "Values(";
foreach (System.Data.DataColumn c in t.Columns)
{
var p = command.CreateParameter();
p.SourceColumn = c.ColumnName;
command.Parameters.Add(p);
string oleDbType = "Note";
if (c.DataType == typeof(int) | c.DataType == typeof(short) | c.DataType == typeof(byte))
{
oleDbType = "LONG";
}
sqlCreate += string.Format("[{0}] {1}, ", c.ColumnName, oleDbType);
sqlInsert += string.Format("[{0}], ", c.ColumnName);
sqlInsertValues += "?, ";
}
command.CommandText = sqlCreate.TrimEnd(' ', ',') + ")";
command.ExecuteNonQuery();
command.CommandText = sqlInsert.TrimEnd(' ', ',') + ")" + sqlInsertValues.TrimEnd(' ', ',') + ")";
using (var adp = new System.Data.OleDb.OleDbDataAdapter(null, con))
{
if (t.TableName != exportName)
{
adp.TableMappings.Add(t.TableName, exportName);
}
adp.InsertCommand = command;
adp.Update(t);
adp.InsertCommand = null;
}
}
}
con.Close();
}
}
public static String ExcelToXML(String e_strExcelFileName)
{
String l_strXMLFileName = System.IO.Path.ChangeExtension(e_strExcelFileName,"xml");
String l_sss;
try
{
System.Data.OleDb.OleDbConnection MyConnection;
System.Data.DataSet ds;
System.Data.OleDb.OleDbDataAdapter MyCommand;
String l_strExtension = System.IO.Path.GetExtension(e_strExcelFileName);
String l_strCombineFileName = "";
if (l_strExtension == ".xls" || l_strExtension == ".XLS")
{
l_strCombineFileName = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + e_strExcelFileName + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
}
else if (l_strExtension == ".xlsx" || l_strExtension == ".XLSX")
{
l_strCombineFileName = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + e_strExcelFileName + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
}
//String l_strCombineFileName = "provider=Microsoft.Jet.OLEDB.4.0;Data Source='Specify path here ";
//l_strCombineFileName += e_strExcelFileName;
//l_strCombineFileName += "';Extended Properties=Excel 8.0;";
//"provider=Microsoft.Jet.OLEDB.4.0;Data Source='Specify path here \\xl2xml.xls';Extended Properties=Excel 8.0;"
MyConnection = new System.Data.OleDb.OleDbConnection(l_strCombineFileName);
//set sheet name here default is sheet1
try
{
//MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection);
//lazy for this,because ExportDataSetToExcel has 3 unuse sheet
MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet4$]", MyConnection);
//MyCommand.TableMappings.Add("Table", "Product");
ds = new System.Data.DataSet("RouteData");
MyCommand.Fill(ds);
//from element to attribute.
foreach (DataTable dt in ds.Tables)
{
foreach (DataColumn dc in dt.Columns)
{
dc.ColumnMapping = MappingType.Attribute;
}
}
//String l_sss = ds.GetXmlSchema();
l_sss = ds.GetXml();
MyConnection.Close();
ds.WriteXml(l_strXMLFileName);
}
catch (System.Exception e)
{
l_sss = e.ToString();
}
return l_sss;
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show(ex.ToString());
}
return "failed";
}
private static void ValidaProductoVentas()
{
// Gets the Calendar instance associated with a CultureInfo.
CultureInfo myCI = new CultureInfo("en-US");
Calendar myCal = myCI.Calendar;
// Gets the DTFI properties required by GetWeekOfYear.
CalendarWeekRule myCWR = myCI.DateTimeFormat.CalendarWeekRule;
DayOfWeek myFirstDOW = myCI.DateTimeFormat.FirstDayOfWeek;
//string sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + System.IO.Path.GetDirectoryName(path) + ";Extended Properties=dBASE IV;";
string sConn = "Provider = vfpoledb.1;Data Source=" + System.IO.Path.GetDirectoryName(path) + ";Collating Sequence=general";
DataTable productos = new DataTable();
productos = ListaProductoVentas();
using (System.Data.OleDb.OleDbConnection dbConn = new System.Data.OleDb.OleDbConnection(sConn))
{
try
{
dbConn.Open();
}
catch (Exception ex)
{
ActualizaLogVentas("", "Error en encontrar DBF en " + path, ex.Message, proceso_log);
throw(ex);
}
foreach (DataRow row in productos.Rows)
{
int weekofyear = myCal.GetWeekOfYear(Convert.ToDateTime(row["fecha"]), myCWR, myFirstDOW);
try
{
string stock_pri = "SELECT * FROM SCACSAL WHERE CSAL_EMPRE ='" + empresa + "' And CSAL_ANO = '" + Convert.ToDateTime(row["fecha"]).ToString("yyyy") + "' And CSAL_SEMAN = '" + weekofyear.ToString().PadLeft(2).Replace(" ", "0") + "' And CSAL_ALMAC = '" + almacen + "' And CSAL_ARTIC='" + row["producto"].ToString() + "' And CSAL_CALID = '" + calidad + "'";
System.Data.OleDb.OleDbCommand stk1 = new System.Data.OleDb.OleDbCommand(stock_pri, dbConn);
System.Data.OleDb.OleDbDataAdapter cn_stk1 = new System.Data.OleDb.OleDbDataAdapter(stk1);
DataTable stock1 = new DataTable();
cn_stk1.Fill(stock1);
string stock_seg = "SELECT * FROM SCACSALP WHERE CSAL_EMPRE ='" + empresa + "' And CSAL_ANO = '" + Convert.ToDateTime(row["fecha"]).ToString("yyyy") + "' And CSAL_SEMAN = '" + weekofyear.ToString().PadLeft(2).Replace(" ", "0") + "' And CSAL_ALMAC = '" + almacen + "' And CSAL_ARTIC='" + row["producto"].ToString() + "' And CSAL_CALID = '" + calidad + "'";
System.Data.OleDb.OleDbCommand stk2 = new System.Data.OleDb.OleDbCommand(stock_seg, dbConn);
System.Data.OleDb.OleDbDataAdapter cn_stk2 = new System.Data.OleDb.OleDbDataAdapter(stk2);
DataTable stock2 = new DataTable();
cn_stk2.Fill(stock2);
if (stock1.Rows.Count == 0 || stock2.Rows.Count == 0)
{
ActualizaVentas(row["id"].ToString(), "E", row["tipo"].ToString());
ActualizaLogVentas(row["id"].ToString(), row["tipo"].ToString() + " - Producto " + row["producto"].ToString() + " no tiene registro semanal.", "Tablas SCACSAL y SCACSALP", proceso_log);
}
}
catch (Exception ex)
{
ActualizaLogVentas("", "Error en Validación de Productos.", ex.Message, proceso_log);
}
}
try
{
//Se termina transacción y cerramos conexión
dbConn.Close();
}
catch (Exception ex)
{
ActualizaLogVentas("", "Error en Cierre de Conexión a DBF.", ex.Message, proceso_log);
}
}
}
private void button1_Click(object sender, EventArgs e)
{
if (comboBox1.Text.ToString() == "")
{
MessageBox.Show("Выбери год", "Внимание!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else if (comboBox2.Text.ToString() == "")
{
MessageBox.Show("Выбери месяц", "Внимание!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else if (textBox1.Text == "")
{
MessageBox.Show("Выбери файл", "Внимание!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
string strConn;
//Check for Excel version
if (textBox1.Text.Substring(textBox1.Text.LastIndexOf('.')).ToLower() == ".xlsx")
{
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + textBox1.Text + ";Extended Properties=\"Excel 12.0;HDR=YES; IMEX=0\"";
}
else
{
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + textBox1.Text + ";Extended Properties=\"Excel 8.0;HDR=YES; IMEX=0\"";
}
System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(strConn);
con.Open();
DataSet ds = new DataSet();
DataTable shemaTable = con.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables,
new object[] { null, null, null, "TABLE" });
string sheet1 = (string)shemaTable.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);
if (System.IO.File.Exists("your_base.mdb"))
{
int year = Convert.ToInt32(comboBox1.Text.ToString());
int month = Convert.ToInt32(comboBox2.Text.ToString());
string conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=your_base.mdb;Jet OLEDB:Engine Type=5";
System.Data.OleDb.OleDbConnection connectDb = new System.Data.OleDb.OleDbConnection(conStr);
connectDb.Open();
System.Data.OleDb.OleDbCommand myCMD = new System.Data.OleDb.OleDbCommand();
myCMD.Connection = connectDb;
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
myCMD.CommandText = "Insert into SURVEY (Anim, weight, height, s_year, s_month) VALUES (\"" +
ds.Tables[0].Rows[i][0] + "\", " + ds.Tables[0].Rows[i][1] + ", " + ds.Tables[0].Rows[i][2] + ", " + year + ", " + month + ")";
myCMD.ExecuteNonQuery();
}
MessageBox.Show("Данные загружены в БД", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
int year = Convert.ToInt32(comboBox1.Text.ToString());
int month = Convert.ToInt32(comboBox2.Text.ToString());
ADOX.Catalog cat = new ADOX.Catalog();
string connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Jet OLEDB:Engine Type=5";
cat.Create(String.Format(connstr, "your_base.mdb"));
cat = null;
string conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=your_base.mdb;Jet OLEDB:Engine Type=5";
Querry("CREATE TABLE SURVEY(Anim varchar(255), weight int, height int, s_year int, s_month int);", "your_base.mdb");
System.Data.OleDb.OleDbConnection connectDb = new System.Data.OleDb.OleDbConnection(conStr);
connectDb.Open();
System.Data.OleDb.OleDbCommand myCMD = new System.Data.OleDb.OleDbCommand();
myCMD.Connection = connectDb;
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
myCMD.CommandText = "Insert into SURVEY (Anim, weight, height, s_year, s_month) VALUES (\"" +
ds.Tables[0].Rows[i][0] + "\", " + ds.Tables[0].Rows[i][1] + ", " + ds.Tables[0].Rows[i][2] + ", " + year + ", " + month + ")";
myCMD.ExecuteNonQuery();
}
//string comm = "Insert into SURVEY (Anim, weight, height) VALUES (hare, 10, 20)";
//System.Data.OleDb.OleDbDataAdapter dbAdp = new System.Data.OleDb.OleDbDataAdapter(comm, conStr);
//dbAdp.Update(ds.Tables[0]);
MessageBox.Show("Данные загружены в БД", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
con.Close();
}
}
/// <summary>
/// 将当前连接的 Excel 文件中指定的 sheets 导出到 <see cref="DataSet"/> 中。
/// </summary>
/// <param name="sheetNames">需要导出的 Sheet 名字集。默认 Sheet1 </param>
/// <exception cref="System.Data.OleDb.OleDbException">一般性数据库错误，或者指定的 Sheet 名字不存在对应的数据。</exception>
/// <returns></returns>
public DataSet ToDataSet(params string[] sheetNames)
{
if (sheetNames == null || sheetNames.Length == 0)
sheetNames = new string[] { "sheet1" };
string ConnStr = ConnectionString;
//Conn
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(ConnStr);
//new DataSet
DataSet myDataSet = new DataSet();
//Command
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand();
cmd.Connection = conn;
try
{
foreach (var p in sheetNames)
{
cmd.CommandText = string.Format(selectSqlString, p);
DataTable dt = new DataTable(p);
conn.Open();
dt.Load(cmd.ExecuteReader(CommandBehavior.CloseConnection));
myDataSet.Tables.Add(dt);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
cmd.Dispose();
}
return myDataSet;
}
/// <summary>
/// 取得 OLEDB 的连接字符串.
/// 优先启动 ACE 驱动，
/// 假如 ACE 失败，再尝试启动 JET
///
/// 该方法可能用不上。
/// 因为 在 Office 2010 上面，Jet 与 ACE 都能正常运作
///
/// 唯一需要注意的是， 如果目标机器的操作系统，是64位的话。
/// 项目需要 编译为 x86， 而不是简单的使用默认的 Any CPU.
/// </summary>
/// <param name="excelFileName"> Excel 文件.</param>
/// <param name="hasTitle">Excel 文件是否有标题行. </param>
/// <param name="isReadonly">是否是只读操作.</param>
/// <returns></returns>
public static string GetOleDbConnectionString(string excelFileName, bool hasTitle, bool isReadonly = false)
{
string optionString = null;
// HDR表示要把第一行作为数据还是作为列名，作为数据用 HDR=no，作为列名用HDR=yes；
if (hasTitle)
{
optionString = "HDR=yes;";
}
else
{
optionString = "HDR=no;";
}
// 当 IMEX = 0 时为“汇出模式”，这个模式开启的 Excel 档案只能用来做“写入”用途。
// 当 IMEX = 1 时为“汇入模式”，这个模式开启的 Excel 档案只能用来做“读取”用途。
// 当 IMEX = 2 时为“连結模式”，这个模式开启的 Excel 档案可同时支援“读取”与“写入”用途。
if (isReadonly)
{
optionString = String.Format("{0};IMEX=1", optionString);
}
// Office 2007 以及 以下版本使用.
string jetConnString =
String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;" + optionString + "\"", excelFileName);
// xlsx 扩展名 使用.
string aceConnXlsxString =
String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;" + optionString + "\"", excelFileName);
// xls 扩展名 使用.
string aceConnXlsString =
String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 8.0;" + optionString + "\"", excelFileName);
// 默认非 xlsx
string aceConnString = aceConnXlsString;
if (excelFileName.EndsWith(".xlsx", StringComparison.CurrentCultureIgnoreCase))
{
// 如果扩展名为 xlsx.
// 那么需要将 驱动切换为 xlsx 扩展名 的.
aceConnString = aceConnXlsxString;
}
// 尝试使用 ACE. 假如不发生错误的话，使用 ACE 驱动.
try
{
System.Data.OleDb.OleDbConnection cn = new System.Data.OleDb.OleDbConnection(aceConnString);
cn.Open();
cn.Close();
// 使用 ACE
return(aceConnString);
}
catch (Exception e)
{
// 启动 ACE 失败.
//Console.WriteLine(e.Message);
}
// 尝试使用 Jet. 假如不发生错误的话，使用 Jet 驱动.
try
{
System.Data.OleDb.OleDbConnection cn = new System.Data.OleDb.OleDbConnection(jetConnString);
cn.Open();
cn.Close();
// 使用 Jet
return(jetConnString);
}
catch (Exception e)
{
// 启动 Jet 失败.
//Console.WriteLine(e.Message);
}
// 假如 ACE 与 JET 都失败了，默认使用 JET.
return(jetConnString);
}
} // End Function GetColumnNamesForTable
public override bool TableExists(string strTableName)
{
strTableName = strTableName.Replace("'", "''");
bool bReturnValue = false;
using (System.Data.OleDb.OleDbConnection dbcon = new System.Data.OleDb.OleDbConnection(this.m_ConnectionString.ConnectionString))
{
lock (dbcon)
{
try
{
if (dbcon.State != System.Data.ConnectionState.Open)
{
dbcon.Open();
}
//string[] tblrestrictions = new string[] {null, null, null, "TABLE"};
//System.Data.DataTable dt1 = dbcon.GetSchema("tables", tblrestrictions);
// Note:
// Restrictions are a string array in the following format:
// {TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE}.
// http://forums.asp.net/t/976267.aspx/1?Record+s+cannot+be+read+no+read+permission+on+MSysObjects+
using (System.Data.DataTable dt = dbcon.GetSchema("tables"))
{
int i = dt.Select("TABLE_NAME like '" + strTableName + "'").Length;
if (i > 0)
{
bReturnValue = true;
}
dt.Clear();
dt.Dispose();
/*
* System.Collections.Generic.List<string> ls = new System.Collections.Generic.List<string>();
* foreach(System.Data.DataColumn dc in dt.Columns)
* {
* ls.Add(dc.ColumnName);
* }
* MsgBox(string.Join(" ", ls.ToArray()));
*/
}
}
catch (System.Exception ex)
{
if (Log("cOleDB_schema.TableExists", ex, "GetSchema(\"ExportTables\")"))
{
throw;
}
}
finally
{
if (dbcon.State != System.Data.ConnectionState.Closed)
{
dbcon.Close();
}
}
} // End Lock dbcon
} // End Using dbcon
/*
* strTableName = strTableName.Replace("'", "''");
*
* string strSQL = @"
* SELECT COUNT(*)
* FROM MSysObjects
* WHERE (((Left([Name],1))<> ""~"")
* AND ((Left([Name],4))<>""MSys"")
* AND ((MSysObjects.Type) In (1,4,6)))
* AND Name = '" + strTableName + "' "
* ;
*
* return ExecuteScalar<bool>(strSQL);
*/
return(bReturnValue);
} // End Function TableExists
protected void btnUpload_Click(object sender, EventArgs e)
{
if (fpUpload.HasFile)
{
string sWJBH = SysClass.SysGlobal.GetCreateGUID();
char[] de = { '\\' };
string[] Afilename = fpUpload.FileName.Split(de);
string strFileName = Afilename[Afilename.Length - 1];
string[] AExt = fpUpload.FileName.Split('.');
string strExt = "";
if (AExt.Length > 1)
{
strExt = AExt[AExt.Length - 1];
}
string sUploadFile = sWJBH;
if (strExt.Length > 0)
{
sUploadFile = sUploadFile + '.' + strExt;
}
string sPathFile = Server.MapPath("..") + "\\" + SysClass.SysUploadFile.UploadDirectory + "\\ImportExcel\\" + sUploadFile;
sPathFile = Server.MapPath("../" + SysClass.SysUploadFile.UploadDirectory);
if (Directory.Exists(sPathFile) == false)
{
Directory.CreateDirectory(sPathFile);
}
sPathFile = Server.MapPath("../" + SysClass.SysUploadFile.UploadDirectory + "/ImportExcel");
if (Directory.Exists(sPathFile) == false)
{
Directory.CreateDirectory(sPathFile);
}
sPathFile = Server.MapPath("..") + "\\" + SysClass.SysUploadFile.UploadDirectory + "\\ImportExcel\\" + sUploadFile;
fpUpload.SaveAs(sPathFile);
lblUploadFile.Text = sPathFile;
lblUploadFileName.Text = fpUpload.FileName;
string strConn, sheetname = "Sheet1$";
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + lblUploadFile.Text + ";Extended Properties=Excel 8.0;";
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn);
conn.Open();
DataTable dtExcelSchema = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
if (dtExcelSchema.Rows.Count > 0)
{
sheetname = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
}
System.Data.OleDb.OleDbDataAdapter oada = new System.Data.OleDb.OleDbDataAdapter("select * from [" + sheetname + "]", strConn);
DataSet ds = new DataSet();
oada.Fill(ds);
CyxPack.CommonOperation.DataBinder.BindGridViewData(gvLists, ds);
conn.Close();
DataSet dk = SysClass.SysUserSalary.GetUserSalaryFieldsImportLstByDataSet(" and 1<>1");
bool _HasOpName = false;
for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
{
DataRow row = dk.Tables[0].NewRow();
row["SourceName"] = ds.Tables[0].Columns[i].ColumnName;
if (ddlTargetName.Items.FindByText(ds.Tables[0].Columns[i].ColumnName) != null)
{
row["TargetName"] = ddlTargetName.Items.FindByText(ds.Tables[0].Columns[i].ColumnName).Value;
}
else if (ddlTargetName.Items.FindByText(ds.Tables[0].Columns[i].ColumnName + "|扩展") != null)
{
row["TargetName"] = ddlTargetName.Items.FindByText(ds.Tables[0].Columns[i].ColumnName + "|扩展").Value;
}
dk.Tables[0].Rows.Add(row);
if (ds.Tables[0].Columns[i].ColumnName == "姓名")
{
_HasOpName = true;
}
}
gvImportSet.DataSource = dk.Tables[0].DefaultView;
gvImportSet.DataBind();
}
else
{
lblProcess.Text = "请选择上传XLS文件。";
}
}
private static void worker_DoWork(object sender, DoWorkEventArgs e)
{
BackgroundWorker worker = sender as BackgroundWorker;
worker.ReportProgress(0, "Проверка подключения к базе...");
string path = System.IO.Path.Combine(startupPath, "DB\\auditHelper.mdb");
if (!System.IO.File.Exists(path))
{
isReady = false;
worker.ReportProgress(0, "");
worker.ReportProgress(2, "Файл базы не найден");
}
else
{
try
{
System.Data.OleDb.OleDbConnection _connection = new System.Data.OleDb.OleDbConnection();
_connection.ConnectionString = AuditHelper.Properties.Settings.Default.auditHelperConnectionString;
_connection.Open();
_connection.Close();
}
catch
{
isReady = false;
worker.ReportProgress(0, "");
worker.ReportProgress(1, "Ошибка подключения к базе");
}
}
//Соединение установлено, загружаем словари
if (isReady)
{
worker.ReportProgress(0, "Загрузка справочников...");
worker.ReportProgress(0, "Загрузка рисков...");
List <int> ids = null;
Classes.Entity tmp = null;
ids = DB.ApplicationDataMappers.LevelsOfRiskDM.GetAllIDs();
foreach (int id in ids)
{
tmp = DB.ApplicationMap.LevelsOfRisk[id];
}
worker.ReportProgress(0, "Загрузка статусов...");
ids = DB.ApplicationDataMappers.ContentStatusDM.GetAllIDs();
foreach (int id in ids)
{
tmp = DB.ApplicationMap.ContentStatus[id];
}
worker.ReportProgress(0, "Загрузка организаций...");
ids = DB.ApplicationDataMappers.OutsideOrgDM.GetAllIDs();
foreach (int id in ids)
{
tmp = DB.ApplicationMap.OutsideOrg[id];
}
worker.ReportProgress(0, "Загрузка сотрудников...");
ids = DB.ApplicationDataMappers.OutsideEmpDM.GetAllIDs();
foreach (int id in ids)
{
tmp = DB.ApplicationMap.OutsideEmp[id];
}
worker.ReportProgress(0, "Загрузка планов...");
ids = DB.ApplicationDataMappers.PlanDM.GetAllIDs();
foreach (int id in ids)
{
tmp = DB.ApplicationMap.Plan[id];
if ((tmp as Classes.Plan).NearestDate != DateTime.MaxValue)
{
if ((tmp as Classes.Plan).NearestDate <= DateTime.Now)
{
++countExpired;
}
if ((tmp as Classes.Plan).NearestDate <= DateTime.Now.AddDays(4) && (tmp as Classes.Plan).NearestDate > DateTime.Now)
{
++countNearestExpired;
}
}
}
}
}
private void btnToolImport_Click(object sender, EventArgs e)
{
OpenFileDialog openFileDialog1 = new OpenFileDialog();
openFileDialog1.InitialDirectory = Environment.SpecialFolder.MyDocuments.ToString();
openFileDialog1.Filter = "Excel Files (*.xls,*.xlsx)|*.xls;*.xlsx";
openFileDialog1.FilterIndex = 0;
openFileDialog1.Multiselect = false;
openFileDialog1.Title = "Import data from file";
openFileDialog1.RestoreDirectory = true;
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
try
{
IsExecuted = false;
System.Data.OleDb.OleDbConnection MyConnection;
System.Data.DataSet DtSet;
System.Data.OleDb.OleDbDataAdapter MyCommand;
FileInfo fi = new FileInfo(openFileDialog1.FileName);
string excelObject = "Provider=Microsoft.{0}.OLEDB.{1};Data Source={2};Extended Properties=\"Excel {3};HDR=YES\"";
if (fi.Extension.Equals(".xls"))
{
excelObject = string.Format(excelObject, "Jet", "4.0", openFileDialog1.FileName, "8.0");
}
else if (fi.Extension.Equals(".xlsx"))
{
excelObject = string.Format(excelObject, "Ace", "12.0", openFileDialog1.FileName, "12.0");
}
MyConnection = new System.Data.OleDb.OleDbConnection(excelObject);
MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection);
MyCommand.TableMappings.Add("Table", "TestTable");
DtSet = new System.Data.DataSet();
MyCommand.Fill(DtSet);
if (dgvGiven.RowCount - 1 > 0)
{
if (MessageBox.Show("Do you want to replace imported points with current ones?", "Add or replace imported point", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == System.Windows.Forms.DialogResult.Yes)
{
dgvGiven.Rows.Clear();
}
}
dgv1.DataSource = DtSet.Tables[0];
bool bVal;
string cVal;
for (int i = 0; i < dgv1.Rows.Count - 1; i++)
{
bVal = false;
if (dgv1.Columns.Count == 4)
{
bVal = (bool)dgv1.Rows[i].Cells[3].Value;
}
cVal = string.Empty;
if (dgv1.Columns.Count >= 3)
{
dgvGiven.Rows.Add(Convert.ToInt16(dgv1.Rows[i].Cells[0].Value), Convert.ToInt16(dgv1.Rows[i].Cells[1].Value), dgv1.Rows[i].Cells[2].Value, bVal, -1);
}
}
lblDataCounts.Text = (dgvGiven.Rows.Count - 1).ToString();
progressBar1.Value = 0;
txtResult.Clear();
RefreshPoints();
MyConnection.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
public static void AccessToDataSet(DataTable excelData, string uniqueField)
{
try
{
DataSet DtSet = new DataSet("DPT");
System.Data.OleDb.OleDbConnection MyConnection;
System.Data.OleDb.OleDbDataAdapter MyCommand;
//Create connection to access database and add to table in dataset
MyConnection = new System.Data.OleDb.OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source= 'C:\Space\Database\MillerCoors.mdb' ; Persist Security Info=False;");
MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [MillerCoors Beer Database]", MyConnection);
MyCommand.TableMappings.Add("Table", "AccessDataTable");
DataTable accessData1 = DtSet.Tables.Add("AccessData");
MyCommand.Fill(accessData1);
MyConnection.Close();
//create array of excel column headers
string[] columnsArray = new string[excelData.Columns.Count];
for (int i = 0; i < excelData.Columns.Count; i++)
{
columnsArray[i] = excelData.Columns[i].ToString();
}
//create array of access column headers
string[] accessFieldsArray = new string[accessData1.Columns.Count];
for (int i = 0; i < accessData1.Columns.Count; i++)
{
accessFieldsArray[i] = accessData1.Columns[i].ToString();
}
//make clone that converts all datatypes to string (for dynamic LINQ)
DataTable accessData = accessData1.Clone();
for (int i = 0; i < accessFieldsArray.Length - 1; i++)
{
accessData.Columns[i].DataType = typeof(string);
}
foreach (DataRow row in accessData1.Rows)
{
accessData.ImportRow(row);
}
//checking to see if there are duplicates between excel column headers and access column headers
var duplicates = columnsArray.Intersect(accessFieldsArray, StringComparer.OrdinalIgnoreCase);
string Intersection = String.Join(", ", duplicates);
if (Intersection.Length > 1)
{
DialogResult result = MessageBox.Show(
"By selecting OK, the UPC Wizard will remove the matching columns from the returnable fields. " +
"\n \n If you would like to change the column headers, hit Cancel. " +
"\n \n Matching columns: " + Intersection, "Excel sheet contains column headers that match the Database.", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning);
if (result == DialogResult.Cancel)
{
return;
}
foreach (var item in duplicates)
{
accessFieldsArray = Array.FindAll(accessFieldsArray, x => x != item);
}
}
//values to get back from form
string selectedColumn = uniqueField;
string columnType = null;
string returnedFields = null;
//get user input for excel field and fields to return
using (var form = new WizardFieldSelectionForm(uniqueField, accessFieldsArray))
{
var result = form.ShowDialog();
if (result == DialogResult.OK)
{
columnType = form.ColumnType;
returnedFields = form.ReturnedFields;
}
else
{
return;
}
}
//DataProcessing dataProcessing = new DataProcessing();
//dataProcessing.Show();
//dataProcessing.Update();
DataProcessing dataProcessing = new DataProcessing();
dataProcessing.Show();
dataProcessing.Update();
Commands.UpdatingMessage("UPC WIZARD");
//add returnFields as columns in excelData
string[] newColumns = returnedFields.Split(',');
for (int i = 0; i < newColumns.Length; i++)
{
string newColumn = newColumns[i];
excelData.Columns.Add(newColumn, typeof(String));
}
var caseSwitch = columnType;
switch (caseSwitch)
{
case "UPC":
GivenUPC(excelData, accessData, selectedColumn, newColumns);
break;
case "ID":
GivenID(excelData, accessData, selectedColumn, newColumns);
break;
case "Name":
GivenName(excelData, accessData, selectedColumn, newColumns);
break;
}
//bubble blanks to top
DataView sortDT = new DataView(excelData)
{
Sort = (newColumns[0]) + "," + uniqueField
};
excelData = sortDT.ToTable();
//Exports dataset back into UPC Wizard tab
Excel.Workbook excelWorkBook = Globals.ThisAddIn.GetActiveWorkbook();
Excel.Worksheet excelWorkSheet = excelWorkBook.ActiveSheet;
ExportData.ExportDataTableToExcel(excelData, excelWorkSheet);
Commands.ClearMessage();
dataProcessing.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
public async Task <TimeSeries> LoadData(ValidFileInfo fileInfo)
{
return(await Task.Run(() =>
{
try
{
var oleDbConnectionString = new OleDbConnectionString(fileInfo);
var progressItem = new ProgressManager
{
TimeStamp = DateTime.UtcNow,
Progress = 1,
Action = "OleDbLoader Load data: ",
Message = "OleDbLoader Loader Load data started:" + fileInfo.Source
};
oleDbConnectionString.ValidFileInfo.ProgressLogger?.Report(progressItem);
var stopWatch = new Stopwatch();
stopWatch.Start();
var timeSeries = new TimeSeries {
Source = oleDbConnectionString.ValidFileInfo.Source
};
//;HDR=yes
//;Format=xlsx;IMEX=1
var oledbConn = new System.Data.OleDb.OleDbConnection(oleDbConnectionString.Value);
var command = new System.Data.OleDb.OleDbDataAdapter(oleDbConnectionString.Sql, oledbConn);
command.TableMappings.Add("Table", "LoadTable");
var dtSet = new DataSet();
command.Fill(dtSet);
var table = dtSet.Tables[0];
var dateCol = table.Columns["date"];
var closeCol = table.Columns["close"];
var nameCol = table.Columns["Name"];
foreach (DataRow tableRow in table.Rows)
{
var date = Utils.SafeCastToDate(tableRow[dateCol].ToString());
var close = Utils.SafeCastToDoubleParse(tableRow[closeCol].ToString());
var tsdp = new TimeSeriesDataPoint
{
Date = Utils.SafeCastToDate(date.ToString()),
Close = Utils.SafeCastToDoubleParse(close.ToString()),
Symbol = tableRow[nameCol].ToString(),
Source = oleDbConnectionString.ValidFileInfo.Source,
LastUpdated = ConfigDataHandler.SystemName,
UpdateDate = DateTime.UtcNow
};
timeSeries.Add(tsdp);
}
oledbConn.Close();
stopWatch.Stop();
timeSeries.LoadTime = stopWatch.Elapsed;
progressItem = new ProgressManager
{
TimeStamp = DateTime.UtcNow,
Progress = 1,
Action = "OleDbLoader Load data",
Message = "OleDbLoader Loader Load data stopped" + fileInfo.Source
};
oleDbConnectionString.ValidFileInfo.ProgressLogger?.Report(progressItem);
return timeSeries;
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
return new TimeSeries();
}));
}
private void miOpen_Click(object sender, EventArgs e)
{
if (fileOpened && !TryCloseFile())
{
return;
}
OpenFileDialog ofd = new OpenFileDialog
{
DefaultExt = "*.xls;*.xlsx",
Filter = "Excel 2003(*.xls)|*.xls|Excel 2007(*.xlsx)|*.xlsx",
Title = "Выберите документ для загрузки данных"
};
if (ofd.ShowDialog() == DialogResult.OK)
{
try
{
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();
var inputForm = new InputForm("Введите название листа.");
inputForm.ShowDialog();
string select = String.Format("SELECT * FROM [{0}$]", inputForm.InputText);
System.Data.OleDb.OleDbDataAdapter ad =
new System.Data.OleDb.OleDbDataAdapter(select, con);
DataTable dt = new DataTable();
ad.Fill(dt);
dgvContent.DataSource = dt;
foreach (DataGridViewColumn column in dgvContent.Columns)
{
column.SortMode = DataGridViewColumnSortMode.NotSortable;
}
for (int i = 0; i < dgvContent.Rows.Count; i++)
{
dgvContent.Rows[i].HeaderCell.Value = (i + 1).ToString();
}
con.Close();
fileOpened = true;
openedFilePath = ofd.FileName;
SwitchGUI(true);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Произошла ошибка при открытии файла.", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
private void btnImportData_Click(object sender, EventArgs e)
{
progressBar1.Value = 0;
OpenFileDialog openFileDialog1 = new OpenFileDialog();
openFileDialog1.InitialDirectory = Environment.SpecialFolder.MyDocuments.ToString();
openFileDialog1.Filter = "Excel Files (*.xls,*.xlsx)|*.xls;*.xlsx";
openFileDialog1.FilterIndex = 0;
openFileDialog1.Multiselect = false;
openFileDialog1.Title = "Import data from file";
openFileDialog1.RestoreDirectory = true;
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
try
{
//IsExecuted = false;
System.Data.OleDb.OleDbConnection MyConnection;
System.Data.DataSet DtSet;
System.Data.OleDb.OleDbDataAdapter MyCommand;
FileInfo fi = new FileInfo(openFileDialog1.FileName);
string excelObject = "Provider=Microsoft.{0}.OLEDB.{1};Data Source={2};Extended Properties=\"Excel {3};HDR=YES\"";
if (fi.Extension.Equals(".xls"))
{
excelObject = string.Format(excelObject, "Jet", "4.0", openFileDialog1.FileName, "8.0");
}
else if (fi.Extension.Equals(".xlsx"))
{
excelObject = string.Format(excelObject, "Ace", "12.0", openFileDialog1.FileName, "12.0");
}
MyConnection = new System.Data.OleDb.OleDbConnection(excelObject);
MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection);
MyCommand.TableMappings.Add("Table", "TestTable");
DtSet = new System.Data.DataSet();
MyCommand.Fill(DtSet);
dgvTrainingData.DataSource = DtSet.Tables[0];
txtTotalData.Text = (dgvTrainingData.Rows.Count).ToString();
txtFeatureCount.Text = dgvTrainingData.Columns.Count.ToString();
//txtResult.Clear();
MyConnection.Close();
progressBar1.Value = 100;
SetLog(">>>> Data has been imported successfully...\r\nNumber of Data: " + txtTotalData.Text + ", Number of Features: " + txtFeatureCount.Text, true);
btnSettings.Enabled = btnClassicalGRASP.Enabled = btnOurGRASP.Enabled = true;
settingsInfo.RCL = dgvTrainingData.Columns.Count / 2;
settingsInfo.TotalNumberOfData = dgvTrainingData.Rows.Count;
settingsInfo.TotalNumberOfFeatures = dgvTrainingData.Columns.Count - 1;
List <string> Classes = GetElementsCount(settingsInfo.TotalNumberOfFeatures);
List <int> ClassesMembersCount = GetCategorizedMembersCount(Classes, settingsInfo.TotalNumberOfFeatures);
settingsInfo.ClassesCount = Classes.Count;
SetLog(">>>> Class elements frequencies in imported data...", false);
for (int i = 0; i < Classes.Count; i++)
{
SetLog((i + 1).ToString() + "- " + Classes[i] + ": " + ClassesMembersCount[i].ToString(), false);
}
SetLog("Total Number of classes: " + Classes.Count.ToString(), true);
txtClassesCount.Text = settingsInfo.ClassesCount.ToString();
txtSubsetCount.Text = settingsInfo.RCL.ToString();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
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;
}
}
protected void btnAdd_Click(object sender, EventArgs e)
{
if (SaveCheck())
{
string SalaryRecGuid = SysClass.SysGlobal.GetCreateGUID();
string sUpdateSQL = "", sUpdateFieldsSQL = "";
if (ddlImportRec.SelectedIndex == 0)
{
sUpdateSQL = " Insert into UserUserImportRec_Info (SalaryRecGuid, SalaryYears, Description) Values('" + SalaryRecGuid + "', '" + txtUserSalaryYears.Text + "', '" + ddlDescription.SelectedItem + "');";
CyxPack.OperateSqlServer.DataCommon.QueryData("begin " + sUpdateSQL + " end;");
}
else
{
SalaryRecGuid = ddlImportRec.SelectedValue.ToString();
sUpdateSQL = " Update UserUserImportRec_Info Set Description='" + ddlDescription.SelectedItem + "' Where SalaryRecGuid='" + SalaryRecGuid + "';";
sUpdateFieldsSQL += " Delete From UserUserImportRecFields_Info Where SalaryRecGuid='" + SalaryRecGuid + "';";
}
lblProcess.Text = "正在导入数据，请稍候。";
DateTime dtbegin = DateTime.Now;
int iUp = 0;
string strConn, sheetname = "Sheet1$";
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + lblUploadFile.Text + ";Extended Properties=Excel 8.0;";
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn);
conn.Open();
DataTable dtExcelSchema = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
if (dtExcelSchema.Rows.Count > 0)
{
sheetname = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
}
System.Data.OleDb.OleDbDataAdapter oada = new System.Data.OleDb.OleDbDataAdapter("select * from [" + sheetname + "]", strConn);
DataSet ds = new DataSet();
oada.Fill(ds);
conn.Close();
DataSet dsImportSet = LoadImportSetByDataSet();
//--导入字段列表
for (int k = 0; k < dsImportSet.Tables[0].Rows.Count; k++)
{
if (dsImportSet.Tables[0].Rows[k]["TargetName"].ToString().Length > 0)
{
sUpdateFieldsSQL += " Insert UserUserImportRecFields_Info (SalaryRecGuid, FieldName, SortID) Values('" + SalaryRecGuid.ToString() + "','" + dsImportSet.Tables[0].Rows[k]["TargetName"].ToString() + "'," + k.ToString() + ");";
}
}
if (CyxPack.OperateSqlServer.DataCommon.QueryData("begin " + sUpdateFieldsSQL + " end;") > 0)
{
}
//--导入数据
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
if (ddlImportRec.SelectedIndex > 0)
{
sUpdateSQL = " Update UserSalary_Info Set SalaryDate=SalaryDate";
for (int n = 0; n < dsImportSet.Tables[0].Rows.Count; n++)
{
string sTargetFieldName = dsImportSet.Tables[0].Rows[n]["TargetName"].ToString();
string sSourceFieldName = dsImportSet.Tables[0].Rows[n]["SourceName"].ToString();
if (dsImportSet.Tables[0].Rows[n]["TargetName"].ToString().Length > 0)
{
string sValue = ds.Tables[0].Rows[i][sSourceFieldName].ToString();
if ((sTargetFieldName.IndexOf("Text") >= 0) ||
((sTargetFieldName.IndexOf("OpCode") >= 0)))
{
sUpdateSQL += "," + dsImportSet.Tables[0].Rows[n]["TargetName"].ToString() + "='" + sValue + "'";
}
else
{
if (sValue.Length > 0)
{
sUpdateSQL += "," + dsImportSet.Tables[0].Rows[n]["TargetName"].ToString() + "=" + sValue;
}
else
{
sUpdateSQL += "," + dsImportSet.Tables[0].Rows[n]["TargetName"].ToString() + "=0";
}
}
}
}
sUpdateSQL += " Where SalaryYears='" + txtUserSalaryYears.Text + "' And SalaryRecGuid='" + SalaryRecGuid + "' And OpCode='" + ds.Tables[0].Rows[i][GetOpCodeFieldName()].ToString() + "'";
}
else
{
sUpdateSQL = " Insert Into UserSalary_Info (SalaryYears, SalaryRecGuid, SalaryDate";
for (int n = 0; n < dsImportSet.Tables[0].Rows.Count; n++)
{
if (dsImportSet.Tables[0].Rows[n]["TargetName"].ToString().Length > 0)
{
sUpdateSQL += "," + dsImportSet.Tables[0].Rows[n]["TargetName"].ToString();
}
}
sUpdateSQL += ")"
+ " Values("
+ " '" + txtUserSalaryYears.Text + "'"
+ ",'" + SalaryRecGuid + "'"
+ " ,Getdate()";
for (int n = 0; n < dsImportSet.Tables[0].Rows.Count; n++)
{
string sTargetFieldName = dsImportSet.Tables[0].Rows[n]["TargetName"].ToString();
string sSourceFieldName = dsImportSet.Tables[0].Rows[n]["SourceName"].ToString();
if (sTargetFieldName.Length > 0)
{
string sValue = ds.Tables[0].Rows[i][sSourceFieldName].ToString();
if ((sTargetFieldName.IndexOf("Text") >= 0) ||
((sTargetFieldName.IndexOf("OpCode") >= 0)))
{
sUpdateSQL += ",'" + sValue + "'";
}
else
{
if (sValue.Length > 0)
{
sUpdateSQL += "," + sValue;
}
else
{
sUpdateSQL += ",0";
}
}
}
}
sUpdateSQL += ");";
}
if (CyxPack.OperateSqlServer.DataCommon.QueryData("begin " + sUpdateSQL + " end;") > 0)
{
iUp += 1;
lblProcess.Text = "正在导入第" + iUp.ToString() + "条记录，总" + ds.Tables[0].Rows.Count.ToString() + "条";;
}
}
if (sUpdateSQL.Length > 0)
{
if (iUp > 0)
{
lblProcess.Text = "已经成功导入 " + iUp.ToString() + " 条记录。";
Dialog.OpenDialogInAjax(upForm, "恭喜您，数据导入成功" + iUp.ToString() + " 条记录……", "SalaryImport_Step1.aspx");
}
else
{
Dialog.OpenDialogInAjax(upForm, "恭喜您，数据导入失败……");
}
}
}
}
private void AddToList(string msg)
{
int n;
DateTime st = System.DateTime.Now;
if (Bt_Record.Text == "Stop Rec.")
{
DGV_Statistics[3, 0].Value = (st.Ticks - LastReceiveTime.Ticks) / 1000000.0; // DGV_Statistics[3,0].Value = st.Subtract(LastReceiveTime);
if (int.Parse(txt_time.Text) <= 0) txt_time.Text = "1000";
textBox1.Text =Math.Truncate((((st.Ticks - LastReceiveTime.Ticks) / 10000.0) - double.Parse(txt_time.Text))).ToString();
textBox2.Text =Math.Truncate((double.Parse(textBox2.Text) + double.Parse(textBox1.Text))).ToString();
}
LastReceiveTime = st; //last receive time
if (msg.IndexOf("GW_ID=")>-1) // discovering gateways
{
int ind = msg.IndexOf("=");
int indx = (msg.IndexOf("\r")-ind-1);
cur_GW_ID += msg.Substring(ind+1,indx);
bool add_gw = false;
int y=int.Parse(msg.Substring(ind+1));
foreach(int i in G_Variables.Gateways_ID)
if (y == G_Variables.Gateways_ID[i]) add_gw = true;
if (!add_gw) //if it is new one so it will be added
{ G_Variables.Gateways_ID[G_Variables.No_GWs_Availble++] = int.Parse(msg.Substring(ind + 1)); txt_time.Text = (1000 * G_Variables.No_GWs_Availble)+""; }
for (int i = 0; i < (DGV_Gateways_Available.Rows.Count - 1); i++)
if (int.Parse(DGV_Gateways_Available.Rows[i].Cells[0].Value.ToString()) == y)
DGV_Gateways_Available.Rows[i].Cells[4].Value = Properties.Resources.on;
}
else if((msg.Contains("Connected"))||(msg.Contains("Disconnected")))
{
n = msg_listbox.Items.Add(msg);
msg_listbox.SelectedIndex = n;
msg_listbox.ClearSelected();
}
else
{
System.Data.OleDb.OleDbConnection MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + Cur_Ds_File + "';Extended Properties='Excel 8.0;HDR=Yes'");
n = Lb_Sensors_Data.Items.Add(msg);
Lb_Sensors_Data.SelectedIndex = n; Lb_Sensors_Data.ClearSelected();
if (Bt_Record.Text == "Stop Rec.")
{
count_sensor_records++;
DGV_Statistics[0, 0].Value = count_sensor_records + ""; lab_SensorRecords.Text = "Recieved =" + count_sensor_records + " msg.(s)";
}
DateTime d = new DateTime();
d = DateTime.Parse(DateTime.Now.Year + "/" + DateTime.Now.Month + "/" + DateTime.Now.Day + " " + DateTime.Now.Hour + ":" + DateTime.Now.Minute);
if ((start_record) && (msg.Contains("@")))// && (rec_state != "Network"))
{
bool missing_headers_order = false;
try
{
int ind_under = (msg.IndexOf('_') - 1);
string eds = msg.Substring(2, ind_under - 1);
int gw = int.Parse(eds);
string s = msg.Substring(msg.LastIndexOf(":") + 1);
int x = s.LastIndexOf(',');
s = s.Substring(0, s.Length - 2);
s = s.Replace("@", "");
string gw_headers = "";
for (int i = 0; i < G_Variables.No_GWs_Availble; i++)
if (G_Variables.Gateways_ID[i] == gw)
{
if (Cur_Type == "Network")
gw_headers = headers[i];
else
gw_headers = headers[0];
}
x = gw_headers.LastIndexOf(',');
gw_headers = gw_headers.Substring(0, gw_headers.Length - 1);
var hlist = gw_headers.Split(',');
var vlist = s.Split(',');
// MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + Cur_Ds_File + "';Extended Properties='Excel 8.0;HDR=Yes'");
MyConnection.Open();
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand();
// to update
string sets = ""; string sql = "";
int NumOfUpdates = 0;
if ((Cur_Type == "Network") && (G_Variables.No_GWs_Availble > 1))
{
for (int i = 0; i < hlist.Length - 1; i++)
{
try { sets += "[" + hlist[i] + "]=" + vlist[i] + ","; }
catch { missing_headers_order = true; }
}
sets += "[" + hlist[hlist.Length - 1] + "]=" + vlist[vlist.Length - 1];
sql = " update [" + Cur_File_Sheet + "] set " + sets + " where [RecDateTime] like '" + d + "' ";
cmd.CommandText = sql;
cmd.Connection = MyConnection;
NumOfUpdates = cmd.ExecuteNonQuery();
}
if (NumOfUpdates == 0)
{
sets = "";
for (int i = 0; i < hlist.Length - 1; i++)
sets += "[" + hlist[i] + "],";
sets += "[" + hlist[hlist.Length - 1] + "]";
sql = "insert into [" + Cur_File_Sheet + "] ([RecDateTime]," + sets + ") values ('" + d + "'," + s + ")";
cmd.CommandText = sql;
cmd.Connection = MyConnection;
cmd.ExecuteNonQuery();
Lab_FileRows.Text = "Recorded=" + (++count_file_rows) + " row(s)";
DGV_Statistics[1, 0].Value = count_file_rows + " row(s)";
//DGV_Statistics[2, 0].Value = System.DateTime.Now.Ticks - st.Ticks / 10000000.0;
// DGV_Statistics[4, 0].Value =""+ DateTime.Parse(DGV_Statistics[2, 0].Value.ToString()).Add( DateTime.Parse(DGV_Statistics[3, 0].Value.ToString());
}
MyConnection.Close();
}
catch { if (missing_headers_order) MessageBox.Show("Please Close Coordinator then press discover followed by reset rules and file creation, Press Yes for append message to continue"); }
DGV_Statistics[2, 0].Value = (System.DateTime.Now.Ticks - st.Ticks) / 1000000.0; //recording time
textBox3.Text = Math.Truncate(((System.DateTime.Now.Ticks - st.Ticks)/10000.0)).ToString();
textBox4.Text = Math.Truncate((double.Parse(textBox4.Text) + double.Parse(textBox3.Text))).ToString();
textBox5.Text = Math.Truncate((double.Parse(textBox2.Text) + double.Parse(textBox4.Text))).ToString();
textBox6.Text = (1-(double.Parse(textBox5.Text) / (int.Parse(DGV_Statistics[0, 0].Value.ToString()) * int.Parse(txt_time.Text)))).ToString();
}
else if ((start_record) && (msg.Contains("="))) //recieve arbitrary msgs according to changes from all devices at all available gateways
{
//try
//{
int ind_under = (msg.IndexOf('_') - 1);
string eds = msg.Substring(2, ind_under - 1);
int gw = int.Parse(eds);
string s = msg.Substring(msg.LastIndexOf(":") + 1);
int x = s.LastIndexOf(',');
s = s.Substring(0, s.Length - 2);
string gw_headers_insert = "";
string gw_inser_values = "";
string gw_headers_update = "";
var vlist = s.Split(',');
for (int i = 0; i < vlist.Length - 1; i++)
{
var pin_value = vlist[i].Split('=');
string sqlstr = "SELECT title, id, root_id FROM dbo.device WHERE (gateway_id = " + gw + ") AND (pin_id = " + pin_value[0] + ")";
con2db.Close();
con2db.Open();
SqlCommand cmdd = new SqlCommand(sqlstr, con2db);
SqlDataReader r = cmdd.ExecuteReader();
r.Read();
gw_headers_insert += "[" + r.GetString(0) + "(" + r.GetInt32(1) + "/" + r.GetInt32(2) + ":" + pin_value[0] + "/" + gw + ")]";
gw_inser_values += pin_value[1];
gw_headers_update += gw_headers_insert + "=" + pin_value[1];
gw_headers_insert += ",";
gw_inser_values += ",";
gw_headers_update += ",";
}
var lpin_value = vlist[vlist.Length - 1].Split('=');
string lsqlstr = "SELECT title, id, root_id FROM dbo.device WHERE (gateway_id = " + gw + ") AND (pin_id = " + lpin_value[0] + ")";
con2db.Close();
con2db.Open();
SqlCommand lcmd = new SqlCommand(lsqlstr, con2db);
SqlDataReader lr = lcmd.ExecuteReader();
lr.Read();
gw_headers_insert += "[" + lr.GetString(0) + "(" + lr.GetInt32(1) + "/" + lr.GetInt32(2) + ":" + lpin_value[0] + "/" + gw + ")]";
gw_inser_values += lpin_value[1];
gw_headers_update += gw_headers_insert + "=" + lpin_value[1];
int NumOfUpdates = 0;
MyConnection.Close();
MyConnection.Open();
System.Data.OleDb.OleDbCommand execlcmd = new System.Data.OleDb.OleDbCommand();
string excelstr = " update [" + Cur_File_Sheet + "] set " + gw_headers_update + " where [RecDateTime] like '" + d + "' ";
//cmd =(sql, MyConnection);
execlcmd.CommandText = excelstr;
execlcmd.Connection = MyConnection;
try
{
NumOfUpdates = execlcmd.ExecuteNonQuery();
}
catch { }
if (NumOfUpdates == 0)
{
excelstr = "insert into [" + Cur_File_Sheet + "] ([RecDateTime]," + gw_headers_insert + ") values ('" + d + "'," + gw_inser_values + ")";
execlcmd.CommandText = excelstr;
execlcmd.Connection = MyConnection;
execlcmd.ExecuteNonQuery();
Lab_FileRows.Text = "Recorded=" + (++count_file_rows) + " row(s)";
DGV_Statistics[1, 0].Value = count_file_rows + " row(s)";
}
MyConnection.Close();
//}
//catch { }
} //end else if changes
else { }
}
//DGV_Statistics[2, 0].Value = (System.DateTime.Now.Ticks - st.Ticks) / 1000000.0; //recording time
if (Bt_Record.Text == "Stop Rec.")
{
double tl = double.Parse(DGV_Statistics[2, 0].Value.ToString()) + double.Parse(DGV_Statistics[3, 0].Value.ToString());
totallatency += tl;
// MessageBox.Show(""+(totallatency / (1+count_msg)));
DGV_Statistics[4, 0].Value = totallatency;
// DGV_Statistics[5, 0].Value = totallatency / (1 + count_sensor_records);
DGV_Statistics[5, 0].Value =100-( 100*( (totallatency / (1 + count_sensor_records))/int.Parse(txt_time.Text)));
list_SynTA.Items.Add(DGV_Statistics[5, 0].Value);
list_SynTA.SelectedIndex=list_SynTA.Items.Count-1;
}
}
public bool AggiornaUtente_pri(UtenteProgramma u)
{
var s = "";
var i = 0;
var n = 0;
try
{
var p = System.IO.Path.Combine(Server.MapPath("public"), "/files/files.mdb");
using (var c = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + p))
{
try
{
c.Open();
try
{
s = "SELECT count(*) Num FROM Utenti where Programma=:Programma and Nome=:Nome";
var pr = new System.Data.OleDb.OleDbParameter[] {
new System.Data.OleDb.OleDbParameter("Programma", u.Programma),
new System.Data.OleDb.OleDbParameter("Nome", u.Utente)
};
using (var m = new System.Data.OleDb.OleDbCommand(s, c))
{
m.Parameters.AddRange(pr);
using (var dr = m.ExecuteReader())
{
while (dr.HasRows && dr.Read())
{
try
{
n = Convert.ToInt32(dr["Num"]);
}
catch
{
//error
n = 0;
}
}
dr.Close();
}
}
}
catch
{
//error
}
try
{
if (n > 0)
{
s = "update Utenti set Versione=:Versione where Nome=:Nome and Programma=:Programma";
}
else
{
s = "insert into Utenti (Versione,Nome,Programma) values (:Versione,:Nome,:Programma)";
}
var pr = new System.Data.OleDb.OleDbParameter[] {
new System.Data.OleDb.OleDbParameter("Versione", u.Versione),
new System.Data.OleDb.OleDbParameter("Nome", u.Utente),
new System.Data.OleDb.OleDbParameter("Programma", u.Programma)
};
using (var m = new System.Data.OleDb.OleDbCommand(s, c))
{
m.Parameters.AddRange(pr);
i = m.ExecuteNonQuery();
}
}
catch
{
//error
}
c.Close();
}
catch
{
//error
}
}
}
catch
{
//error
}
return(i > 0);
}
public void CloseConnection()
{
MyConnection.Close();
}
protected void btnAdd_Click(object sender, EventArgs e)
{
if (SaveCheck())
{
string sUpdateSQL = "", sEditSQL = "", sCustomInsertSQL = "", sCustomEditSQL = "";
lblProcess.Text = "正在导入数据，请稍候。";
DateTime dtbegin = DateTime.Now;
int iUp = 0;
string strConn, sheetname = "Sheet1$";
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + lblUploadFile.Text + ";Extended Properties=Excel 8.0;";
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn);
conn.Open();
DataTable dtExcelSchema = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
if (dtExcelSchema.Rows.Count > 0)
{
sheetname = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
}
System.Data.OleDb.OleDbDataAdapter oada = new System.Data.OleDb.OleDbDataAdapter("select * from [" + sheetname + "]", strConn);
DataSet ds = new DataSet();
oada.Fill(ds);
conn.Close();
DataSet dsImportSet = LoadImportSetByDataSet();
string _GetOpCodeFieldName = GetOpCodeFieldName();
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
sUpdateSQL = " if not Exists(Select top 1 1 From SysUser_Info Where OpCode='" + ds.Tables[0].Rows[i][_GetOpCodeFieldName].ToString() + "') "
+ " begin";
sCustomInsertSQL = " if not Exists(Select top 1 1 From SysUserExt_Info Where UserID in (Select ID From SysUser_Info Where OpCode='" + ds.Tables[0].Rows[i][_GetOpCodeFieldName].ToString() + "')) "
+ " begin";
sUpdateSQL += " Insert Into SysUser_Info (SystemID , Guid, OrganID, Password";
sEditSQL = " Update SysUser_Info Set SystemID=SystemID, OrganID=";
sCustomInsertSQL += " Insert Into SysUserExt_Info (UserID ";
sCustomEditSQL = " Update SysUserExt_Info Set UserID=SysUser_Info.ID";
for (int n = 0; n < dsImportSet.Tables[0].Rows.Count; n++)
{
string sTargetFieldName = dsImportSet.Tables[0].Rows[n]["TargetName"].ToString();
if (sTargetFieldName != "Organ")
{
if (sTargetFieldName.Length > 0)
{
if (dsImportSet.Tables[0].Rows[n]["TargetName"].ToString().IndexOf("|") > 0)
{
string _TargerName = dsImportSet.Tables[0].Rows[n]["TargetName"].ToString();
_TargerName = "Column_" + _TargerName.Substring(2, _TargerName.Length - 2);
sCustomInsertSQL += "," + _TargerName;
}
else
{
sUpdateSQL += "," + dsImportSet.Tables[0].Rows[n]["TargetName"].ToString();
}
}
}
}
sUpdateSQL += ")"
+ " Values("
+ SysClass.SysParams.GetPurviewSystemID().ToString()
+ ", '" + SysClass.SysGlobal.GetCreateGUID() + "'";
sCustomInsertSQL += ")"
+ " (Select ID ";
string _OrganFieldName = GetOrganFieldName();
if ((_OrganFieldName.Length > 0) && (SysClass.SysOrgan.GetOrganIDByOrganName(ds.Tables[0].Rows[i][_OrganFieldName].ToString()) > 0))
{
int _TOrganID = SysClass.SysOrgan.GetOrganIDByOrganName(ds.Tables[0].Rows[i][_OrganFieldName].ToString());
if (_TOrganID == 0)
{
_TOrganID = _OrganID;
}
sUpdateSQL += ", " + _TOrganID.ToString();
sEditSQL += _TOrganID.ToString();
}
else
{
//sUpdateSQL += ", " + _OrganID.ToString();
sUpdateSQL += ", 58"; //不存在的单位导入临时单位下
sEditSQL += "OrganID";
}
sUpdateSQL += " ,'111111'";
for (int n = 0; n < dsImportSet.Tables[0].Rows.Count; n++)
{
string sTargetFieldName = dsImportSet.Tables[0].Rows[n]["TargetName"].ToString();
string sSourceFieldName = dsImportSet.Tables[0].Rows[n]["SourceName"].ToString();
if (sTargetFieldName != "Organ")
{
if (sTargetFieldName.Length > 0)
{
if (dsImportSet.Tables[0].Rows[n]["TargetName"].ToString().IndexOf("|") > 0)
{
string sValue = ds.Tables[0].Rows[i][sSourceFieldName].ToString();
sCustomInsertSQL += ",'" + sValue + "'";
string _TargerName = dsImportSet.Tables[0].Rows[n]["TargetName"].ToString();
string _A = _TargerName.Substring(0, 1);
_TargerName = _TargerName.Substring(2, _TargerName.Length - 2);
if (_A == "1")
{
sCustomEditSQL += ",Column_" + _TargerName + "='" + sValue + "'";
}
else
{
if (sValue.Length == 0)
{
sCustomEditSQL += "," + "Column_" + _TargerName + "=0";
}
else
{
sCustomEditSQL += "," + "Column_" + _TargerName + "=" + sValue;
}
}
}
else
{
string sValue = ds.Tables[0].Rows[i][sSourceFieldName].ToString();
string _TargerName = dsImportSet.Tables[0].Rows[n]["TargetName"].ToString();
if (_TargerName == "IsCanLogin")
{
if (sValue == "是")
{
sValue = "1";
}
else
{
sValue = "0";
}
sUpdateSQL += "," + sValue;
sEditSQL += "," + _TargerName + "=" + sValue + "";
}
else if (_TargerName == "Sex")
{
if (sValue == "女")
{
sValue = "1";
}
else
{
sValue = "0";
}
sUpdateSQL += ",'" + sValue + "'";
sEditSQL += "," + _TargerName + "='" + sValue + "'";
}
else
{
sUpdateSQL += ",'" + sValue + "'";
sEditSQL += "," + _TargerName + "='" + sValue + "'";
}
}
}
}
}
sCustomEditSQL += " From SysUser_Info Where SysUser_Info.OpCode='" + ds.Tables[0].Rows[i][_GetOpCodeFieldName].ToString() + "' And SysUserExt_Info.UserID=SysUser_Info.ID; ";
sCustomInsertSQL += " From SysUser_Info Where OpCode='" + ds.Tables[0].Rows[i][_GetOpCodeFieldName].ToString() + "'); end "
+ " else begin " + sCustomEditSQL + " end;";
sEditSQL += " Where OpCode='" + ds.Tables[0].Rows[i][_GetOpCodeFieldName].ToString() + "';";
sUpdateSQL += ");";
sUpdateSQL += " end else begin " + sEditSQL + " end; " + sCustomInsertSQL;
if (CyxPack.OperateSqlServer.DataCommon.QueryData("begin " + sUpdateSQL + " end;") > 0)
{
iUp += 1;
lblProcess.Text = "正在导入第" + iUp.ToString() + "条记录，总" + ds.Tables[0].Rows.Count.ToString() + "条";;
}
}
string _SQL = "begin"
+ " Insert Into SysUserRoles_Info (SystemID, UserID, RoleID) (Select 2, ID, 30 From SysUser_Info Where ID not in (Select UserID From SysUserRoles_Info Where SystemID=2));"
+ " Delete from SysUserRoles_Info Where UserID not in (Select ID From SysUser_Info);"
+ " end;";
CyxPack.OperateSqlServer.DataCommon.QueryData("begin " + _SQL + " end;");
if (sUpdateSQL.Length > 0)
{
if (iUp > 0)
{
lblProcess.Text = "已经成功导入 " + iUp.ToString() + " 条记录。";
Dialog.OpenDialogInAjax(upForm, "恭喜您，数据导入成功" + iUp.ToString() + " 条记录……", "User_Import.aspx?OrganID=" + _OrganID.ToString());
}
else
{
Dialog.OpenDialogInAjax(upForm, "恭喜您，数据导入失败……");
}
}
}
}
public void SaveScenarioProperties()
{
string strTemp1;
string strTemp2;
string strSQL = "";
bool bCore = false;
string strDesc = "";
if (this.lstScenario.Visible == true) //new scenario
{
//validate the input
//Optimization id
if (this.txtScenarioId.Text.Length == 0)
{
MessageBox.Show("Enter A Unique Optimization scenario Id");
this.txtScenarioId.Focus();
return;
}
//check for duplicate scenario id
if (this.lstScenario.Items.Count > 0)
{
strTemp2 = this.txtScenarioId.Text.Trim();
for (int x = 0; x <= this.lstScenario.Items.Count - 1; x++)
{
strTemp1 = this.lstScenario.Items[x].ToString().Trim();
if (strTemp1.ToUpper() == strTemp2.ToUpper())
{
MessageBox.Show("Cannot have a duplicate Optimization scenario id");
this.txtScenarioId.Focus();
return;
}
}
}
//make sure user entered scenario path
if (this.txtScenarioPath.Text.Length > 0)
{
//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 scenario_results database to the new project directory
string strSourceFile = ((frmMain)this.ParentForm.ParentForm).frmProject.uc_project1.m_strProjectDirectory + "\\" + ScenarioType + "\\db\\scenario_results.mdb";
string strDestFile = this.txtScenarioPath.Text + "\\db\\scenario_results.mdb";
System.IO.File.Copy(strSourceFile, strDestFile, true);
}
}
catch
{
MessageBox.Show("Error Creating Folder");
return;
}
}
else
{
MessageBox.Show("Enter A Directory Location To Save Optimization scenario Files");
this.txtScenarioPath.Focus();
return;
}
//copy the project data source values to the scenario data source
ado_data_access p_ado = new ado_data_access();
string strProjDBDir = ((frmMain)this.ParentForm.ParentForm).frmProject.uc_project1.m_strProjectDirectory + "\\db";
string strProjFile = "project.mdb";
StringBuilder strProjFullPath = new StringBuilder(strProjDBDir);
strProjFullPath.Append("\\");
strProjFullPath.Append(strProjFile);
string strProjConn = p_ado.getMDBConnString(strProjFullPath.ToString(), "admin", "");
System.Data.OleDb.OleDbConnection p_OleDbProjConn = new System.Data.OleDb.OleDbConnection();
p_ado.OpenConnection(strProjConn, ref p_OleDbProjConn);
string strScenarioDBDir = ((frmMain)this.ParentForm.ParentForm).frmProject.uc_project1.m_strProjectDirectory + "\\" + ScenarioType + "\\db";
string strScenarioFile = "scenario_" + ScenarioType + "_rule_definitions.mdb";
StringBuilder strScenarioFullPath = new StringBuilder(strScenarioDBDir);
strScenarioFullPath.Append("\\");
strScenarioFullPath.Append(strScenarioFile);
string strScenarioConn = p_ado.getMDBConnString(strScenarioFullPath.ToString(), "admin", "");
p_ado.OpenConnection(strScenarioConn);
if (p_ado.m_intError == 0)
{
if (this.txtDescription.Text.Trim().Length > 0)
{
strDesc = p_ado.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');";
//"'" + this.txtScenarioMDBFile.Text.Trim() + "');";
p_ado.SqlNonQuery(p_ado.m_OleDbConnection, strSQL);
p_ado.SqlQueryReader(p_OleDbProjConn, "select * from datasource");
if (p_ado.m_intError == 0)
{
try
{
while (p_ado.m_OleDbDataReader.Read())
{
bCore = false;
switch (p_ado.m_OleDbDataReader["table_type"].ToString().Trim().ToUpper())
{
case "PLOT":
bCore = true;
break;
case "CONDITION":
bCore = true;
break;
//case "FIRE AND FUEL EFFECTS":
// bCore = true;
// break;
case "HARVEST COSTS":
bCore = true;
break;
case "TREATMENT PRESCRIPTIONS":
bCore = true;
break;
case "TREE VOLUMES AND VALUES BY SPECIES AND DIAMETER GROUPS":
bCore = true;
break;
case "TRAVEL TIMES":
bCore = true;
break;
case "PROCESSING SITES":
bCore = true;
break;
case "TREE SPECIES AND DIAMETER GROUPS DOLLAR VALUES":
bCore = true;
break;
default:
break;
}
if (bCore == true)
{
strSQL = "INSERT INTO scenario_datasource (scenario_id,table_type,Path,file,table_name) VALUES " + "('" + this.txtScenarioId.Text.Trim() + "'," +
"'" + p_ado.m_OleDbDataReader["table_type"].ToString().Trim() + "'," +
"'" + p_ado.m_OleDbDataReader["path"].ToString().Trim() + "'," +
"'" + p_ado.m_OleDbDataReader["file"].ToString().Trim() + "'," +
"'" + p_ado.m_OleDbDataReader["table_name"].ToString().Trim() + "');";
p_ado.SqlNonQuery(p_ado.m_OleDbConnection, strSQL);
}
}
}
catch (Exception caught)
{
intError = -1;
strError = caught.Message;
MessageBox.Show(strError);
}
if (p_ado.m_intError == 0)
{
if (ScenarioType.Trim().ToUpper() == "OPTIMIZER")
{
((frmOptimizerScenario)ParentForm).uc_datasource1.strScenarioId = this.txtScenarioId.Text.Trim();
((frmOptimizerScenario)ParentForm).uc_datasource1.strDataSourceMDBFile = ((frmMain)ParentForm.ParentForm).frmProject.uc_project1.txtRootDirectory.Text.Trim() + "\\" + ScenarioType + "\\db\\scenario_" + ScenarioType + "_rule_definitions.mdb";
((frmOptimizerScenario)ParentForm).uc_datasource1.strDataSourceTable = "scenario_datasource";
((frmOptimizerScenario)ParentForm).uc_datasource1.strProjectDirectory = ((frmMain)ParentForm.ParentForm).frmProject.uc_project1.txtRootDirectory.Text.Trim();
}
else
{
this.ReferenceProcessorScenarioForm.uc_datasource1.strScenarioId = this.txtScenarioId.Text.Trim();
this.ReferenceProcessorScenarioForm.uc_datasource1.strDataSourceMDBFile = ((frmMain)ParentForm.ParentForm).frmProject.uc_project1.txtRootDirectory.Text.Trim() + "\\" + ScenarioType + "\\db\\scenario_" + ScenarioType + "_rule_definitions.mdb";
this.ReferenceProcessorScenarioForm.uc_datasource1.strDataSourceTable = "scenario_datasource";
this.ReferenceProcessorScenarioForm.uc_datasource1.strProjectDirectory = ((frmMain)ParentForm.ParentForm).frmProject.uc_project1.txtRootDirectory.Text.Trim();
}
}
p_ado.m_OleDbDataReader.Close();
p_ado.m_OleDbDataReader = null;
p_ado.m_OleDbCommand = null;
p_OleDbProjConn.Close();
p_OleDbProjConn = null;
}
if (ScenarioType.Trim().ToUpper() == "OPTIMIZER")
{
string strTemp = p_ado.FixString("SELECT @@PlotTable@@.* FROM @@PlotTable@@ ", "'", "''");
strSQL = "INSERT INTO scenario_plot_filter (scenario_id,sql_command,current_yn) VALUES " + "('" + this.txtScenarioId.Text.Trim() + "'," +
"'" + strTemp + "'," +
"'Y');";
p_ado.SqlNonQuery(p_ado.m_OleDbConnection, strSQL);
strTemp = p_ado.FixString("SELECT @@CondTable@@.* FROM @@CondTable@@", "'", "''");
strSQL = "INSERT INTO scenario_cond_filter (scenario_id,sql_command,current_yn) VALUES " + "('" + this.txtScenarioId.Text.Trim() + "'," +
"'" + strTemp + "'," +
"'Y');";
p_ado.SqlNonQuery(p_ado.m_OleDbConnection, strSQL);
}
}
p_ado.m_OleDbConnection.Close();
p_ado.m_OleDbConnection = null;
p_ado = null;
this.btnCancel.Enabled = false;
this.btnOpen.Enabled = true;
this.lstScenario.Enabled = true;
this.txtScenarioId.Visible = false;
this.lblNewScenario.Visible = false;
this.txtScenarioPath.Enabled = false;
this.lstScenario.Items.Add(this.txtScenarioId.Text);
this.lstScenario.SelectedIndex = this.lstScenario.Items.Count - 1;
}
else
{
ado_data_access p_ado = new ado_data_access();
System.Data.OleDb.OleDbConnection oConn = new System.Data.OleDb.OleDbConnection();
string strProjDir = ((frmMain)this.ParentForm.ParentForm).frmProject.uc_project1.m_strProjectDirectory;
string strScenarioDir = ((frmMain)this.ParentForm.ParentForm).frmProject.uc_project1.m_strProjectDirectory + "\\" + ScenarioType + "\\db";
string strFile = "scenario_" + ScenarioType + "_rule_definitions.mdb";
if (ScenarioType.Trim().ToUpper() == "OPTIMIZER")
{
((frmOptimizerScenario)ParentForm).uc_datasource1.strScenarioId = this.txtScenarioId.Text.Trim();
((frmOptimizerScenario)ParentForm).uc_datasource1.strDataSourceMDBFile = strScenarioDir + "\\scenario_" + ScenarioType + "_rule_definitions.mdb";
((frmOptimizerScenario)ParentForm).uc_datasource1.strDataSourceTable = "scenario_datasource";
((frmOptimizerScenario)ParentForm).uc_datasource1.strProjectDirectory = strProjDir;
}
else
{
this.ReferenceProcessorScenarioForm.uc_datasource1.strScenarioId = this.txtScenarioId.Text.Trim();
this.ReferenceProcessorScenarioForm.uc_datasource1.strDataSourceMDBFile = strScenarioDir + "\\scenario_" + ScenarioType + "_rule_definitions.mdb";
this.ReferenceProcessorScenarioForm.uc_datasource1.strDataSourceTable = "scenario_datasource";
this.ReferenceProcessorScenarioForm.uc_datasource1.strProjectDirectory = strProjDir;
}
StringBuilder strFullPath = new StringBuilder(strScenarioDir);
strFullPath.Append("\\");
strFullPath.Append(strFile);
if (this.txtDescription.Text.Trim().Length > 0)
{
strDesc = p_ado.FixString(this.txtDescription.Text.Trim(), "'", "''");
}
string strConn = p_ado.getMDBConnString(strFullPath.ToString(), "admin", "");
//string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFullPath.ToString() + ";User Id=admin;Password=;";
strSQL = "UPDATE scenario SET description = '" +
strDesc +
"' WHERE scenario_id = '" + this.txtScenarioId.Text.ToLower() + "';";
p_ado.SqlNonQuery(strConn, strSQL);
p_ado = null;
}
if (ScenarioType.Trim().ToUpper() == "OPTIMIZER")
{
if (((frmOptimizerScenario)this.ParentForm).m_bScenarioOpen == false)
{
((frmOptimizerScenario)this.ParentForm).Text = "Core Analysis: Optimization Scenario (" + this.txtScenarioId.Text.Trim() + ")";
((frmOptimizerScenario)this.ParentForm).SetMenu("scenario");
((frmOptimizerScenario)this.ParentForm).m_bScenarioOpen = true;
this.lblTitle.Text = "";
this.Visible = false;
}
}
else
{
if (this.ReferenceProcessorScenarioForm.m_bScenarioOpen == false)
{
this.ReferenceProcessorScenarioForm.Text = "Processor: Scenario (" + this.txtScenarioId.Text.Trim() + ")";
this.ReferenceProcessorScenarioForm.m_bScenarioOpen = true;
this.lblTitle.Text = "";
this.Visible = false;
}
}
}
private void btnUpdate_Click(object sender, RoutedEventArgs e)
{
if (openedFilePath == string.Empty)
{
System.Windows.MessageBox.Show("No sheet opened!");
return;
}
// remove original file
try
{
File.Delete(openedFilePath);
}catch (Exception ex)
{
System.Windows.MessageBox.Show(ex.Message);
return;
}
string connectionString = getConnectionStr(openedFilePath, openedFileExtention);
using (System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(connectionString))
{
con.Open();
StringBuilder strSQL = new StringBuilder();
System.Data.OleDb.OleDbCommand cmd;
System.Data.DataTable dt = ((System.Data.DataView)grdData.ItemsSource).Table;
// create sheet/table
strSQL.Append("CREATE TABLE ").Append("[" + dt.TableName + "]");
strSQL.Append("(");
for (int i = 0; i < dt.Columns.Count; i++)
{
strSQL.Append("[" + dt.Columns[i].ColumnName + "] text,");
}
strSQL = strSQL.Remove(strSQL.Length - 1, 1);
strSQL.Append(")");
cmd = new System.Data.OleDb.OleDbCommand(strSQL.ToString(), con);
cmd.ExecuteNonQuery();
// insert data line by line
for (int i = 0; i < dt.Rows.Count; i++)
{
if (dt.Rows[i].RowState == System.Data.DataRowState.Deleted)
{
continue;
}
strSQL.Clear();
StringBuilder strvalue = new StringBuilder();
for (int j = 0; j < dt.Columns.Count; j++)
{
strvalue.Append("'" + dt.Rows[i][j].ToString() + "'");
if (j != dt.Columns.Count - 1)
{
strvalue.Append(",");
}
}
cmd.CommandText = strSQL.Append(" insert into [" + dt.TableName + "] values (").Append(strvalue).Append(")").ToString();
cmd.ExecuteNonQuery();
}
con.Close();
}
System.Windows.MessageBox.Show("update successfully");
}
private void Get_Click(object sender, EventArgs e)
{
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection();
conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\PenDrive\mydatabase.accdb;User Id=admin;Password=;";
conn.Open();
string string1 = "Select STARTTIME,ENDTIME from Lecture";
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(string1, conn);
System.Data.OleDb.OleDbDataReader r = cmd.ExecuteReader();
#region while loop
while (r.Read())
{
String string2 = r["STARTTIME"].ToString();
String string3 = r["ENDTIME"].ToString();
//STARTTIME_Get.Text = string2;
//ENDTIME_Get.Text = string3;
}
#endregion
#region while loop with Dictionary
/* int i = 1;
while (r.Read())
{
string string7="";
Dictionary<string, int> d = new Dictionary<string, int>()
{
{r["STARTTIME"].ToString(),i}
};
foreach (var pair in d)
{
string7 = pair.Key;
}
STARTTIME_Get.Text = string7;
i = i + 1;
} */
#endregion
r.Close();
conn.Close();
}
/// <summary> 查看 案卷级 的 电子案卷 </summary>
/// <param name="FileID">案卷ID</param>
public MemoryStream OpenFilePDF(int FileID)
{
MemoryStream ms = new MemoryStream();
if (!UIPublicClass.PublicClass.IsSingleClent)
{
SqlConnection conn = new SqlConnection(DataSql.ConStr);
string sql = "select Pdf from XfPdf.dbo.CjFilePdf where 案卷ID = " + FileID;
try
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = sql;
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
ms = new MemoryStream((byte[])dr[0]); //创建Stream对象
}
else
{
ms = null;
}
dr.Close();
conn.Close();
return(ms);
}
catch (System.Exception e)
{
MessageBox.Show("打开失败！" + e.Message);
return(null);
}
}
else
{
string connstr = DataSql.ConStr_Pdf;
string sql = "select Pdf from CJFilePdf where 案卷ID = " + FileID;
try
{
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(connstr);
conn.Open();
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand();
cmd.Connection = conn;
cmd.CommandText = sql;
System.Data.OleDb.OleDbDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
ms = new MemoryStream((byte[])dr[0]); //创建Stream对象
}
else
{
ms = null;
}
dr.Close();
conn.Close();
return(ms);
}
catch (System.Exception e)
{
MessageBox.Show("打开失败！" + e.Message);
return(null);
}
}
}
static void Main(string[] args)
{
if (args.Count() > 0)
{
if (args[0] == "ship")
{
#region Mark As Shipped
try
{
//This is a fix for it being a desktop application. This should not
//be run on a live webserver
ServicePointManager.ServerCertificateValidationCallback = delegate { return(true); };
//Use app.config so the directory can be changed regardless of the code
string dir = ConfigurationManager.AppSettings["ShippingData"].ToString();
//grab all the files in the directory
Directory.GetFiles(dir);
foreach (var file in Directory.GetFiles(dir))
{
//Get just the name of the file
FileInfo import = new FileInfo(file);
string filename = import.Name.Split('.')[0];
//Split on '_' and it should create a array with the length of 3
string[] filenamesplit = filename.Split('_');
if (filenamesplit.Length == 4)
{
string consumerKey = filenamesplit[0];
string consumerSecret = filenamesplit[1];
var client = new RestClient();
client.BaseUrl = APIHelper.GetBaseUri();
client.Authenticator = new HttpBasicAuthenticator(consumerKey, consumerSecret);
var summaries = ImportHelper.ImportShippingSummaries(file);
if (summaries.FirstOrDefault() != null && summaries.FirstOrDefault().Company_ID != "-1")
{
//changed to false if any of the orders fail to be posted.
bool allShipped = true;
foreach (var summary in summaries)
{
//Use the summary information to create a JSON object
string order = OrderServices.GetOrderByOrderNumber(client, summary);
//if the string did not return and error try to
//do a post.
if (!order.StartsWith("An Error Has Occured"))
{
//Shipsation async code, but it causes business logic problems
//Task tsk = OrderServices.MakeAsyncOrderPost(order, consumerKey, consumerSecret);
//tsk.Wait();
//Post the order to be marked.
allShipped = OrderServices.MarkOrderPost(client, order);
}
else
{
allShipped = false;
}
}
if (allShipped)
{
// File.Delete(file);
//Update first row companyID to -1
string DSN = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0", file);
using (System.Data.OleDb.OleDbConnection Con = new System.Data.OleDb.OleDbConnection(DSN))
{
Con.Open();
using (System.Data.OleDb.OleDbCommand Com = new System.Data.OleDb.OleDbCommand())
{
Com.Connection = Con;
Com.CommandText = "UPDATE [Shipping_Summary___Optional_Sav$] SET [Company_ID] = -1 WHERE [Company_ID] = " + summaries.FirstOrDefault().Company_ID;
Com.ExecuteNonQuery();
}
Con.Close();
}
}
else
{
MailMessage mail = new MailMessage();
SmtpClient SmtpServer = new SmtpClient("smtp.gmail.com");
mail.From = new MailAddress("*****@*****.**");
mail.To.Add("*****@*****.**");
mail.Subject = "An Error Has Occured Posting to Ship Station";
mail.Body = string.Format("Not all orders for the file {0} were marked as shipped", import.Name);
SmtpServer.Port = 587;
SmtpServer.Credentials = new NetworkCredential("*****@*****.**", "5HB7c9ut");
SmtpServer.EnableSsl = true;
SmtpServer.Send(mail);
}
}
}
}
}
catch (Exception ex)
{
MailMessage mail = new MailMessage();
SmtpClient SmtpServer = new SmtpClient("smtp.gmail.com");
mail.From = new MailAddress("*****@*****.**");
mail.To.Add("*****@*****.**");
mail.Subject = "An Error Has Occured Posting to Ship Station";
mail.Body = string.Format(ex.ToString());
SmtpServer.Port = 587;
SmtpServer.Credentials = new System.Net.NetworkCredential("*****@*****.**", "5HB7c9ut");
SmtpServer.EnableSsl = true;
SmtpServer.Send(mail);
}
#endregion
}
if (args[0] == "orders")
{
#region Pull Order
var customers = ImportHelper.ImportShipstationCustomers();
foreach (var customer in customers)
{
Console.WriteLine("Processing {0}", customer.CompanyName);
var client = new RestClient();
client.BaseUrl = APIHelper.GetBaseUri();
client.Authenticator = new HttpBasicAuthenticator(customer.Key, customer.Token);
//RestRequest request = new RestRequest("users", Method.GET);
//IRestResponse response = client.Execute(request);
//get all the stores
Console.WriteLine("Getting All Stores");
List <Store> stores = StoreService.GetAllStores(client);
Console.WriteLine("Pulling down unshipped orders");
//var orders = OrderServices.GetOrders(client, storeId);
List <Order> orders = new List <Order>();
foreach (var store in stores)
{
Console.WriteLine("Pulling down unshipped orders for {0}", store.storeName);
orders.AddRange(OrderServices.GetOrders(client, store.storeId));
}
var SClientorders = orders.Where(o => o.userId == APIHelper.GetShipstationUserID()).ToList();
//check to see if the id's have been
List <string> extractedids = new List <string>();
using (StreamReader reader = new StreamReader(System.Configuration.ConfigurationManager.AppSettings["Path"] + "extractedids.txt"))
{
string line = reader.ReadLine();
while (line != null)
{
extractedids.Add(line.Trim());
line = reader.ReadLine();
}
reader.Close();
}
foreach (var id in extractedids)
{
var order = SClientorders.Where(xo => xo.orderNumber == id).FirstOrDefault();
if (order != null)
{
SClientorders.Remove(order);
}
}
if (SClientorders.Count > 0)
{
Console.WriteLine("Creating Extraction Report Count : {0}", SClientorders.Count);
ExportHelpers.ExtractionReport(SClientorders, customer, stores);
Console.WriteLine("Sending Email");
MailHelpers.SendEmailWithOrders(customer.CompanyName);
Console.WriteLine("Finished Processing {0}", customer.CompanyName);
//save the id's
using (StreamWriter writer = new StreamWriter(System.Configuration.ConfigurationManager.AppSettings["Path"] + "extractedids.txt", true))
{
foreach (var order in SClientorders.OrderBy(y => y.orderNumber))
{
writer.WriteLine(order.orderNumber);
}
}
}
else
{
MailMessage mail = new MailMessage();
SmtpClient SmtpServer = new SmtpClient("smtp.gmail.com");
mail.From = new MailAddress("*****@*****.**");
mail.To.Add("[email protected],[email protected]");
mail.Bcc.Add("*****@*****.**");
mail.Subject = customer.CompanyName + " Orders From Ship Station - No New Orders";
mail.Body = string.Format("No New Orders");
SmtpServer.Port = 587;
SmtpServer.Credentials = new System.Net.NetworkCredential("*****@*****.**", "5HB7c9ut");
SmtpServer.EnableSsl = true;
SmtpServer.Send(mail);
}
}
#endregion
}
Console.WriteLine("done");
}
else
{
MailMessage mail = new MailMessage();
SmtpClient SmtpServer = new SmtpClient("smtp.gmail.com");
mail.From = new MailAddress("*****@*****.**");
mail.To.Add("*****@*****.**");
mail.Subject = "An Error Has Occured Posting to Ship Station";
mail.Body = string.Format("No Argument Has Been Passed");
SmtpServer.Port = 587;
SmtpServer.Credentials = new System.Net.NetworkCredential("*****@*****.**", "5HB7c9ut");
SmtpServer.EnableSsl = true;
SmtpServer.Send(mail);
}
}
/// <include file='InterfaceDocumentationComments.xml' path='doc/members/member[@name="M:OperatingSystemAbstraction.IOleDbConnection.Close"]/*'/>
public void Close()
{
oleDbConnection.Close();
}
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));
}
public void LoadMe()
{
System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection();
con.ConnectionString =
"Provider=Microsoft.Jet.OLEDB.4.0;"
+ "Data Source=acct.mdb;";
con.Open();
string quryString = "select * from Expenses where accountid=" + this.accountid + " order by id desc";
System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(quryString, con);
DataSet ds = new DataSet();
da.Fill(ds, "Expenses");
dataGridView1.Columns.Clear();
dataGridView1.Columns.Add("id", "");
dataGridView1.Columns.Add("paymentMethod", "Payment");
dataGridView1.Columns.Add("company", "Company");
dataGridView1.Columns.Add("firstName", "First Name");
dataGridView1.Columns.Add("lastName", "Last Name");
dataGridView1.Columns.Add("date", "Date");
dataGridView1.Columns.Add("amount", "Amount");
dataGridView1.Columns.Add("expenseNote", "Note");
//set autosize mode
dataGridView1.Columns[0].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
dataGridView1.Columns[1].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
dataGridView1.Columns[2].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
dataGridView1.Columns[3].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
dataGridView1.Columns[4].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
dataGridView1.Columns[5].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
//datagrid has calculated it's widths so we can store them
for (int i = 0; i <= dataGridView1.Columns.Count - 1; i++)
{
//store autosized widths
int colw = dataGridView1.Columns[i].Width;
//remove autosizing
dataGridView1.Columns[i].AutoSizeMode = DataGridViewAutoSizeColumnMode.None;
//set width to calculated by autosize
dataGridView1.Columns[i].Width = colw;
}
int row = ds.Tables["Expenses"].Rows.Count - 1;
for (int r = 0; r <= row; r++)
{
dataGridView1.Rows.Add();
dataGridView1.Rows[r].Cells[0].Value = ds.Tables["Expenses"].Rows[r].ItemArray[6];
dataGridView1.Rows[r].Cells[1].Value = ds.Tables["Expenses"].Rows[r].ItemArray[8];
dataGridView1.Rows[r].Cells[2].Value = ds.Tables["Expenses"].Rows[r].ItemArray[7];
dataGridView1.Rows[r].Cells[3].Value = ds.Tables["Expenses"].Rows[r].ItemArray[0];
dataGridView1.Rows[r].Cells[4].Value = ds.Tables["Expenses"].Rows[r].ItemArray[1];
dataGridView1.Rows[r].Cells[5].Value = ds.Tables["Expenses"].Rows[r].ItemArray[2];
dataGridView1.Rows[r].Cells[6].Value = ds.Tables["Expenses"].Rows[r].ItemArray[3];
dataGridView1.Rows[r].Cells[7].Value = ds.Tables["Expenses"].Rows[r].ItemArray[5];
}
quryString = "select * from Income where accountid=" + this.accountid + " order by id desc";
da = new System.Data.OleDb.OleDbDataAdapter(quryString, con);
ds = new DataSet();
da.Fill(ds, "Income");
dataGridView2.Columns.Clear();
dataGridView2.Columns.Add("id", "");
dataGridView2.Columns.Add("paymentMethod", "Payment");
dataGridView2.Columns.Add("company", "Company");
dataGridView2.Columns.Add("firstName", "First Name");
dataGridView2.Columns.Add("lastName", "Last Name");
dataGridView2.Columns.Add("date", "Date");
dataGridView2.Columns.Add("amount", "Amount");
dataGridView2.Columns.Add("incomeNote", "Note");
//set autosize mode
dataGridView2.Columns[0].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
dataGridView2.Columns[1].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
dataGridView2.Columns[2].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
dataGridView2.Columns[3].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
dataGridView2.Columns[4].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
dataGridView2.Columns[5].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
//datagrid has calculated it's widths so we can store them
for (int i = 0; i <= dataGridView2.Columns.Count - 1; i++)
{
//store autosized widths
int colw = dataGridView2.Columns[i].Width;
//remove autosizing
dataGridView2.Columns[i].AutoSizeMode = DataGridViewAutoSizeColumnMode.None;
//set width to calculated by autosize
dataGridView2.Columns[i].Width = colw;
}
row = ds.Tables["Income"].Rows.Count - 1;
for (int r = 0; r <= row; r++)
{
dataGridView2.Rows.Add();
dataGridView2.Rows[r].Cells[0].Value = ds.Tables["Income"].Rows[r].ItemArray[7];
dataGridView2.Rows[r].Cells[1].Value = ds.Tables["Income"].Rows[r].ItemArray[10];
dataGridView2.Rows[r].Cells[2].Value = ds.Tables["Income"].Rows[r].ItemArray[5];
dataGridView2.Rows[r].Cells[3].Value = ds.Tables["Income"].Rows[r].ItemArray[0];
dataGridView2.Rows[r].Cells[4].Value = ds.Tables["Income"].Rows[r].ItemArray[1];
dataGridView2.Rows[r].Cells[5].Value = ds.Tables["Income"].Rows[r].ItemArray[2];
dataGridView2.Rows[r].Cells[6].Value = ds.Tables["Income"].Rows[r].ItemArray[3];
dataGridView2.Rows[r].Cells[7].Value = ds.Tables["Income"].Rows[r].ItemArray[6];
}
con.Close();
}
private void createAndFillTreesRef()
{
//DataTable dtTrees = null;
using (System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(dbLc))
{
con.Open();
//Console.WriteLine("opened the database");
string sql = "SELECT SPCD, JENKINS_TOTAL_B1, JENKINS_TOTAL_B2, JENKINS_STEM_WOOD_RATIO_B1, JENKINS_STEM_WOOD_RATIO_B2, JENKINS_STEM_BARK_RATIO_B1, JENKINS_STEM_BARK_RATIO_B2, JENKINS_FOLIAGE_RATIO_B1, JENKINS_FOLIAGE_RATIO_B2 FROM REF_SPECIES";
System.Data.OleDb.OleDbCommand oleCom = new System.Data.OleDb.OleDbCommand(sql, con);
System.Data.OleDb.OleDbDataReader oleRd = oleCom.ExecuteReader();
while (oleRd.Read())
{
object[] vls = new object[9];
oleRd.GetValues(vls);
bmDic.Add(vls[0].ToString(),vls);
}
oleRd.Close();
sql = "SELECT CN, PLT_CN, SUBP, TREE, SPCD, DIA, HT, STATUSCD FROM TREE WHERE DIA >= 5 and SPCD > 0";
oleCom.CommandText = sql;
oleRd = oleCom.ExecuteReader();
while (oleRd.Read())
{
object[] vls = new object[8];
oleRd.GetValues(vls);
string pltCn = vls[1].ToString();
string subp = vls[2].ToString();
if(SubPlotField==""||SubPlotField==null)subp="0";
string linkVl = pltCn+"_"+subp;
if(unPlots.Contains(linkVl))
{
string spcd = vls[4].ToString();
//adding in live and dead
string stcd = vls[7].ToString();
string grpCd = spcd;
if (groupstatuscode) grpCd = spcd + "_" + stcd;
object[] bmVls;
if(bmDic.TryGetValue(spcd,out bmVls))
{
if (grp) spcd = findGrp(grpCd);//findGrp(spcd);
unSp.Add(spcd);
string lk = linkVl+"_"+spcd.ToString();
object[] treeVls;
if (vlDic.TryGetValue(lk, out treeVls))
{
}
else
{
treeVls = new object[12];
treeVls[0] = pltCn;
treeVls[1] = subp;
treeVls[2] = spcd;
treeVls[3] = 0;//dbh
treeVls[4] = 0;//AGB
treeVls[5] = 0;//StemAGB
treeVls[6] = 0;//ButtAGB
treeVls[7] = 0;//FoliageAGB
treeVls[8] = 0;//TopAGB
treeVls[9] = 0;//BAA
treeVls[10] = 0;//TPA
treeVls[11] = 0;//MeanHT
vlDic.Add(lk,treeVls);
}
//Console.WriteLine(System.Convert.ToDouble(vls[5]).ToString());
double diam = System.Convert.ToDouble(vls[5]);//diameter
double ht = System.Convert.ToDouble(vls[6]);//height
treeVls[3] = System.Convert.ToDouble(treeVls[3])+diam;
double j_tbm = Math.Exp(System.Convert.ToDouble(bmVls[1])+System.Convert.ToDouble(bmVls[2])*Math.Log(diam*2.54))*2.2046;//AGB
double j_sbm = j_tbm*Math.Exp(System.Convert.ToDouble(bmVls[3])+System.Convert.ToDouble(bmVls[4])/ (2.54*diam));//SAGB
double j_bbm = j_tbm*Math.Exp(System.Convert.ToDouble(bmVls[5])+System.Convert.ToDouble(bmVls[6])/ (2.54*diam));//BAGB
double j_fbm = j_tbm*Math.Exp(System.Convert.ToDouble(bmVls[7])+System.Convert.ToDouble(bmVls[8])/ (2.54*diam));//FAGB
double j_tpbm = j_tbm - j_sbm - j_bbm - j_fbm;//TPAGB
treeVls[4] = System.Convert.ToDouble(treeVls[4]) + j_tbm;
treeVls[5] = System.Convert.ToDouble(treeVls[5]) + j_sbm;
treeVls[6] = System.Convert.ToDouble(treeVls[6]) + j_bbm;
treeVls[7] = System.Convert.ToDouble(treeVls[7]) + j_fbm;
treeVls[8] = System.Convert.ToDouble(treeVls[8]) + j_tpbm;
treeVls[9] = System.Convert.ToDouble(treeVls[9]) + 0.005454 * Math.Pow(diam, 2);//BA
treeVls[10] = System.Convert.ToDouble(treeVls[10]) + 1.0;//trees
treeVls[11] = System.Convert.ToDouble(treeVls[11]) + ht;
}
}
}
oleRd.Close();
con.Close();
}
}
public void insertall(string path)
{
try
{
System.Data.OleDb.OleDbConnection yaalconnection;
System.Data.DataSet DtSet;
System.Data.OleDb.OleDbDataAdapter MyCommand;
// public static string path = @"C:\src\RedirectApplication\RedirectApplication\301s.xlsx";
// public static string connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;";
//yaalconnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0;Data Source='Z:\\33BRKPR0174G1ZS_032019_R2A.xlsx';Extended Properties=Excel 12.0;");
yaalconnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;");
MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [B2B$]", yaalconnection);
MyCommand.TableMappings.Add("Table", "TestTable");
DtSet = new System.Data.DataSet();
MyCommand.Fill(DtSet);
dataGridView1.DataSource = DtSet.Tables[0];
yaalconnection.Close();
System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(" Provider = Microsoft.ACE.OLEDB.12.0; Data Source =D:\\json\\test.accdb");
System.Data.OleDb.OleDbCommand comm = con.CreateCommand();
con.Open();
String gstno;
string mystring;
mystring = "";
string res;
res = "";
for (int i = 0; i < dataGridView1.RowCount; i++)
{
for (int j = 0; j < dataGridView1.ColumnCount; j++)
{
if (dataGridView1.Rows[i].Cells[j].Value == DBNull.Value)
{
dataGridView1.Rows[i].Cells[j].Value = 0;
}
}
}
dataGridView1.Update();
int strlen;
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
if (dataGridView1.Rows[i].Cells[2].Value != null && dataGridView1.Rows[i].Cells[2].ToString() != String.Empty)
{
res = "";
string value = (string)dataGridView1.Rows[i].Cells[2].Value;
mystring = value;
strlen = mystring.Length;
if (strlen > 5)
{
res = mystring.Substring(strlen - 5, 5);
}
}
string path1 = "";
string StrQuery;
if (res == "Total")
{
double amount = (double)(dataGridView1.Rows[i].Cells[5].Value);
if (amount > 0)
{
//33BKMPP8726K1ZJ_012019_R2A.xlsx
path1 = path.Substring(path.Length - 15, 6);
StrQuery = @"INSERT INTO test(gstno,monthname,invno,invdt,amount,TaxAmt,camount,samount,name)
VALUES('" + dataGridView1.Rows[i].Cells[0].Value + "', '" + path1 + "','" + dataGridView1.Rows[i].Cells[2].Value + "','" + dataGridView1.Rows[i].Cells[4].Value + "'," + dataGridView1.Rows[i].Cells[5].Value + "," + dataGridView1.Rows[i].Cells[9].Value + ", " + dataGridView1.Rows[i].Cells[11].Value + ", " + dataGridView1.Rows[i].Cells[12].Value + ", '" + dataGridView1.Rows[i].Cells[1].Value + "'); ";
//string StrQuery = "INSERT INTO tableName VALUES ('" + dataGridView1.Rows[i].Cells[0].Value + "',' " + dataGridView1.Rows[i].Cells[1].Value + "', '" + dataGridView1.Rows[i].Cells[2].Value + "', '" + dataGridView1.Rows[i].Cells[3].Value + "',' " + dataGridView1.Rows[i].Cells[4].Value + "')";
// 11,12,1
// StrQuery = @"INSERT INTO test VALUES (" + dataGridView1.Rows[i].Cells["ColumnName"].Text + ", "
// + dataGridView1.Rows[i].Cells["ColumnName"].Text + ");";
//VALUES ('" + dataGridView1.Rows[i].Cells[0].Value +"'," + dataGridView1.Rows[i].Cells[5].Value +",'" + dataGridView1.Rows[i].Cells[1].Value +"');";
comm.Connection = con;
comm.CommandText = StrQuery;
comm.ExecuteNonQuery();
}
}
}
// cmd.CommandText = "Insert into test(test)Values(1)";
//comm.Connection = con;
//comm.ExecuteNonQuery();
MessageBox.Show("Record Submitted", "Congrats");
con.Close();
MessageBox.Show("Completed");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void button1_Click(object sender, EventArgs e)
{
if (comboBox1.Text.ToString() == "")
{
MessageBox.Show("Выбери год", "Внимание!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else if (comboBox2.Text.ToString() == "")
{
MessageBox.Show("Выбери месяц", "Внимание!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else if (textBox1.Text == "")
{
MessageBox.Show("Выбери файл", "Внимание!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
string strConn;
//Check for Excel version
if (textBox1.Text.Substring(textBox1.Text.LastIndexOf('.')).ToLower() == ".xlsx")
{
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + textBox1.Text + ";Extended Properties=\"Excel 12.0;HDR=YES; IMEX=0\"";
}
else
{
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + textBox1.Text + ";Extended Properties=\"Excel 8.0;HDR=YES; IMEX=0\"";
}
System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(strConn);
con.Open();
DataSet ds = new DataSet();
DataTable shemaTable = con.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables,
new object[] { null, null, null, "TABLE" });
string sheet1 = (string)shemaTable.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);
if (System.IO.File.Exists("your_base.mdb"))
{
int year = Convert.ToInt32(comboBox1.Text.ToString());
int month = Convert.ToInt32(comboBox2.Text.ToString());
string conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=your_base.mdb;Jet OLEDB:Engine Type=5";
System.Data.OleDb.OleDbConnection connectDb = new System.Data.OleDb.OleDbConnection(conStr);
connectDb.Open();
System.Data.OleDb.OleDbCommand myCMD = new System.Data.OleDb.OleDbCommand();
myCMD.Connection = connectDb;
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
myCMD.CommandText = "Insert into SURVEY (Anim, weight, height, s_year, s_month) VALUES (\"" +
ds.Tables[0].Rows[i][0] + "\", " + ds.Tables[0].Rows[i][1] + ", " + ds.Tables[0].Rows[i][2] + ", " + year + ", " + month + ")";
myCMD.ExecuteNonQuery();
}
MessageBox.Show("Данные загружены в БД", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
int year = Convert.ToInt32(comboBox1.Text.ToString());
int month = Convert.ToInt32(comboBox2.Text.ToString());
ADOX.Catalog cat = new ADOX.Catalog();
string connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Jet OLEDB:Engine Type=5";
cat.Create(String.Format(connstr, "your_base.mdb"));
cat = null;
string conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=your_base.mdb;Jet OLEDB:Engine Type=5";
Querry("CREATE TABLE SURVEY(Anim varchar(255), weight int, height int, s_year int, s_month int);", "your_base.mdb");
System.Data.OleDb.OleDbConnection connectDb = new System.Data.OleDb.OleDbConnection(conStr);
connectDb.Open();
System.Data.OleDb.OleDbCommand myCMD = new System.Data.OleDb.OleDbCommand();
myCMD.Connection = connectDb;
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
myCMD.CommandText = "Insert into SURVEY (Anim, weight, height, s_year, s_month) VALUES (\"" +
ds.Tables[0].Rows[i][0] + "\", " + ds.Tables[0].Rows[i][1] + ", " + ds.Tables[0].Rows[i][2] + ", " + year + ", " + month + ")";
myCMD.ExecuteNonQuery();
}
//string comm = "Insert into SURVEY (Anim, weight, height) VALUES (hare, 10, 20)";
//System.Data.OleDb.OleDbDataAdapter dbAdp = new System.Data.OleDb.OleDbDataAdapter(comm, conStr);
//dbAdp.Update(ds.Tables[0]);
MessageBox.Show("Данные загружены в БД", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
con.Close();
}
}
// create excel sheet for WoT hierarchical structure
public static void write_hierarchicals_excel(string dir, SqlConnection con2db)
{
string path = dir + "\\WoT";
string filename = "WoT_Structure.xls";
string sheet1 = "Building", sheet2 = "Device";
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
if (File.Exists(path + "\\" + filename))
{
File.Delete(path + "\\" + filename);
}
string header1 = "[id]int,[title]string,[root_id]int,[type]string,[description]string,[image_url]string";
string header2 = "[id]int,[building_id]int,[title]string,[type]string,[model]string,[serial]string,[manufacturer]string,[description]string,[gateway_id]int,[pin_id]int,[ws_address]string,[img_on_url]string,[img_off_url]string";
System.Data.OleDb.OleDbConnection ExcelCon;
ExcelCon = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + path + "\\" + filename + "';Extended Properties='Excel 8.0;HDR=Yes'");
ExcelCon.Open();
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand("create table [" + sheet1 + "] (" + header1 + " ) ", ExcelCon);
cmd.ExecuteNonQuery();
cmd = new System.Data.OleDb.OleDbCommand("create table [" + sheet2 + "] (" + header2 + " ) ", ExcelCon);
cmd.ExecuteNonQuery();
ExcelCon.Close();
string sql1 = "Select id, title, root_id, type, description from building";
string sql2 = "Select id, root_id, title, type, model, serial, manufacturer, description, gateway_id, pin_id,ws_address from device";
con2db.Close();
con2db.Open();
ExcelCon.Close();
ExcelCon.Open();
SqlCommand sqlcmd = new SqlCommand(sql1, con2db);
SqlDataAdapter dt = new SqlDataAdapter(sqlcmd);
DataSet st = new DataSet();
dt.Fill(st);
DataRowCollection dtc = st.Tables[0].Rows;
foreach (DataRow dr in dtc)
{
try { int.Parse(dr[2] + ""); } catch { dr[2] = 0; }
//string txt = dr[4].ToString();
for (int i = 0; i < 5; i++)
{
if (dr[i].ToString().Contains("'"))
{
dr[i] = dr[i].ToString().Replace("'s", "''s");
}
}
cmd.CommandText = " insert into [" + sheet1 + "] ([id],[title],[root_id],[type],[description]) values (" + dr[0] + ",'" + dr[1].ToString() + "'," + dr[2] + ",'" + dr[3].ToString() + "','" + dr[4] + "')";
cmd.Connection = ExcelCon;
cmd.ExecuteNonQuery();
}
con2db.Close();
con2db.Open();
ExcelCon.Close();
ExcelCon.Open();
sqlcmd = new SqlCommand(sql2, con2db);
dt = new SqlDataAdapter(sqlcmd);
st = new DataSet();
dt.Fill(st);
dtc = st.Tables[0].Rows;
foreach (DataRow dr in dtc)
{
cmd.CommandText = " insert into [" + sheet2 + "] ([id],[building_id],[title],[type],[model],[serial],[manufacturer],[description],[gateway_id],[pin_id],[ws_address]) values (" + dr[0] + "," + dr[1] + ",'" + dr[2] + "','" + dr[3] + "','" + dr[4] + "','" + dr[5] + "','" + dr[6] + "','" + dr[7] + "'," + dr[8] + "," + dr[9] + ",'" + dr[10] + "')"; cmd.Connection = ExcelCon;
cmd.ExecuteNonQuery();
}
con2db.Close();
ExcelCon.Close();
}
private void button1_Click(object sender, EventArgs e)
{
//Mostrar na listBox2 os alunos do curso selecionado no listBox1:
try
{
listBox2.Items.Clear(); //limpamos a segunda lista
string cursoSelec = (string)((DataRowView)listBox1.SelectedItem)[0];
string sql = "SELECT * FROM Alunos WHERE abrevcurso = '" + cursoSelec + "'";
//suponhamos o BD em C:\tempo
System.Data.OleDb.OleDbConnection conexao =
new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/temp/Universidade3.mdb");
conexao.Open();
System.Data.OleDb.OleDbCommand comando =
new System.Data.OleDb.OleDbCommand(sql, conexao);
System.Data.OleDb.OleDbDataReader dr = comando.ExecuteReader();
while (dr.Read())
{
string dadosAluno = dr.GetString(0)
+ ", " + dr.GetString(1) + ", " + dr.GetString(2)
+ ", " + dr.GetInt32(3) + ", " + dr.GetString(4);
listBox2.Items.Add(dadosAluno);
}
dr.Close();
comando.Dispose();
conexao.Close();
}
catch (Exception exc) { MessageBox.Show(exc.Message); }
}