public Class1()
{
string test = "INSERT INTO ImportAccess(Time,Source,Condition,Action,Level,Description,Value,Units,Operator) VALUES ('1','2','3','4','5','6','7','8','9');INSERT INTO ImportAccess(Time,Source,Condition,Action,Level,Description,Value,Units,Operator) VALUES ('1','2','3','4','5','6','7','8','9');";
string connString = "Provider = Microsoft.ACE.OLEDB.12.0; Data Source = C:\\Users\\hycy_tabby\\Databases\\ImportAccessExample.accdb; Persist Security Info = False";
OleDbConnection conn = new OleDbConnection(connString);
conn.Open();
executeNonQuery(conn, test);
}
public static DataSet ImportExcel(this DataSet dataSet, String filename, bool hasHeaders = true)
{
if (filename.IsNotNullOrWhiteSpace()) return default(DataSet);
var connectionString = String.Empty;
var HDR = hasHeaders ? "Yes" : "No";
var extension = Path.GetExtension(filename);
if (extension.IsNotNullOrWhiteSpace())
switch (extension.Substring(1))
{
case "xls": //Excel 97-03
connectionString = String.Format(
@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1};IMEX=1'",
filename, HDR);
break;
case "xlsx": //Excel 07
connectionString = String.Format(
@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1};IMEX=1'",
filename, HDR);
break;
}
dataSet = new DataSet(Path.GetFileNameWithoutExtension(filename));
using (var conOleDb = new OleDbConnection(connectionString))
{
conOleDb.Open();
//{TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE}
var arrRestrict = new Object[] {null, null, null, "TABLE"};
var dtSchema = conOleDb.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, arrRestrict);
if (null != dtSchema)
{
foreach (DataRow drSchema in dtSchema.Rows)
{
var nameSheet =
//drSchema[dtSchema.Columns[2].ColumnName].ToString();
drSchema["TABLE_NAME"].ToString();
var selectQuery = String.Format("SELECT * FROM [{0}]", nameSheet);
var table = new DataTable(nameSheet);
var cmdOleDb = new OleDbCommand(selectQuery, conOleDb) { CommandType = CommandType.Text };
new OleDbDataAdapter(cmdOleDb).Fill(table);
dataSet.Tables.Add(table);
}
}
conOleDb.Close();
}
RemoveNull(dataSet);
return dataSet;
}
protected void btnUpload_Click(object sender, EventArgs e)
{
if (conn_sql.State == ConnectionState.Closed)
{
conn_sql.Open();
}
string filePath = ConfigurationManager.AppSettings["FilePath"].ToString();
string filename = string.Empty;
string new_filePath = "";
if (fileUpload.HasFile)
{
try
{
string[] allowedFile = { ".XLS", ".XLSX" };
string FileExt = System.IO.Path.GetExtension(fileUpload.PostedFile.FileName);
bool isValidFile = allowedFile.Contains(FileExt);
if (!isValidFile)
{
lblMessage.Text = FileExt;
}
else
{
filename = Path.GetFileName(Server.MapPath(fileUpload.FileName));
fileUpload.SaveAs(Server.MapPath(filePath) + filename);
new_filePath = Server.MapPath(filePath) + filename;
OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + new_filePath + ";Extended Properties='Excel 12.0 XML;';");
con.Open();
//excel from storage
OleDbCommand ExcelCommand = new OleDbCommand("SELECT * FROM [Sheet3$]", con);
OleDbDataAdapter ExcelAdapter = new OleDbDataAdapter(ExcelCommand);
DataSet ExcelDataSet = new DataSet();
ExcelAdapter.Fill(ExcelDataSet);
gvStorage.DataSource = ExcelDataSet.Tables[0];
gvStorage.DataBind();
//insert to database storage
try
{
for (int i = 0; i < gvStorage.Rows.Count; i++)
{
string storage_location = gvStorage.Rows[i].Cells[0].Text;
string warehouse = gvStorage.Rows[i].Cells[1].Text;
if (warehouse.Trim() == " ")
{
warehouse = string.Empty;
}
SqlCommand cmd = new SqlCommand("insert into storage(storage_location,warehouse) values(@Storage_location,@Warehouse)", conn_sql);
cmd.Parameters.AddWithValue("@Storage_location", storage_location);
cmd.Parameters.AddWithValue("@Warehouse", warehouse);
cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
lblMessage.Text += ex.Message;
};
if (ExcelDataSet.Tables.Count == 0)
{
lblMessage.Text += "Storage Failed";
}
else
{
lblMessage.Text += "Storage Success! ";
}
//excel from detail_cs
ExcelCommand = new OleDbCommand("SELECT * FROM [Sheet1$]", con);
ExcelAdapter = new OleDbDataAdapter(ExcelCommand);
ExcelDataSet = new DataSet();
ExcelAdapter.Fill(ExcelDataSet);
//deleting rows if all cell are empty
for (int i = 5; i < 24; i++)
{
ExcelDataSet.Tables[0].Rows.RemoveAt(i);
}
GridView1.DataSource = ExcelDataSet.Tables[0];
GridView1.DataBind();
//insert to database detail_cs
try
{
for (int k = 1; k < GridView1.Rows.Count; k++)
{
string exception = GridView1.Rows[k].Cells[0].Text;
string material = GridView1.Rows[k].Cells[1].Text;
string plant = GridView1.Rows[k].Cells[2].Text;
string storage_location = GridView1.Rows[k].Cells[3].Text;
string material_description = GridView1.Rows[k].Cells[4].Text;
string base_unit_of_measure = GridView1.Rows[k].Cells[5].Text;
string batch = GridView1.Rows[k].Cells[6].Text;
string unrestricted = GridView1.Rows[k].Cells[7].Text;
string in_quality_insp = GridView1.Rows[k].Cells[8].Text;
string blocked = GridView1.Rows[k].Cells[9].Text;
string total_stock = GridView1.Rows[k].Cells[10].Text;
string market = GridView1.Rows[k].Cells[11].Text;
string week = GridView1.Rows[k].Cells[12].Text;
string year = GridView1.Rows[k].Cells[13].Text;
string warehouse = GridView1.Rows[k].Cells[14].Text;
if (exception.Trim() == " ")
{
exception = string.Empty;
}
if (material.Trim() == " ")
{
material = string.Empty;
}
if (plant.Trim() == " ")
{
plant = string.Empty;
}
if (material_description.Trim() == " ")
{
material_description = string.Empty;
}
if (base_unit_of_measure.Trim() == " ")
{
base_unit_of_measure = string.Empty;
}
if (batch.Trim() == " ")
{
batch = string.Empty;
}
if (market.Trim() == " ")
{
market = string.Empty;
}
if (year.Trim() == " ")
{
year = string.Empty;
}
if (storage_location != string.Empty)
{
SqlCommand cmd = new SqlCommand("insert into detail_cs(exception,material,plant,storage_location,material_description,base_unit_of_measure,batch,unrestricted,in_quality,blocked,total_stock,market,week,year,warehouse) values(@Exception,@Material,@Plant,@Storage_Location,@Material_Description,@Base_Unit_Of_Measure,@Batch,@Unrestricted,@In_Quality,@Blocked,@Total_Stock,@Market,@Week,@Year,@Warehouse)", conn_sql);
cmd.Parameters.AddWithValue("@Exception", exception);
cmd.Parameters.AddWithValue("@Material", material);
cmd.Parameters.AddWithValue("@Plant", plant);
cmd.Parameters.AddWithValue("@Storage_Location", storage_location);
cmd.Parameters.AddWithValue("@Material_Description", material_description);
cmd.Parameters.AddWithValue("@Base_Unit_Of_Measure", base_unit_of_measure);
cmd.Parameters.AddWithValue("@Batch", batch);
cmd.Parameters.AddWithValue("@Unrestricted", Convert.ToInt32(unrestricted));
cmd.Parameters.AddWithValue("@In_Quality", Convert.ToInt32(in_quality_insp));
cmd.Parameters.AddWithValue("@Blocked", Convert.ToInt32(blocked));
cmd.Parameters.AddWithValue("@Total_Stock", Convert.ToInt32(total_stock));
cmd.Parameters.AddWithValue("@Market", market);
cmd.Parameters.AddWithValue("@Week", Convert.ToInt32(week));
cmd.Parameters.AddWithValue("@Year", year);
cmd.Parameters.AddWithValue("@Warehouse", warehouse);
cmd.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
Label1.Text += ex.Message;
};
if (ExcelDataSet.Tables.Count == 0)
{
lblMessage.Text += "Detail_CS Failed!!";
}
else
{
lblMessage.Text += "Detail_CS Success!!";
}
con.Close();
}
}
catch (Exception ex)
{
lblMessage.Text = ex.Message;
}
}
}
private void Import_To_Grid(string FilePath, string Extension, string isHDR)
{
string conStr = "";
switch (Extension)
{
case ".xls": //Excel 97-03
conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
break;
case ".xlsx": //Excel 07
conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
break;
}
conStr = String.Format(conStr, FilePath, isHDR);
OleDbConnection connExcel = new OleDbConnection(conStr);
OleDbCommand cmdExcel = new OleDbCommand();
OleDbDataAdapter oda = new OleDbDataAdapter();
DataTable dt = new DataTable();
cmdExcel.Connection = connExcel;
//Get the name of First Sheet
connExcel.Open();
DataTable dtExcelSchema;
dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
connExcel.Close();
//Read Data from First Sheet
connExcel.Open();
cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
oda.SelectCommand = cmdExcel;
oda.Fill(dt);
connExcel.Close();
//Bind Data to GridView
GridView1.Caption = Path.GetFileName(FilePath);
GridView1.DataSource = dt;
GridView1.DataBind();
}
public void ReadExcelContents(string fileName)
{
try
{
OleDbConnection connection = new OleDbConnection();
if (System.IO.Path.GetExtension(fileName) == ".xlsx")
{
connection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=\"Excel 8.0;HDR=No;IMEX=1\";Data Source=" + fileName);
}
else
{
connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"Excel 8.0;HDR=No;IMEX=1\";Data Source=" + fileName);
}
// connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"Excel 8.0;HDR=No;IMEX=1\";Data Source=" + fileName); //Excel 97-2003, .xls //string excelQuery = @"Select [Day],[Outlook],[temp],[Humidity],[Wind], [PlaySport] // FROM [Sheet1$]";
connection.Open();
//get list sheet
DataTable dt_sheet = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
String[] excelSheets = new String[dt_sheet.Rows.Count];
int i = 0;
// Add the sheet name to the string array.
foreach (DataRow row in dt_sheet.Rows)
{
excelSheets[i] = row["TABLE_NAME"].ToString();
i++;
}
//-------------
string excelQuery = @"Select * FROM [" + excelSheets[0] + "]";
OleDbCommand cmd = new OleDbCommand(excelQuery, connection);
OleDbDataAdapter adapter = new OleDbDataAdapter();
adapter.SelectCommand = cmd;
DataSet ds = new DataSet();
adapter.Fill(ds);
ds_excel = ds;
Session["ds_excel"] = ds_excel;
if (!DataHelper.DataSourceIsEmpty(ds_excel))
{
Literal1.Text = (ds_excel.Tables[0].Rows.Count - 1).ToString() + " rows <br>";
/*
* ds_excel_column = new DataTable();
*
* DataColumn colShowName = new DataColumn("column_names");
* colShowName.DataType = System.Type.GetType("System.String");//System.Int32
* ds_excel_column.Columns.Add(colShowName);
*
*
* DataRow r = null;
* r = ds_excel_column.NewRow();
* r["column_names"] = "Null";
* ds_excel_column.Rows.Add(r);
* foreach (DataColumn column in ds_excel.Tables[0].Columns)
* {
* //Literal1.Text += column.ColumnName;
* r = ds_excel_column.NewRow();
* r["column_names"] = column.ColumnName;
* ds_excel_column.Rows.Add(r);
* }
* Session["ds_excel_column"] = ds_excel_column;
* */
}
connection.Close();
}
catch (Exception ex)
{
Literal1.Text = ex.Message;
}
}
public AccessToSQLite()
{
old_Access = new OleDbConnection(ConnectionString);
}
public int connectEdit(Student x, string editchoice, string editdata)
{
OleDbConnection connection = new OleDbConnection();
try
{
connection.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccessFile.accdb;
Jet OLEDB:Database Password=MyDbPassword; Data Source=C:\Users\franc_000\Documents\College\StudentRegfinal\StudentReg\studentregdb.accdb; Persist Security Info = false; ";
//var cmd = new OleDbCommand("UPDATE Student SET "+"@editchoice"+" = '"+"@entrydata"+"' WHERE "+"@a"+" = @searchdata;"); //need correct reference
var cmd = new OleDbCommand("UPDATE Student SET FirstName = 'bananak' WHERE Id = '123456';"); //need correct reference
connection.Open();
cmd.Parameters.AddRange(new[] {
new OleDbParameter("@entrytype", editchoice),
new OleDbParameter("@entrydata", x.Id),
new OleDbParameter("@a", "Id"),
new OleDbParameter("@searchdata", x.Id)
});
cmd.Connection = connection;
Console.WriteLine(editchoice);
Console.WriteLine(editdata);
Console.WriteLine(x.Id);
cmd.ExecuteNonQuery();
Console.WriteLine("Student edited successfully.");
}
catch(Exception ex)
{
Console.WriteLine("Error " + ex);
}
connection.Close();
return 0;
}
public FuncStatus DataBind(BindOperator _bo)
{
bool _tf = true;
OleDbConnection _conn = new OleDbConnection(Prument.Properties.Settings.Default.Properties["dataConnectionString"].DefaultValue.ToString());
OleDbCommand _cmd = new OleDbCommand();
OleDbDataReader _dr;
_cmd.Connection = _conn;
try
{
_conn.Open();
if (_bo == BindOperator.Select)
{
_cmd.CommandText = "select Products.ID,Products.Name,Products.Description from Products where " + (this.ID != 0 ? "ID=" + this.ID.ToString() : (this.Name != null ? "Name='" + this.Name : "Description like '_" + this.Description + "_") + "'");
}
if (_bo == BindOperator.Insert)
{
_cmd.CommandText = "insert into Products (Name,Description) values ('" + this.Name + "','" + this.Description + "')";
_cmd.ExecuteScalar();
_cmd.CommandText = "select ID,Name,Description from products where id = (select Max(ID) from Products)";
}
if (_bo == BindOperator.Update) {
_cmd.CommandText = "update products set Name='" + this.Name + "',Description='" + this.Description + "' where ID=" +this.ID;
_cmd.ExecuteNonQuery();
_cmd.CommandText = "select ID,Name,Description from products where id = " + this.ID;
}
if (_bo == BindOperator.Delete)
{
_cmd.CommandText = "select ID,Name,Description from products where id = " + this.ID;
}
_dr = _cmd.ExecuteReader();
_dr.Read();
this.ID = _dr.GetInt32(0);
this.Name = _dr.GetValue(1).ToString();
this.Description = _dr.GetValue(2).ToString();
this.Images.pID = this.ID;
_dr.Close();
if (_bo == BindOperator.Select) {
Images.DataBind(_bo);
}
if (_bo == BindOperator.Insert) {
Images.DataBind(_bo);
}
if (_bo == BindOperator.Update && this.Images.Path[0] != "") {
Images.DataBind(_bo);
}
if (_bo == BindOperator.Delete)
{
_cmd.CommandText = "delete from products where ID=" + this.ID;
_cmd.ExecuteNonQuery();
this.Images.clear();
this.Images.DataBind(BindOperator.Update);
}
this.Status = ORMStatus.Saved;
}
catch (Exception ex)
{
_tf = false;
Exception _ex = new Exception("ORM.Products["+this.ID+"]:" + ex.Message);
throw _ex;
}
finally {
_conn.Close();
}
return _tf == false?FuncStatus.Fail:FuncStatus.Success;
}
public object GetObjectVO(string pstrCode)
{
const string METHOD_NAME = THIS + ".GetObjectVO()";
DataSet dstPCS = new DataSet();
OleDbDataReader odrPCS = null;
OleDbConnection oconPCS = null;
OleDbCommand ocmdPCS = null;
try
{
string strSql = String.Empty;
strSql = "SELECT "
+ CST_ActCostAllocationMasterTable.ACTCOSTALLOCATIONMASTERID_FLD + ","
+ CST_ActCostAllocationMasterTable.PERIODNAME_FLD + ","
+ CST_ActCostAllocationMasterTable.FROMDATE_FLD + ","
+ CST_ActCostAllocationMasterTable.TODATE_FLD + ","
+ CST_ActCostAllocationMasterTable.CCNID_FLD + ","
+ CST_ActCostAllocationMasterTable.CURRENCYID_FLD + ","
+ CST_ActCostAllocationMasterTable.ROLLUPDATE_FLD
+ " FROM " + CST_ActCostAllocationMasterTable.TABLE_NAME
+ " WHERE " + CST_ActCostAllocationMasterTable.PERIODNAME_FLD + "='" + pstrCode.Replace("'", "''") + "'";
Utils utils = new Utils();
oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
ocmdPCS = new OleDbCommand(strSql, oconPCS);
ocmdPCS.Connection.Open();
odrPCS = ocmdPCS.ExecuteReader();
CST_ActCostAllocationMasterVO objObject = new CST_ActCostAllocationMasterVO();
if (odrPCS.Read())
{
objObject.ActCostAllocationMasterID = int.Parse(odrPCS[CST_ActCostAllocationMasterTable.ACTCOSTALLOCATIONMASTERID_FLD].ToString().Trim());
objObject.PeriodName = odrPCS[CST_ActCostAllocationMasterTable.PERIODNAME_FLD].ToString().Trim();
objObject.FromDate = DateTime.Parse(odrPCS[CST_ActCostAllocationMasterTable.FROMDATE_FLD].ToString().Trim());
objObject.ToDate = DateTime.Parse(odrPCS[CST_ActCostAllocationMasterTable.TODATE_FLD].ToString().Trim());
objObject.CCNID = int.Parse(odrPCS[CST_ActCostAllocationMasterTable.CCNID_FLD].ToString().Trim());
objObject.CurrencyID = int.Parse(odrPCS[CST_ActCostAllocationMasterTable.CURRENCYID_FLD].ToString().Trim());
if (!odrPCS[CST_ActCostAllocationMasterTable.ROLLUPDATE_FLD].Equals(DBNull.Value))
{
objObject.RollupDate = DateTime.Parse(odrPCS[CST_ActCostAllocationMasterTable.ROLLUPDATE_FLD].ToString().Trim());
}
else
{
objObject.RollupDate = DateTime.MinValue;
}
return(objObject);
}
return(null);
}
catch (OleDbException ex)
{
throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
}
catch (Exception ex)
{
throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
}
finally
{
if (oconPCS != null)
{
if (oconPCS.State != ConnectionState.Closed)
{
oconPCS.Close();
}
}
}
}
public static List<T> GetList<T>(string strSql, Dal.Models.PageInfo<T> pageInfo, OleDbConnection conn, OleDbTransaction tran = null)
where T : Dal.Models.BaseEntity, new()
{
OleDbCommand cmd = new OleDbCommand("[dbo].[Proc_Pagination]", conn);
cmd.CommandType = CommandType.StoredProcedure;
if (tran != null)
{
cmd.Transaction = tran;
}
cmd.Parameters.Add(new OleDbParameter("@Sql", OleDbType.VarChar) { Value = strSql, Direction = ParameterDirection.Input });
cmd.Parameters.Add(new OleDbParameter("@PerPage", OleDbType.Integer) { Value = pageInfo.PerPage, Direction = ParameterDirection.Input });
cmd.Parameters.Add(new OleDbParameter("@CuttentPage", OleDbType.Integer) { Value = pageInfo.CuttentPage, Direction = ParameterDirection.InputOutput });
cmd.Parameters.Add(new OleDbParameter("@RecordNumber", OleDbType.Integer) { Direction = ParameterDirection.Output });
cmd.Parameters.Add(new OleDbParameter("@PagesCount", OleDbType.Integer) { Direction = ParameterDirection.Output });
IDataReader reader = cmd.ExecuteReader();
List<T> lst = new List<T>();
if (reader != null)
{
reader.Read();
if (reader.NextResult())
{
while (reader.Read())
{
T t = new T();
t.Load<T>(reader);
lst.Add(t);
}
}
reader.Close();
pageInfo.CuttentPage = (int)cmd.Parameters["@CuttentPage"].Value;
pageInfo.RecordNumber = (int)cmd.Parameters["@RecordNumber"].Value;
pageInfo.PagesCount = (int)cmd.Parameters["@PagesCount"].Value;
}
return lst;
}
//public Access(string fileName) : base(string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};", fileName)) { }
/// <summary>新实例</summary>
public Access(OleDbConnection connection) : base(connection)
{
}
protected void Page_Load(object sender, EventArgs e)
{
string sFilePath = Server.MapPath(@"App_Data\SMLiiga.mdb");
string conString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sFilePath;
OleDbConnection Conn = new OleDbConnection(conString);
dt = new DataTable();
dt.TableName = "taabeli";
using (Conn)
{
Conn.Open();
OleDbCommand cmd = new OleDbCommand("SELECT * FROM Pisteet", Conn);
OleDbDataAdapter oDA = new OleDbDataAdapter(cmd);
oDA.Fill(dt);
}
DataView dv = new DataView();
dv.Table = dt;
dv.Sort = "sukunimi ASC";
GridView1.DataSource = dv;
GridView1.DataBind();
if (!Page.IsPostBack)
{
List <String> pelipaikat = new List <String>();
pelipaikat.Add("");
pelipaikat.Add("P");
pelipaikat.Add("H");
pelipaikat.Add("M");
listPosition.DataSource = pelipaikat;
listPosition.DataBind();
listPaikka.DataSource = pelipaikat;
listPaikka.DataBind();
List <String> joukkueet = new List <String>();
joukkueet.Add("");
joukkueet.Add("Kärpät");
joukkueet.Add("Tappara");
joukkueet.Add("Lukko");
joukkueet.Add("TPS");
joukkueet.Add("HIFK");
joukkueet.Add("Blues");
joukkueet.Add("HPK");
joukkueet.Add("Ilves");
joukkueet.Add("Kalpa");
joukkueet.Add("JYP");
joukkueet.Add("Ässät");
joukkueet.Add("Pelicans");
joukkueet.Add("SaiPa");
listTeam.DataSource = joukkueet;
listTeam.DataBind();
listSeura.DataSource = joukkueet;
listSeura.DataBind();
}
}
/// <summary>
/// Initialize variables and sets the connectionstring and select query string.
/// </summary>
/// <param name="connString">Connection String.</param>
/// <param name="sqlSelectString">Query String.</param>
public OleDbRecordsetClass(String connString, String sqlSelectString)
{
this.dbConnection = new OleDbConnection(connString);
this.SqlQuery = sqlSelectString;
this.operationFinished = false;
this.dbAdapter = CreateAdapter(SqlQuery, dbConnection);
this.dbDataSet = new DataSet();
this.dbTable = new DataTable();
this.dbTable = dbDataSet.Tables.Add("Table");
this.dbAdapter.Fill(this.dbTable);
CheckUpdateCommandsFromMetaData();
this.operationFinished = true;
this.newRow = false;
this.newRowChanged = false;
this.foundRecordsCounter = 0;
this.OnAfterQuery();
classFields = new FieldsClass(this);
SetInitialPosition();
}
/// <summary>
/// Initialize variables and sets the connection string.
/// </summary>
/// <param name="connString">string</param>
public OleDbRecordsetClass(String connString)
{
dbConnection = new OleDbConnection(connString);
dbAdapter = new OleDbDataAdapter();
dbDataSet = new DataSet();
newRow = false;
newRowChanged = false;
foundRecordsCounter = 0;
classFields = new FieldsClass(this);
SetInitialPosition();
}
// This function saves the personnel data
public static bool SavePersonnel(string Database, string FirstName, string LastName,
string PayRate, string StartDate, string EndDate)
{
bool recordSaved;
/*try
* {
* // Instantiation of OleDbConnection 'conn' object to establish connection to database
* OleDbConnection conn = new OleDbConnection("PROVIDER=Microsoft.ACE.OLEDB.12.0;" +
* "Data Source=" + Database);
* conn.Open();
* OleDbCommand command = conn.CreateCommand();
* string strSQL;
* // 'strSQL variable allows users to input data into the application
* strSQL = "Insert into tblPersonnel " +
* "(FirstName, LastName) values ('" +
* FirstName + "', '" + LastName + "')";
* // Turns data entered in the textbox into appropriate data form
* command.CommandType = CommandType.Text;
* command.CommandText = strSQL;
* // Allows an action to take place based on data entered into database
* command.ExecuteNonQuery();
* strSQL = "Update tblPersonnel " +
* "Set PayRate=" + PayRate + ", " +
* "StartDate='" + StartDate + "', " +
* "EndDate='" + EndDate + "' " +
* "Where ID=(Select Max(ID) From tblPersonnel)";
* // Turns data entered in the textbox into appropriate data form
* command.CommandType = CommandType.Text;
* command.CommandText = strSQL;
* // Allows an action to take place based on data entered into database
* command.ExecuteNonQuery();
*
* // Closes the connection to the database
* conn.Close();
* recordSaved = true;*/
// ** NEW ** declaration of myTransaction variable of OleDbTransaction to an assignment of 'null'
OleDbTransaction myTransaction = null;
try
{
// instantiatiion of OleDbConnection 'conn'
OleDbConnection conn = new OleDbConnection("PROVIDER=Microsoft.ACE.OLEDB.12.0;" +
"Data Source=" + Database);
conn.Open();
OleDbCommand command = conn.CreateCommand();
string strSQL;
// ** NEW ** Using dot notation to use the OleDbConection to begin database transaction
myTransaction = conn.BeginTransaction();
command.Transaction = myTransaction;
// 'strSQL' variable allows users to input data into the application
strSQL = "Insert into tblPersonnel " +
"(FirstName, LastName) values ('" +
FirstName + "', '" + LastName + "')";
// Turns data entered in the textbox into appropriate data form
command.CommandType = CommandType.Text;
command.CommandText = strSQL;
// Allows an action to take place based on data entered into database
command.ExecuteNonQuery();
// 'strSQL' variable allows users to input data into the application
strSQL = "Update tblPersonnel " +
"Set PayRate=" + PayRate + ", " +
"StartDate='" + StartDate + "', " +
"EndDate='" + EndDate + "' " +
"Where ID=(Select Max(ID) From tblPersonnel)";
// Turns data entered in the textbox into appropriate data form
command.CommandType = CommandType.Text;
command.CommandText = strSQL;
// Allows an action to take place based on data entered into database
command.ExecuteNonQuery();
// ** NEW ** Permanently adds entered data into the database
myTransaction.Commit();
// Terminates the connection to the database through instantiated variable
conn.Close();
recordSaved = true;
}
catch (Exception ex)
{
// ** NEW ** If any unhandled exception occurrs, delete any information entered
myTransaction.Rollback();
recordSaved = false;
}
return(recordSaved);
}
protected void Button2_Click(object sender, EventArgs e)
{
float ID;
String Case_ID;
String Created_Date;
String Created_By;
String Title;
String Case_Status;
String Case_Type;
String Service_Type;
String Case_Category;
String Case_Sub_Category;
String Engineer;
String Team;
String Customer;
String Region;
String Site;
String Contact;
String Channel;
String Priority;
String Response_Overdue;
String Onsite_Overdue;
String Resolve_Overdue;
String Close_Overdue;
String Response_Duration;
String Onsite_Duration;
String Resolve_Duration;
String Close_Duration;
float Case_Duration;
String Response;
String Onsite;
String Resolve;
String Auto_Close;
String SLA;
String Resolved_Time;
float Hour_to_Resolve;
float Hour_to_Resolve_Pending;
String Closed_Time;
float Hour_to_Closed;
float Hour_to_Closed_Pending;
String Root_Cause;
String Resolved_Method;
float New_to_response;
float New_to_Assign;
String Latest_Resolve_to_Close;
float Latest_Response_to_Close;
float Agent_UTL_Time;
float Eng_UTL_Time;
string path = Path.GetFileName(FileUpload2.FileName);
path = path.Replace(" ", "");
FileUpload2.SaveAs(Server.MapPath("~/ExcelFile2/") + path);
String ExcelPath = Server.MapPath("~/ExcelFile2/") + path;
OleDbConnection mycon = new OleDbConnection("Provider = Microsoft.ACE.OLEDB.12.0; Data Source = " + ExcelPath + "; Extended Properties=Excel 8.0; Persist Security Info = False");
mycon.Open();
OleDbCommand cmd = new OleDbCommand("select * from [Sheet1$]", mycon);
OleDbDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
ID = Convert.ToInt32(dr[0].ToString());
Case_ID = dr[1].ToString();
Created_Date = dr[2].ToString();
Created_By = dr[3].ToString();
Title = dr[4].ToString();
Case_Status = dr[5].ToString();
Case_Type = dr[6].ToString();
Service_Type = dr[7].ToString();
Case_Category = dr[8].ToString();
Case_Sub_Category = dr[9].ToString();
Engineer = dr[10].ToString();
Team = dr[11].ToString();
Customer = dr[12].ToString();
Region = dr[13].ToString();
Site = dr[14].ToString();
Contact = dr[15].ToString();
Channel = dr[16].ToString();
Priority = dr[17].ToString();
Response_Overdue = dr[18].ToString();
Onsite_Overdue = dr[19].ToString();
Resolve_Overdue = dr[20].ToString();
Close_Overdue = dr[21].ToString();
Response_Duration = dr[22].ToString();
Onsite_Duration = dr[23].ToString();
Resolve_Duration = dr[24].ToString();
Close_Duration = dr[25].ToString();
Case_Duration = Convert.ToInt32(dr[26].ToString());
Response = dr[27].ToString();
Onsite = dr[28].ToString();
Resolve = dr[29].ToString();
Auto_Close = dr[30].ToString();
SLA = dr[31].ToString();
Resolved_Time = dr[32].ToString();
Hour_to_Resolve = Convert.ToInt32(dr[33].ToString());
Hour_to_Resolve_Pending = Convert.ToInt32(dr[34].ToString());
Closed_Time = dr[35].ToString();
Hour_to_Closed = Convert.ToInt32(dr[36].ToString());
Hour_to_Closed_Pending = Convert.ToInt32(dr[37].ToString());
Root_Cause = dr[38].ToString();
Resolved_Method = dr[39].ToString();
New_to_response = Convert.ToInt32(dr[40].ToString());
New_to_Assign = Convert.ToInt32(dr[41].ToString());
Latest_Resolve_to_Close = dr[42].ToString();
Latest_Response_to_Close = Convert.ToInt32(dr[43].ToString());
Agent_UTL_Time = Convert.ToInt32(dr[44].ToString());
Eng_UTL_Time = Convert.ToInt32(dr[45].ToString());
savedata(ID, Case_ID, Created_Date, Created_By, Title, Case_Status, Case_Type, Service_Type, Case_Category, Case_Sub_Category, Engineer, Team
, Customer, Region, Site, Contact, Channel, Priority, Response_Overdue, Onsite_Overdue, Resolve_Overdue, Close_Overdue, Response_Duration
, Onsite_Duration, Resolve_Duration, Close_Duration, Case_Duration, Response, Onsite, Resolve, Auto_Close, SLA, Resolved_Time, Hour_to_Resolve
, Hour_to_Resolve_Pending, Closed_Time, Hour_to_Closed, Hour_to_Closed_Pending, Root_Cause, Resolved_Method, New_to_response, New_to_Assign
, Latest_Resolve_to_Close, Latest_Response_to_Close, Agent_UTL_Time, Eng_UTL_Time);
}
Label2.Text = "Data Has Been Saved Successfully";
}
//**************************************************************************
/// <Description>
/// This method uses to add data to CST_ActCostAllocationMaster
/// </Description>
/// <Inputs>
/// CST_ActCostAllocationMasterVO
/// </Inputs>
/// <Outputs>
/// newly inserted primarkey value
/// </Outputs>
/// <Returns>
/// void
/// </Returns>
/// <Authors>
/// HungLa
/// </Authors>
/// <History>
/// Tuesday, February 21, 2006
/// </History>
/// <Notes>
/// </Notes>
//**************************************************************************
public void Add(object pobjObjectVO)
{
const string METHOD_NAME = THIS + ".Add()";
OleDbConnection oconPCS = null;
OleDbCommand ocmdPCS = null;
try
{
CST_ActCostAllocationMasterVO objObject = (CST_ActCostAllocationMasterVO)pobjObjectVO;
string strSql = String.Empty;
Utils utils = new Utils();
oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
ocmdPCS = new OleDbCommand("", oconPCS);
strSql = "INSERT INTO CST_ActCostAllocationMaster("
+ CST_ActCostAllocationMasterTable.PERIODNAME_FLD + ","
+ CST_ActCostAllocationMasterTable.FROMDATE_FLD + ","
+ CST_ActCostAllocationMasterTable.TODATE_FLD + ","
+ CST_ActCostAllocationMasterTable.CCNID_FLD + ","
+ CST_ActCostAllocationMasterTable.CURRENCYID_FLD + ","
+ CST_ActCostAllocationMasterTable.ROLLUPDATE_FLD + ")"
+ "VALUES(?,?,?,?,?,?)";
ocmdPCS.Parameters.Add(new OleDbParameter(CST_ActCostAllocationMasterTable.PERIODNAME_FLD, OleDbType.WChar));
ocmdPCS.Parameters[CST_ActCostAllocationMasterTable.PERIODNAME_FLD].Value = objObject.PeriodName;
ocmdPCS.Parameters.Add(new OleDbParameter(CST_ActCostAllocationMasterTable.FROMDATE_FLD, OleDbType.Date));
ocmdPCS.Parameters[CST_ActCostAllocationMasterTable.FROMDATE_FLD].Value = objObject.FromDate;
ocmdPCS.Parameters.Add(new OleDbParameter(CST_ActCostAllocationMasterTable.TODATE_FLD, OleDbType.Date));
ocmdPCS.Parameters[CST_ActCostAllocationMasterTable.TODATE_FLD].Value = objObject.ToDate;
ocmdPCS.Parameters.Add(new OleDbParameter(CST_ActCostAllocationMasterTable.CCNID_FLD, OleDbType.Integer));
ocmdPCS.Parameters[CST_ActCostAllocationMasterTable.CCNID_FLD].Value = objObject.CCNID;
ocmdPCS.Parameters.Add(new OleDbParameter(CST_ActCostAllocationMasterTable.CURRENCYID_FLD, OleDbType.Integer));
ocmdPCS.Parameters[CST_ActCostAllocationMasterTable.CURRENCYID_FLD].Value = objObject.CurrencyID;
ocmdPCS.Parameters.Add(new OleDbParameter(CST_ActCostAllocationMasterTable.ROLLUPDATE_FLD, OleDbType.Date));
ocmdPCS.Parameters[CST_ActCostAllocationMasterTable.ROLLUPDATE_FLD].Value = objObject.RollupDate;
ocmdPCS.CommandText = strSql;
ocmdPCS.Connection.Open();
ocmdPCS.ExecuteNonQuery();
}
catch (OleDbException ex)
{
if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
{
throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);
}
else
{
throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
}
}
catch (InvalidOperationException ex)
{
throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
}
catch (Exception ex)
{
throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
}
finally
{
if (oconPCS != null)
{
if (oconPCS.State != ConnectionState.Closed)
{
oconPCS.Close();
}
}
}
}
//**************************************************************************
/// <Description>
/// This method uses to update data to CST_ActCostAllocationMaster
/// </Description>
/// <Inputs>
/// CST_ActCostAllocationMasterVO
/// </Inputs>
/// <Outputs>
///
/// </Outputs>
/// <Returns>
///
/// </Returns>
/// <Authors>
/// HungLa
/// </Authors>
/// <History>
/// 09-Dec-2004
/// </History>
/// <Notes>
/// </Notes>
//**************************************************************************
public void Update(object pobjObjecVO)
{
const string METHOD_NAME = THIS + ".Update()";
CST_ActCostAllocationMasterVO objObject = (CST_ActCostAllocationMasterVO)pobjObjecVO;
//prepare value for parameters
OleDbConnection oconPCS = null;
OleDbCommand ocmdPCS = null;
try
{
string strSql = String.Empty;
Utils utils = new Utils();
oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
ocmdPCS = new OleDbCommand(strSql, oconPCS);
strSql = "UPDATE CST_ActCostAllocationMaster SET "
+ CST_ActCostAllocationMasterTable.PERIODNAME_FLD + "= ?" + ","
+ CST_ActCostAllocationMasterTable.FROMDATE_FLD + "= ?" + ","
+ CST_ActCostAllocationMasterTable.TODATE_FLD + "= ?" + ","
+ CST_ActCostAllocationMasterTable.CCNID_FLD + "= ?" + ","
+ CST_ActCostAllocationMasterTable.CURRENCYID_FLD + "= ?" + ","
+ CST_ActCostAllocationMasterTable.ROLLUPDATE_FLD + "= ?"
+ " WHERE " + CST_ActCostAllocationMasterTable.ACTCOSTALLOCATIONMASTERID_FLD + "= ?";
ocmdPCS.Parameters.Add(new OleDbParameter(CST_ActCostAllocationMasterTable.PERIODNAME_FLD, OleDbType.WChar));
ocmdPCS.Parameters[CST_ActCostAllocationMasterTable.PERIODNAME_FLD].Value = objObject.PeriodName;
ocmdPCS.Parameters.Add(new OleDbParameter(CST_ActCostAllocationMasterTable.FROMDATE_FLD, OleDbType.Date));
ocmdPCS.Parameters[CST_ActCostAllocationMasterTable.FROMDATE_FLD].Value = objObject.FromDate;
ocmdPCS.Parameters.Add(new OleDbParameter(CST_ActCostAllocationMasterTable.TODATE_FLD, OleDbType.Date));
ocmdPCS.Parameters[CST_ActCostAllocationMasterTable.TODATE_FLD].Value = objObject.ToDate;
ocmdPCS.Parameters.Add(new OleDbParameter(CST_ActCostAllocationMasterTable.CCNID_FLD, OleDbType.Integer));
ocmdPCS.Parameters[CST_ActCostAllocationMasterTable.CCNID_FLD].Value = objObject.CCNID;
ocmdPCS.Parameters.Add(new OleDbParameter(CST_ActCostAllocationMasterTable.CURRENCYID_FLD, OleDbType.Integer));
ocmdPCS.Parameters[CST_ActCostAllocationMasterTable.CURRENCYID_FLD].Value = objObject.CurrencyID;
ocmdPCS.Parameters.Add(new OleDbParameter(CST_ActCostAllocationMasterTable.ROLLUPDATE_FLD, OleDbType.Date));
if (objObject.RollupDate != DateTime.MinValue)
{
ocmdPCS.Parameters[CST_ActCostAllocationMasterTable.ROLLUPDATE_FLD].Value = objObject.RollupDate;
}
else
{
ocmdPCS.Parameters[CST_ActCostAllocationMasterTable.ROLLUPDATE_FLD].Value = DBNull.Value;
}
ocmdPCS.Parameters.Add(new OleDbParameter(CST_ActCostAllocationMasterTable.ACTCOSTALLOCATIONMASTERID_FLD, OleDbType.Integer));
ocmdPCS.Parameters[CST_ActCostAllocationMasterTable.ACTCOSTALLOCATIONMASTERID_FLD].Value = objObject.ActCostAllocationMasterID;
ocmdPCS.CommandText = strSql;
ocmdPCS.Connection.Open();
ocmdPCS.ExecuteNonQuery();
}
catch (OleDbException ex)
{
if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
{
throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);
}
else
{
throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
}
}
catch (InvalidOperationException ex)
{
throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
}
catch (Exception ex)
{
throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
}
finally
{
if (oconPCS != null)
{
if (oconPCS.State != ConnectionState.Closed)
{
oconPCS.Close();
}
}
}
}
/// <summary>
/// Read the table from specified dBASE (DBF) file and
/// merge the rows with shapefile records.
/// </summary>
/// <remarks>
/// The filename of the dBASE file is expected to follow 8.3 naming
/// conventions. If it doesn't follow the convention, we try to
/// determine the 8.3 short name ourselves (but the name we come up
/// with may not be correct in all cases).
/// </remarks>
/// <param name="dbaseFile">Full file path of the dBASE (DBF) file.</param>
public void ReadAttributes(string dbaseFile)
{
if (string.IsNullOrEmpty(dbaseFile))
{
throw new ArgumentNullException("dbaseFile");
}
// Check if the file exists. If it doesn't exist,
// this is not an error.
if (!File.Exists(dbaseFile))
{
return;
}
// Get the directory in which the dBASE (DBF) file resides.
FileInfo fi = new FileInfo(dbaseFile);
string directory = fi.DirectoryName;
// Get the filename minus the extension.
string fileNameNoExt = fi.Name.ToUpper(System.Globalization.CultureInfo.InvariantCulture);
if (fileNameNoExt.EndsWith(".DBF"))
{
fileNameNoExt = fileNameNoExt.Substring(0, fileNameNoExt.Length - 4);
}
// Convert to a short filename (may not work in every case!).
if (fileNameNoExt.Length > 8)
{
if (fileNameNoExt.Contains(" "))
{
string noSpaces = fileNameNoExt.Replace(" ", "");
if (noSpaces.Length > 8)
{
fileNameNoExt = noSpaces;
}
}
fileNameNoExt = fileNameNoExt.Substring(0, 6) + "~1";
}
// Set the connection string.
string connectionString;
switch (Environment.MachineName)
{
case "SLU002758": //
connectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" + directory + ";Extended Properties=dBASE 5.0";
break;
default:
connectionString = "PROVIDER=Microsoft.ACE.OLEDB.12.0;Data Source=" + directory + ";Extended Properties=dBASE 5.0";
break;
}
// Set the select query.
string selectQuery = "SELECT * FROM [" + fileNameNoExt + "#DBF];";
// Create a database connection object using the connection string.
OleDbConnection connection = new OleDbConnection(connectionString);
// Create a database command on the connection using the select query.
OleDbCommand command = new OleDbCommand(selectQuery, connection);
try
{
// Open the connection.
connection.Open();
// Create a data adapter to fill a dataset.
OleDbDataAdapter dataAdapter = new OleDbDataAdapter();
dataAdapter.SelectCommand = command;
DataSet dataSet = new DataSet();
dataSet.Locale = System.Globalization.CultureInfo.InvariantCulture;
dataAdapter.Fill(dataSet);
// Merge attributes into the shape file.
if (dataSet.Tables.Count > 0)
{
this.MergeAttributes(dataSet.Tables[0]);
}
}
catch (OleDbException)
{
// Note: An exception will occur if the filename of the dBASE
// file does not follow 8.3 naming conventions. In this case,
// you must use its short (MS-DOS) filename.
// Rethrow the exception.
throw;
}
finally
{
// Dispose of connection.
((IDisposable)connection).Dispose();
}
}
public int connectRemove(Student x, string idremove)
{
OleDbConnection connection = new OleDbConnection();
try
{
connection.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccessFile.accdb;
Jet OLEDB:Database Password=MyDbPassword; Data Source=C:\Users\franc_000\Documents\College\StudentRegfinal\StudentReg\studentregdb.accdb; Persist Security Info = false; ";
var cmd = new OleDbCommand("DELETE FROM Student WHERE Id = @a;"); //need correct reference
connection.Open();
cmd.Parameters.AddRange(new[] {
new OleDbParameter("@a", x.Id)
});
cmd.Connection = connection;
cmd.ExecuteNonQuery();
Console.WriteLine("Student removed successfully.");
}
catch(Exception ex)
{
Console.WriteLine("Error " + ex);
}
connection.Close();
return 0;
}
public void LoadRepository()
{
//1. recursive delete \extracted [catching NonExisting exception]
string extractPath = @"..\..\extracted";
if (Directory.Exists(extractPath))
{
Directory.Delete(extractPath, true);
}
//2. create \extracted
Directory.CreateDirectory(extractPath);
//3. extract from sales_reports.zip into extracted
string zipPath = @"..\..\sales_reports.zip";
using (var zip1 = ZipFile.Read(zipPath))
{
foreach (var entry in zip1)
{
entry.Extract(extractPath, ExtractExistingFileAction.OverwriteSilently);
}
}
//4. get \extracted directories
var subfolders = Directory.GetDirectories(extractPath);
//5. foreach the directories
foreach (var dateFolderPath in subfolders)
{
string dateFolder = dateFolderPath.Split('\\').Last();
// parsing the date (parse fail = skip)
DateTime saleDate;
if (!DateTime.TryParse(dateFolder, out saleDate))
{
continue;
}
// get filenames
var saleReportsByDealer = Directory.GetFiles(dateFolderPath);
// foreach the filenames
foreach (var reportPath in saleReportsByDealer)
{
// check if .xls extension (fail = skip)
var fileInfo = new FileInfo(reportPath);
string extension = fileInfo.Extension;
if (extension != ".xls")
{
continue;
}
// parse int for DealerId (fail = skip)
string filename = fileInfo.Name.Split('.').First();
int dealerId;
if (!int.TryParse(filename, out dealerId))
{
continue;
}
// connect via OleDb + pull the rows + close connection
string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + reportPath + ";"
+ "Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";";
OleDbConnection db_Con = new OleDbConnection(connectionString);
#region Connection
db_Con.Open();
using (db_Con)
{
// this code is for discovering sheet names if they are not available
DataTable tables = db_Con.GetSchema("Tables");
var sheetNameList = new List <string>();
for (int i = 0; i < tables.Rows.Count; i++)
{
var name = tables.Rows[i][2].ToString();
sheetNameList.Add(name);
}
foreach (var sheetName in sheetNameList)
{
Sale sale = new Sale()
{
DealerId = dealerId, SaleDate = saleDate
};
this.repo.Add <Sale>(sale);
string sqlString = "select * from [" + sheetName + "];";
OleDbCommand cmdGetRows = new OleDbCommand(sqlString, db_Con);
//cmdGetRows.Parameters.AddWithValue("@sheetName", sheetName);
OleDbDataReader reader = cmdGetRows.ExecuteReader();
using (reader)
{
SaleDetails details;
while (reader.Read())
{
details = this.DetailsRow(this.repo, reader, sale);
if (details != null)
{
this.repo.Add <SaleDetails>(details);
}
}
}
}
}//using db_Con
#endregion
}
}
}
public String ExportContactListToExcel(int userId, int plotId)
{
String sourceFileName = "ContactListTemplate.xls";
String destinationFileName = plotId.ToString() + "_" + "ContactList.xls";
//Build Source directory and Destination directory.
String sourcePath = System.Configuration.ConfigurationManager.AppSettings["ContactFileLocation"];
String destinationPath = sourcePath;
if (!sourcePath.EndsWith("\\"))
{
sourcePath += "\\";
}
sourcePath += sourceFileName;
if (!destinationPath.EndsWith("\\"))
{
destinationPath += "\\";
}
destinationPath += userId;
destinationPath += "\\" + destinationFileName;
//Getting the list of contacts for the Given Plot
List <ContactInfo> contacts = new List <ContactInfo>();
// Get an instance of the MailingCycle BO
Farm bll = new Farm();
contacts = bll.GetContactListForPlot(plotId);
int totalContacts = contacts.Count;
try
{
//Copy file to user Workspace
if (File.Exists(destinationPath))
{
File.Delete(destinationPath);
}
File.Copy(sourcePath, destinationPath);
OleDbDataAdapter adapter = new OleDbDataAdapter();
DataTable schemaTable = new DataTable();
string connectionString = string.Empty;
connectionString = "Data Source=" + destinationPath + ";" +
"Provider=Microsoft.Jet.OLEDB.4.0;" +
"Extended Properties=Excel 8.0;";
OleDbConnection connection = new OleDbConnection();
connection = new OleDbConnection(connectionString);
connection.Open();
//To get the Table name
schemaTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
new object[] { null, null, null, "TABLE" });
string tableName = schemaTable.Rows[0]["Table_Name"].ToString();
schemaTable.Clear();
//Adding Contacts to the Excel file through OleDbCommand Object
for (int i = 0; i < totalContacts; i++)
{
string sqlInsertQuery = "INSERT INTO [" + tableName + "] (";
sqlInsertQuery += "[ScheduleNumber], [OwnerFullName], [Lot],";
sqlInsertQuery += "[Block], [subdivision], [Filing],";
sqlInsertQuery += "[SiteAddress], [BedRooms], [FullBath],";
sqlInsertQuery += " [ThreeQuaterBath], [HalfBath], [Acres],";
sqlInsertQuery += "[AckMktCombo], [OwnerFirstName], [OwerLastName],";
sqlInsertQuery += "[OwnerAddressLine1], [OwnerAddressLine2], [OwnerCity],";
sqlInsertQuery += "[OwnerState], [OwnerZip], [OwnerCountry],";
sqlInsertQuery += "[SaleDate], [TransAmount]) ";
sqlInsertQuery += "VALUES('" + contacts[i].ScheduleNumber.ToString() + "','" + contacts[i].OwnerFullName + "'," + contacts[i].Lot.ToString() + ",";
sqlInsertQuery += "'" + contacts[i].Block + "','" + contacts[i].Subdivision + "','" + contacts[i].Filing + "',";
sqlInsertQuery += "'" + contacts[i].SiteAddress + "'," + contacts[i].Bedrooms.ToString() + "," + contacts[i].FullBath.ToString() + ",";
sqlInsertQuery += "" + contacts[i].ThreeQuarterBath.ToString() + "," + contacts[i].HalfBath.ToString() + "," + contacts[i].Acres.ToString() + ",";
sqlInsertQuery += "'" + contacts[i].ActMktComb + "','" + contacts[i].OwnerFirstName + "','" + contacts[i].OwnerLastName + "',";
sqlInsertQuery += "'" + contacts[i].OwnerAddress1 + "','" + contacts[i].OwnerAddress2 + "','" + contacts[i].OwnerCity + "',";
sqlInsertQuery += "'" + contacts[i].OwnerState + "','" + contacts[i].OwnerZip + "','" + contacts[i].OwnerCountry + "',";
sqlInsertQuery += "'" + contacts[i].SaleDate.ToShortDateString() + "'," + contacts[i].TransAmount.ToString();
sqlInsertQuery += ")";
OleDbCommand InsertCommand = new OleDbCommand(sqlInsertQuery, connection);
InsertCommand.ExecuteNonQuery();
}
connection.Close();
}
catch
{
//if (connection.State = ConnectionState.Open)
//connection.Close();
throw;
}
return(destinationFileName);
}
public start()
{
InitializeComponent();
myConnection = new OleDbConnection(connectString);
myConnection.Open();
}
public ActionResult Import(CompanyProfRecordViewModel viewModel)
{
if (!ModelState.IsValid)
{
return(new HttpStatusCodeResult(HttpStatusCode.BadRequest));
}
if (viewModel.excelFile == null || viewModel.excelFile.ContentLength == 0)
{
//handel error
ViewBag.ErrorMessage = "This field is required";
ChemAnalystContext _context = new ChemAnalystContext();
var viewModelIndex = new CompanyProfRecordViewModel()
{
FinancialYear = _context.FinancialYears.ToList(),
SA_Company = _context.SA_Company.ToList()
};
return(View("Index", viewModelIndex));
}
else
{
ChemAnalystContext _context = new ChemAnalystContext();
string FileExtension = Path.GetExtension(viewModel.excelFile.FileName);
string FileName = Path.GetFileName(viewModel.excelFile.FileName);
FileName = FileName + "_" + Guid.NewGuid().ToString();
if (FileExtension.ToLower() == ".xls")
{
viewModel.excelFile.SaveAs(Server.MapPath("~/ExcelFiles/") + FileName);
string FilePath = Server.MapPath("~/ExcelFiles/" + FileName);
OleDbConnection OleConn = null;
OleConn = new OleDbConnection(@"provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FilePath + ";Extended Properties=Excel 8.0;");
OleConn.Open();
DataTable DT = OleConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string GetExcelSheetName = DT.Rows[0]["Table_Name"].ToString();
OleDbDataAdapter DA = new OleDbDataAdapter("SELECT * FROM [" + GetExcelSheetName + "]", OleConn);
DataSet DS = new DataSet();
DA.Fill(DS);
OleConn.Close();
foreach (DataRow DR in DS.Tables[0].Rows)
{
var yr = DR[0].ToString();
//if (_context.CompanyProfRecordNew.FirstOrDefault(x => x.SA_CompanyId == viewModel.SA_CompanyId && x.FinancialYearId == viewModel.FinancialYearId) == null)
if (_context.CompanyProfRecordNew.FirstOrDefault(x => x.SA_CompanyId == viewModel.SA_CompanyId && x.year == yr) == null)
{
try
{
CompanyProfRecordNew cpr = new CompanyProfRecordNew();
cpr.year = DR[0].ToString();
cpr.Revenue = Convert.ToDecimal(DR[1]);
cpr.Growth = Convert.ToInt16(DR[2]);
cpr.PBT = Convert.ToInt16(DR[3]);
cpr.Margin = Convert.ToDecimal(DR[4]);
cpr.Pat = Convert.ToDecimal(DR[5]);
cpr.Margin1 = Convert.ToDecimal(DR[6]);
cpr.Liablities = Convert.ToDecimal(DR[7]);
cpr.SA_CompanyId = viewModel.SA_CompanyId;
cpr.FinancialYearId = 0;
cpr.CreateDate = DateTime.Now;
_context.CompanyProfRecordNew.Add(cpr);
_context.SaveChanges();
}
catch (Exception ex)
{
ViewBag.ErrorMessage = "Import operation failed. Please upload a valid formate for import.";
var viewModelIndex = new CompanyProfRecordViewModel()
{
FinancialYear = _context.FinancialYears.ToList(),
SA_Company = _context.SA_Company.ToList()
};
return(View("Index", viewModelIndex));
}
}
else
{
ViewBag.ErrorMessage = "Financial data already exists with same company for same financial year";
var viewModelIndex = new CompanyProfRecordViewModel()
{
FinancialYear = _context.FinancialYears.ToList(),
SA_Company = _context.SA_Company.ToList()
};
return(View("Index", viewModelIndex));
}
}
}
else
{
ViewBag.ErrorMessage = "Only .xls file allowed";
var viewModelIndex = new CompanyProfRecordViewModel()
{
FinancialYear = _context.FinancialYears.ToList(),
SA_Company = _context.SA_Company.ToList()
};
return(View("Index", viewModelIndex));
}
return(RedirectToAction("AllCompanyProfileRecords"));
}
}
public ActionResult Index(HttpPostedFileBase file)
{
var students = new List <string> {
};
var email = new List <string> {
};
var nope = new List <string> {
};
DataSet ds = new DataSet();
if (Request.Files["file"].ContentLength > 0)
{
string fileExtension =
System.IO.Path.GetExtension(Request.Files["file"].FileName);
if (fileExtension == ".xls" || fileExtension == ".xlsx")
{
string fileLocation = Server.MapPath("~/Content/") + Request.Files["file"].FileName;
if (System.IO.File.Exists(fileLocation))
{
System.IO.File.Delete(fileLocation);
}
Request.Files["file"].SaveAs(fileLocation);
string excelConnectionString = string.Empty;
excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
//connection String for xls file format.
if (fileExtension == ".xls")
{
excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
}
//connection String for xlsx file format.
else if (fileExtension == ".xlsx")
{
excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
}
//Create Connection to Excel work book and add oledb namespace
OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
excelConnection.Open();
DataTable dt = new DataTable();
dt = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (dt == null)
{
return(null);
}
String[] excelSheets = new String[dt.Rows.Count];
int t = 0;
//excel data saves in temp file here.
foreach (DataRow row in dt.Rows)
{
excelSheets[t] = row["TABLE_NAME"].ToString();
t++;
}
OleDbConnection excelConnection1 = new OleDbConnection(excelConnectionString);
string query = string.Format("Select * from [{0}]", excelSheets[0]);
using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, excelConnection1))
{
dataAdapter.Fill(ds);
}
}
if (fileExtension.ToString().ToLower().Equals(".xml"))
{
string fileLocation = Server.MapPath("~/Content/") + Request.Files["FileUpload"].FileName;
if (System.IO.File.Exists(fileLocation))
{
System.IO.File.Delete(fileLocation);
}
Request.Files["FileUpload"].SaveAs(fileLocation);
XmlTextReader xmlreader = new XmlTextReader(fileLocation);
// DataSet ds = new DataSet();
ds.ReadXml(xmlreader);
xmlreader.Close();
}
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
string conn = ConfigurationManager.ConnectionStrings["dbconnection"].ConnectionString;
SqlConnection con = new SqlConnection(conn);
string query = "Insert into Person(Name,Email,Mobile) Values('" + ds.Tables[0].Rows[i][0].ToString() + "','" + ds.Tables[0].Rows[i][1].ToString() + "','" + ds.Tables[0].Rows[i][2].ToString() + "')";
con.Open();
SqlCommand cmd = new SqlCommand(query, con);
cmd.ExecuteNonQuery();
con.Close();
}
}
return(View());
}
public ICollection <ErrorHandler> BulkInsert(string argExcelPath, string argQuery, string strTableName, string argFileExt, string argUserName, string argClientCode)
{
DataTable dtExcel = null;
string xConnStr = "";
string strSheetName = "";
DataSet dsExcel = new DataSet();
DataTable dtTableSchema = new DataTable();
OleDbConnection objXConn = null;
OleDbDataAdapter objDataAdapter = new OleDbDataAdapter();
List <ErrorHandler> lstErr = new List <ErrorHandler>();
if (argFileExt.ToString() == ".xls")
{
xConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + argExcelPath.Trim() + ";" +
"Extended Properties=Excel 8.0";
}
else
{
xConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;" +
"Data Source=" + argExcelPath.Trim() + ";" +
"Extended Properties=Excel 12.0";
}
try
{
objXConn = new OleDbConnection(xConnStr);
objXConn.Open();
dtTableSchema = objXConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (argFileExt.ToString() == ".xls")
{
strSheetName = Convert.ToString(dtTableSchema.Rows[0]["TABLE_NAME"]);
}
else
{
strSheetName = Convert.ToString(dtTableSchema.Rows[0]["TABLE_NAME"]);
if (strSheetName.IndexOf(@"_xlnm#_FilterDatabase") >= 0)
{
strSheetName = Convert.ToString(dtTableSchema.Rows[1]["TABLE_NAME"]);
}
}
argQuery = argQuery + " [" + strSheetName + "]";
OleDbCommand objCommand = new OleDbCommand(argQuery, objXConn);
objDataAdapter.SelectCommand = objCommand;
objDataAdapter.Fill(dsExcel);
dtExcel = dsExcel.Tables[0];
/*****************************************/
DataAccess da = new DataAccess();
da.Open_Connection();
da.BEGIN_TRANSACTION();
try
{
SaveSalesArea_SalesOffice(colGetSalesArea_SalesOffice(dtExcel, argUserName, argClientCode), lstErr);
foreach (ErrorHandler objerr in lstErr)
{
if (objerr.Type == "E")
{
da.ROLLBACK_TRANSACTION();
break;
}
}
}
catch (Exception ex)
{
if (da != null)
{
da.ROLLBACK_TRANSACTION();
}
objErrorHandler.Type = ErrorConstant.strAboartType;
objErrorHandler.MsgId = 0;
objErrorHandler.Module = ErrorConstant.strInsertModule;
objErrorHandler.ModulePart = ErrorConstant.strMasterModule;
objErrorHandler.Message = ex.Message.ToString();
objErrorHandler.RowNo = 0;
objErrorHandler.FieldName = "";
objErrorHandler.LogCode = "";
lstErr.Add(objErrorHandler);
}
finally
{
if (da != null)
{
da.Close_Connection();
da = null;
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
objXConn.Close();
}
return(lstErr);
}
private static IEnumerable <T> RetrieveFromSearchIndex <T>(OleDbConnection conn,
string author = null, string freetext = null,
DateTime?before = null, DateTime?after = null)
{
var type = typeof(T);
// Fetch properties
var props = type.GetProperties().Select(
p => Tuple.Create(p, p.GetCustomAttr <XmlElementAttribute>())).ToArray();
// Define parameters
var attrs = string.Join(", ", props.Select(p => p.Item2.ElementName));
var folder = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
var conds = new StringBuilder();
if (!string.IsNullOrWhiteSpace(author))
{
conds.Append("and CONTAINS(System.Author, '\"" + author + "\"') ");
}
if (!string.IsNullOrWhiteSpace(freetext))
{
conds.Append("and FREETEXT('" + freetext + "') ");
}
const string dateFmt = "yyyy-MM-dd";
if (after != null)
{
conds.Append("and System.DateModified >= '" + after.Value.ToString(dateFmt) + "' ");
}
if (before != null)
{
conds.Append("and System.DateModified <= '" + before.Value.ToString(dateFmt) + "' ");
}
// Build necessary SQL
var query = string.Format("SELECT {0} FROM SystemIndex WHERE scope = 'file:{1}' {2}",
attrs, folder, conds);
// Open if not already done
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
// Build command
using (var cmd = new OleDbCommand(query, conn))
{
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var obj = Activator.CreateInstance <T>();
for (int i = 0; i < reader.FieldCount; i++)
{
var name = reader.GetName(i);
var value = reader.GetValue(i);
if (value is DBNull)
{
value = null;
}
var prop = props.FirstOrDefault(
p => p.Item2.ElementName.Equals(name, StringComparison.InvariantCultureIgnoreCase)).Item1;
var targetType = prop.PropertyType;
prop.SetValue(obj, value);
}
yield return(obj);
}
}
}
}
private void btnUploadExcel_Click(object sender, EventArgs e)
{
int size = -1;
DialogResult result = openFileDialog1.ShowDialog();
if (result == DialogResult.OK)
{
string file = openFileDialog1.FileName;
try
{
//string text = File.ReadAllText(file);
//size = text.Length;
string conn = string.Empty;
DataTable dtexcel = new DataTable();
//if (fileExt.CompareTo(".xls") == 0)
//conn = @"provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file + ";Extended Properties='Excel 8.0;HRD=Yes;IMEX=1';"; //for below excel 2007
//else
conn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + file + ";Extended Properties='Excel 12.0;HDR=YES';"; //for above excel 2007
using (OleDbConnection con = new OleDbConnection(conn))
{
try
{
OleDbDataAdapter oleAdpt = new OleDbDataAdapter("select * from [Sheet1$]", con); //here we read data from sheet1
dtexcel.Clear();
dtexcel.Columns.Clear();
oleAdpt.Fill(dtexcel); //fill excel data into dataTable
if (dtexcel.Rows.Count > 1)
{
StringBuilder sbBuildJSON = new StringBuilder();
sbBuildJSON.Append("[");
for (int i = 0; i < dtexcel.Rows.Count; i++)
{
if (i == 0)
{
sbBuildJSON.AppendLine("{");
}
else
{
sbBuildJSON.AppendLine(",{");
}
for (int j = 0; j < dtexcel.Columns.Count; j++)
{
if (j == dtexcel.Columns.Count - 1)
{
if (dtexcel.Columns[j].ColumnName.ToLower() == "receipt")
{
sbBuildJSON.AppendLine("\"" + dtexcel.Columns[j] + "\":" + (dtexcel.Columns[j].DataType.FullName.Equals("System.String") ? "\"" + (dtexcel.Rows[i][j].Equals(DBNull.Value) ? "" : dtexcel.Rows[i][j]) + "\"" : (dtexcel.Rows[i][j].Equals(DBNull.Value) ? 0 : dtexcel.Rows[i][j])));
}
else
{
sbBuildJSON.AppendLine("\"" + dtexcel.Columns[j] + "\":" + (dtexcel.Columns[j].DataType.FullName.Equals("System.String") ? "\"" + (dtexcel.Rows[i][j].Equals(DBNull.Value) ? "" : dtexcel.Rows[i][j]) + "\"" : (dtexcel.Rows[i][j].Equals(DBNull.Value) ? 0 : dtexcel.Rows[i][j])));
}
}
else
{
if (dtexcel.Columns[j].ColumnName.ToLower() == "receipt")
{
sbBuildJSON.AppendLine("\"" + dtexcel.Columns[j] + "\":" + (dtexcel.Columns[j].DataType.FullName.Equals("System.String") ? "\"" + (dtexcel.Rows[i][j].Equals(DBNull.Value) ? "" : dtexcel.Rows[i][j]) + "\"" : (dtexcel.Rows[i][j].Equals(DBNull.Value) ? 0 : dtexcel.Rows[i][j])) + ",");
}
else
{
sbBuildJSON.AppendLine("\"" + dtexcel.Columns[j] + "\":" + (dtexcel.Columns[j].DataType.FullName.Equals("System.String") ? "\"" + (dtexcel.Rows[i][j].Equals(DBNull.Value) ? "" : dtexcel.Rows[i][j]) + "\"" : (dtexcel.Rows[i][j].Equals(DBNull.Value) ? 0 : dtexcel.Rows[i][j])) + ",");
}
}
}
sbBuildJSON.AppendLine("}");
}
sbBuildJSON.Append("]");
richTextBox1.Text = sbBuildJSON.ToString();
}
}
catch (Exception ex)
{
}
}
// dtexcel;
}
catch (IOException)
{
}
}
}
public TechnologyRepository()
{
connection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\DB1_Candidates.mdb");
}
/// <summary>
/// 导出dbf格式
/// </summary>
/// <param name="path">路径(这里只有导出dbf用到了,其它格式的导出没有用到path)</param>
/// <param name="fileName">文件名(这里只有导出dbf用到了，其它格式的导出没有用到fileName)</param>
/// <param name="dataSet">导出的集合</param>
/// <param name="Leixing">导出类型1:DBF格式 2:XML格式 3:TXT格式 4:Microsoft.Office.Interop.Excel格式</param>
/// <returns></returns>
public static void ExportData(string fileName, DataSet dataSet, string Leixing)
{
string zhuangtai = "";
#region EXCEL导出
if (Leixing == "4")
{
CreateEXCEL(dataSet, fileName);
}
#endregion
#region DBF格式导出
if (Leixing == "1")
{
fileName += ".dbf";
string path = System.Web.HttpContext.Current.Server.MapPath("~/dbf/");
ArrayList list = new ArrayList();//用集合保存需导出的字段
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);//创建该文件夹
}
if (File.Exists(path + "xs.dbf"))
{
File.Delete(path + "xs.dbf");
}
string createSql = "create table " + "xs.dbf" + " (";
foreach (DataColumn dc in dataSet.Tables[0].Columns)
{
string fieldName = dc.ColumnName;
string type = dc.DataType.ToString();
type = "varchar(" + dc.MaxLength.ToString() + ")";
createSql = createSql + fieldName + " " + type + ",";
list.Add(fieldName);
}
createSql = createSql.Substring(0, createSql.Length - 1) + ")";
string conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=dBASE IV;";
OleDbConnection con = new OleDbConnection(conStr);
OleDbCommand cmd = new OleDbCommand();
try
{
cmd.Connection = con;
con.Open();
cmd.CommandText = createSql;
cmd.ExecuteNonQuery(); //创建新表
foreach (DataRow row in dataSet.Tables[0].Rows) //将数据导出到DBF文件中
{
string insertSql = "insert into " + "xs.dbf" + " values(";
for (int i = 0; i < list.Count; i++)
{
insertSql = insertSql + "'" + row[list[i].ToString()].ToString() + "',";
}
insertSql = insertSql.Substring(0, insertSql.Length - 1) + ")";
cmd.CommandText = insertSql;
cmd.ExecuteNonQuery();
}
StreamReader objReader = new StreamReader(path + "xs.dbf");
zhuangtai = objReader.ReadToEnd();
objReader.Close();
objReader.Dispose();
File.Delete(path + "xs.dbf");
}
catch (Exception ex)
{
zhuangtai = ex.Message;
}
finally
{
con.Close();
}
}
#endregion
#region XML格式导出
if (Leixing == "2")
{
fileName += ".xml";
try
{
zhuangtai = dataSet.GetXml();
}
catch (Exception ex)
{
zhuangtai = ex.Message;
}
}
#endregion
#region TXT格式导出
if (Leixing == "3")
{
fileName += ".txt";
try
{
StringBuilder sb = new StringBuilder();
for (int i = 0; i < dataSet.Tables[0].Columns.Count; i++)
{
sb.Append(dataSet.Tables[0].Columns[i].ColumnName + ",");
}
sb.Append("\r\n");
for (int i = 0; i < dataSet.Tables[0].Rows.Count; i++)
{
for (int j = 0; j < dataSet.Tables[0].Columns.Count; j++)
{
sb.Append(dataSet.Tables[0].Rows[i][j] + ",");
}
sb.Append("\r\n");
}
zhuangtai = sb.ToString();
}
catch (Exception ex)
{
zhuangtai = ex.Message;
}
}
#endregion
#region XML和TXT格式导出
RenderToDaoChu(zhuangtai, fileName);
#endregion
}
/// <summary>
/// 将数据导出到指定的Excel文件中
/// </summary>
/// <param name="destFileName">指定目标文件路径</param>
/// <param name="tableName">要导出到的表名称</param>
/// <returns>导出的记录的行数</returns>
public static int ExportToExcel(DataTable dt, string destFileName, string tableName)
{
//得到字段名
string szFields = "";
string szValues = "";
for (int i = 1; i < dt.Columns.Count; i++)
{
szFields += "[" + dt.Columns[i] + "],";
}
szFields = szFields.TrimEnd(',');
//定义数据连接
OleDbConnection connection = new OleDbConnection();
connection.ConnectionString = GetConnectionString("C:\\" + destFileName + ".xlsx");
OleDbCommand command = new OleDbCommand();
command.Connection = connection;
command.CommandType = CommandType.Text;
//打开数据库连接
try
{
connection.Open();
}
catch
{
throw new Exception("目标文件路径错误。");
}
//创建数据库表
try
{
command.CommandText = GetCreateTableSql("[" + tableName + "]", szFields.Split(','));
command.ExecuteNonQuery();
}
catch (Exception ex)
{
//如果允许覆盖则删除已有数据
throw ex;
}
try
{
//循环处理数据------------------------------------------
int recordCount = 0;
for (int i = 0; i < dt.Rows.Count; i++)
{
szValues = "";
for (int j = 1; j < dt.Columns.Count; j++)
{
szValues += "'" + dt.Rows[i][j] + "',";
}
szValues = szValues.TrimEnd(',');
//组合成SQL语句并执行
string szSql = "INSERT INTO [" + tableName + "](" + szFields + ") VALUES(" + szValues + ")";
command.CommandText = szSql;
recordCount += command.ExecuteNonQuery();
}
connection.Close();
return(recordCount);
}
catch (Exception ex)
{
throw ex;
}
}
public void dataCon()
{
strDSN = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Application.StartupPath.ToString() + "\\Resources/Student.accdb"; oledbCon = new OleDbConnection(strDSN);
}
/// <summary>
/// 导出到Excel（使用OleDB数据库操作方式，速度较快）
/// </summary>
/// <param name="sourceData">数据源</param>
/// <param name="fullPath">导出文件全路径，包括文件名，如果使用固定模板则该文件必须存在，否则目标位置不应有同名文件。</param>
public static void ExportToExcelByOleDB(System.Data.DataTable sourceData, string fullPath)
{
if (File.Exists(fullPath))
{
File.Delete(fullPath);
}
//初始化
string ExcelVersion = "8.0";
string strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + fullPath + "';Extended Properties='Excel " + ExcelVersion + ";HDR=YES';";
if (fullPath.EndsWith("xlsx"))
{
ExcelVersion = "12.0 xml";
strConn = @"Provider=Microsoft.ACE.OleDb.12.0;Data Source='" + fullPath + "';Extended Properties='Excel " + ExcelVersion + ";HDR=YES';";
}
OleDbConnection oleConn = new OleDbConnection(strConn);
try
{
oleConn.Open();
OleDbCommand comm = new OleDbCommand();
comm.CommandType = CommandType.Text;
comm.Connection = oleConn;
string tableName = "";
tableName = "DataExport";
string strCreateTable = "create table DataExport (";
foreach (DataColumn dc in sourceData.Columns)
{
strCreateTable += "[" + dc.ColumnName + "] Ntext, ";
}
if (strCreateTable.EndsWith(", "))
{
strCreateTable = strCreateTable.Substring(0, strCreateTable.Length - 2);
}
strCreateTable += ")";
comm.CommandText = strCreateTable;
comm.ExecuteNonQuery();
//拼合Sql语句，同时构建Parameter。
string strSql = "insert into [" + tableName + "$] values (";
for (int i = 0; i < sourceData.Columns.Count; i++)
{
strSql += "@p" + i.ToString() + ",";
OleDbParameter p = new OleDbParameter();
p.ParameterName = "@p" + i.ToString();
p.DbType = ConvertType(sourceData.Columns[i].DataType);
comm.Parameters.Add(p);
}
strSql = strSql.Substring(0, strSql.Length - 1) + ")";
comm.CommandText = strSql;
comm.Connection = oleConn;
int perSheetRowCount = fullPath.EndsWith("xlsx") ? 1048575 : 65535;
int sheetRowCount = perSheetRowCount;
for (int i = 1; i <= sourceData.Rows.Count; i++)
{
if (i > sheetRowCount)
{
comm.CommandText = "select * into [" + tableName + ((int)(i / perSheetRowCount)).ToString() + "] from [" + tableName + "$] where 1 = 2 ";
comm.ExecuteNonQuery();
comm.CommandText = strSql.Replace("[" + tableName + "$]", "[" + tableName + ((int)(i / perSheetRowCount)).ToString() + "$]");
sheetRowCount += perSheetRowCount;
}
DataRow dr = sourceData.Rows[i - 1];
//逐个读取每行的每个字段，填入Parameter
for (int j = 0; j < dr.ItemArray.Length; j++)
{
comm.Parameters[j].Value = dr[j];
}
//执行并插入Excel
comm.ExecuteNonQuery();
}
}
finally
{
oleConn.Close();
}
}
public FuncStatus DataBind(BindOperator _bp)
{
bool _tf = true;
int i;
OleDbConnection _conn = new OleDbConnection(Prument.Properties.Settings.Default.Properties["dataConnectionString"].DefaultValue.ToString());
OleDbCommand _cmd = new OleDbCommand();
OleDbDataReader _dr;
_cmd.Connection = _conn;
try
{
_conn.Open();
if (_bp == BindOperator.Update) {
_cmd.CommandText = "delete from Images where pID = " + this.pID;
_cmd.ExecuteNonQuery();
}
if (_bp == BindOperator.Insert || _bp == BindOperator.Update)
{
i = 0;
while (i < _max)
{
_cmd.CommandText = "insert into Images (Path,pID) values ('" + this.Path[i] + "'," + this.pID + ")";
_cmd.ExecuteNonQuery();
i++;
}
}
_cmd.CommandText = "select ID,Path,pID from Images where pID=" + this.pID;
_dr = _cmd.ExecuteReader();
i = 0;
while (_dr.Read())
{
this.ID[i] = _dr.GetInt32(0);
this.Path[i] = _dr.GetValue(1).ToString();
this.pID = _dr.GetInt32(2);
i++;
}
}
catch (Exception ex) {
_tf = false;
Exception _ex = new Exception("ORM.Images:" + ex.Message);
throw _ex;
}
finally
{
_conn.Close();
}
return _tf == false ? FuncStatus.Fail : FuncStatus.Success;
}
/*
* Indicatif présent [1 - 6] 185
* Indicatif imparfait [1 - 6] 185
* indicatif passé simple [1 - 6] 185
* Indicatif futur Simple [1 - 6] 196
*
* Conditionnel présent [1 - 6] 196
*
* Subjonctif présent [1 - 6] 207
* Subjonctif imparfait [1 - 6] 207
*
* Impératif présent [2 4 5] 208
*
* Participe présent [1] 210
* Participe passé [1] 210
*/
#endregion
public static void TousLesVerbes()
{
const string outtxt = "out.txt";
string[] tousLesVerves = File.ReadAllLines("verbes.txt");
string res = "";
for (int i = 0; i < tousLesVerves.Length; i++)
{
int val = 0;
// Indicatif
res += "'3', '" + Temps(val++) + 1 + "', '" + tousLesVerves[i] + "', '0'\n";
res += "'3', '" + Temps(val++) + 2 + "', '" + tousLesVerves[i] + "', '0'\n";
res += "'3', '" + Temps(val++) + 3 + "', '" + tousLesVerves[i] + "', '0'\n";
res += "'3', '" + Temps(val++) + 4 + "', '" + tousLesVerves[i] + "', '0'\n";
res += "'3', '" + Temps(val++) + 5 + "', '" + tousLesVerves[i] + "', '0'\n";
res += "'3', '" + Temps(val++) + 6 + "', '" + tousLesVerves[i] + "', '0'\n";
res += "'3', '" + Temps(val++) + 1 + "', '" + tousLesVerves[i] + "', '0'\n";
res += "'3', '" + Temps(val++) + 2 + "', '" + tousLesVerves[i] + "', '0'\n";
res += "'3', '" + Temps(val++) + 3 + "', '" + tousLesVerves[i] + "', '0'\n";
res += "'3', '" + Temps(val++) + 4 + "', '" + tousLesVerves[i] + "', '0'\n";
res += "'3', '" + Temps(val++) + 5 + "', '" + tousLesVerves[i] + "', '0'\n";
res += "'3', '" + Temps(val++) + 6 + "', '" + tousLesVerves[i] + "', '0'\n";
res += "'3', '" + Temps(val++) + 1 + "', '" + tousLesVerves[i] + "', '0'\n";
res += "'3', '" + Temps(val++) + 2 + "', '" + tousLesVerves[i] + "', '0'\n";
res += "'3', '" + Temps(val++) + 3 + "', '" + tousLesVerves[i] + "', '0'\n";
res += "'3', '" + Temps(val++) + 4 + "', '" + tousLesVerves[i] + "', '0'\n";
res += "'3', '" + Temps(val++) + 5 + "', '" + tousLesVerves[i] + "', '0'\n";
res += "'3', '" + Temps(val++) + 6 + "', '" + tousLesVerves[i] + "', '0'\n";
res += "'3', '" + Temps(val++) + 1 + "', '" + tousLesVerves[i] + "', '0'\n";
res += "'3', '" + Temps(val++) + 2 + "', '" + tousLesVerves[i] + "', '0'\n";
res += "'3', '" + Temps(val++) + 3 + "', '" + tousLesVerves[i] + "', '0'\n";
res += "'3', '" + Temps(val++) + 4 + "', '" + tousLesVerves[i] + "', '0'\n";
res += "'3', '" + Temps(val++) + 5 + "', '" + tousLesVerves[i] + "', '0'\n";
res += "'3', '" + Temps(val++) + 6 + "', '" + tousLesVerves[i] + "', '0'\n";
// Conditionnel
res += "'3', '" + Temps(val++) + 1 + "', '" + tousLesVerves[i] + "', '0'\n";
res += "'3', '" + Temps(val++) + 2 + "', '" + tousLesVerves[i] + "', '0'\n";
res += "'3', '" + Temps(val++) + 3 + "', '" + tousLesVerves[i] + "', '0'\n";
res += "'3', '" + Temps(val++) + 4 + "', '" + tousLesVerves[i] + "', '0'\n";
res += "'3', '" + Temps(val++) + 5 + "', '" + tousLesVerves[i] + "', '0'\n";
res += "'3', '" + Temps(val++) + 6 + "', '" + tousLesVerves[i] + "', '0'\n";
// Subjonctif
res += "'3', '" + Temps(val++) + 1 + "', '" + tousLesVerves[i] + "', '0'\n";
res += "'3', '" + Temps(val++) + 2 + "', '" + tousLesVerves[i] + "', '0'\n";
res += "'3', '" + Temps(val++) + 3 + "', '" + tousLesVerves[i] + "', '0'\n";
res += "'3', '" + Temps(val++) + 4 + "', '" + tousLesVerves[i] + "', '0'\n";
res += "'3', '" + Temps(val++) + 5 + "', '" + tousLesVerves[i] + "', '0'\n";
res += "'3', '" + Temps(val++) + 6 + "', '" + tousLesVerves[i] + "', '0'\n";
res += "'3', '" + Temps(val++) + 1 + "', '" + tousLesVerves[i] + "', '0'\n";
res += "'3', '" + Temps(val++) + 2 + "', '" + tousLesVerves[i] + "', '0'\n";
res += "'3', '" + Temps(val++) + 3 + "', '" + tousLesVerves[i] + "', '0'\n";
res += "'3', '" + Temps(val++) + 4 + "', '" + tousLesVerves[i] + "', '0'\n";
res += "'3', '" + Temps(val++) + 5 + "', '" + tousLesVerves[i] + "', '0'\n";
res += "'3', '" + Temps(val++) + 6 + "', '" + tousLesVerves[i] + "', '0'\n";
// Impératif
res += "'3', '" + Temps(val++) + 2 + "', '" + tousLesVerves[i] + "', '0'\n";
res += "'3', '" + Temps(val++) + 4 + "', '" + tousLesVerves[i] + "', '0'\n";
res += "'3', '" + Temps(val++) + 5 + "', '" + tousLesVerves[i] + "', '0'\n";
// Participe
res += "'3', '" + Temps(val++) + 1 + "', '" + tousLesVerves[i] + "', '0'\n";
res += "'3', '" + Temps(val++) + 1 + "', '" + tousLesVerves[i] + "', '0'\n";
}
File.AppendAllText(outtxt, res);
// Maintenant, faire le insert dans la base de données
System.Threading.Thread.Sleep(100);
using (OleDbConnection con = new OleDbConnection(@"Provider = Microsoft.Jet.OLEDB.4.0; Data Source =..\..\..\IA.MDB")) {
con.Open();
string[] lesVerbesFormat = File.ReadAllLines(outtxt);
foreach (string ligneValues in lesVerbesFormat)
{
Console.WriteLine(ligneValues);
new OleDbCommand(@"INSERT INTO VerbesConjugues (Groupe, Temps, Mode, Personne, Infinitif, nbrUtilisation)
VALUES (" + ligneValues + ")", con).ExecuteNonQuery();
}
}
}
public conectar()
{
CONEX = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source = .\\FELDB.accdb");
initializeComponet();
}
public EditOzenkiData(string[] values)
{
InitializeComponent();
this.Load += (f, a) =>
{
ozenkiSubject.Items.Clear();
using (var connec = new OleDbConnection(Properties.Settings.Default.connect))
{
connec.Open();
using (var cmd = connec.CreateCommand())
{
cmd.CommandText = "SELECT [НаименованиеПредмета], [КоличествоЧасов], [Курс] " +
"FROM [Предметы]";
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
ozenkiSubject.Items.Add(reader[0].ToString() + " " + reader[1].ToString() + "ч" + reader[2].ToString() + "к");
}
}
}
}
};
this.Load += (f, a) =>
{
this.ID = values[0];
ozenkiChipher.Text = values[1];
ozenkiCourse.Text = values[2];
ozenkiSubject.Text = values[3];
ozenkiOzenka.Text = values[4];
KRIsTrue.Checked = values[5] == "Да" ? true : false;
KRIsOzenka.Text = values[6];
};
save.Click += (f, a) =>
{
(this.Owner as MainForm).UpdateDataInOzenkiDataTable(
new string[]
{
"Шифр",
"КодПредмета",
"Оценка",
"НаличиеКР",
"ОценкаКР"
},
new string[]
{
ozenkiChipher.Text,
Datebase.GetSubjectID(ozenkiSubject.Text).ToString(),
ozenkiOzenka.Text,
KRIsTrue.Checked == true ? "Да" : "Нет",
KRIsOzenka.Text
},
new string[]
{
this.ID
}
);
(this.Owner as MainForm).LoadDataInOzenkiDataTable();
this.Close();
};
cancel.Click += (f, a) => this.Close();
}
public oledbConn(string path, string fileType)
{
string connectionstring = "";
if (fileType == "xls")
connectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + path + ";" + "Extended Properties=Excel 8.0;";
if (fileType == "mdb")
connectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + path + ";";
this.conn = new OleDbConnection(connectionstring);
this.conn.Open();
}
static void doSearch(Object state)
{
SearchObjectState sos = (SearchObjectState)state;
if (sos.SearchString == string.Empty)
{
m_results.Clear();
sos.Reset.Set();
return;
}
cHelper = cManager.GetCatalog("SYSTEMINDEX").GetQueryHelper();
cHelper.QuerySelectColumns = "\"System.ItemNameDisplay\",\"System.ItemUrl\",\"System.ItemPathDisplay\",\"System.DateModified\"";
cHelper.QueryMaxResults = MAX_RESULT;
cHelper.QuerySorting = "System.Search.Rank desc";
OleDbConnection cConnection;
try
{
using (cConnection = new OleDbConnection(
cHelper.ConnectionString))
{
cConnection.Open();
using (OleDbCommand cmd = new OleDbCommand(
cHelper.GenerateSQLFromUserQuery(
sos.SearchString
),
cConnection))
{
if (cConnection.State == ConnectionState.Open)
{
using (OleDbDataReader reader = cmd.ExecuteReader())
{
m_results.Clear();
IsWorkingFlag = true;
while (!reader.IsClosed && reader.Read())
{
if (ToAbortFlag)
{
break;
}
SearchResult result = new SearchResult()
{
Name = reader[0].ToString(), Path = reader[1].ToString(), PathDisplay = reader[2].ToString(), DateModified = reader[3].ToString()
};
m_results.Add(result);
}
IsWorkingFlag = false;
}
}
}
}
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex);
}
sos.Reset.Set();
ToAbortFlag = false;
}
public int connectRegUG(UnderGrad x)
{
OleDbConnection connection = new OleDbConnection();
try
{
connection.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccessFile.accdb;
Jet OLEDB:Database Password=MyDbPassword; Data Source=C:\Users\franc_000\Documents\College\StudentRegfinal\StudentReg\studentregdb.accdb; Persist Security Info = false; ";
connection.Open();
var cmd = new OleDbCommand("INSERT INTO Student ([Id],[Firstname], [Lastname] ,[Email] , [Address], [Subject], [intCAOdata], [doubleDegreegrade]) VALUES (@a, @b, @c, @d, @e, @f, @g, @h)");
cmd.Connection = connection;
cmd.Parameters.AddRange(new[] {
new OleDbParameter("@a", x.Id),
new OleDbParameter("@b", x.FirstName),
new OleDbParameter("@c", x.LastName),
new OleDbParameter("@d", x.Email), ////cant find reference to correct variables
new OleDbParameter("@e", x.Address),
new OleDbParameter("@f", x.Subject),
new OleDbParameter("@g", x.CAO),
new OleDbParameter("@h", "")
});
cmd.ExecuteNonQuery();
Console.WriteLine("Student registered successfully.");
}
catch (Exception ex)
{
Console.WriteLine("Error " + ex);
}
connection.Close();
return 0;
}
public List <ContactInfo> GetFarmListFromFile(string fileName, ContactFileType fileType,
byte[] bytes, int userId)
{
List <ContactInfo> contacts = new List <ContactInfo>();
String folderPath = System.Configuration.ConfigurationManager.AppSettings["ContactFileLocation"];
String path = folderPath;
FileStream fileStream;
String filePath = "";
string[] dateFormatSupport = { "MM/dd/yyyy", "MM/dd/yy", "ddMMMyyyy", "dMMMyyyy" };
// Make sure the base directory ends with a "\".
if (!path.EndsWith("\\"))
{
path += "\\";
}
path += userId;
try
{
//Check for User Id Directory. If not existing create one
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
filePath += path + "\\" + userId + "-" + fileName;
fileStream = File.Open(filePath, FileMode.Create, FileAccess.Write);
int length = bytes.Length;
fileStream.Write(bytes, 0, length);
fileStream.Flush();
fileStream.Close();
//Load Contact Data From File
string connectionString = string.Empty;
DataTable schemaTable = new DataTable();
DataTable dataTable = new DataTable();
if (fileType == ContactFileType.Excel)
{
OleDbConnection connection = new OleDbConnection();
OleDbDataAdapter adapter = new OleDbDataAdapter();
connectionString = "Data Source=" + filePath + ";" +
"Provider=Microsoft.Jet.OLEDB.4.0;" +
"Extended Properties=Excel 8.0;";
connection = new OleDbConnection(connectionString);
connection.Open();
// Populate the DataTable with schema information on the data source tables.
schemaTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
new object[] { null, null, null, "TABLE" });
string tableName = schemaTable.Rows[0]["Table_Name"].ToString();
// Clear the DataTable.
schemaTable.Clear();
adapter = new OleDbDataAdapter("SELECT * FROM [" + tableName + "]",
connection);
adapter.Fill(dataTable);
connection.Close();
}
else if (fileType == ContactFileType.Csv)
{
OdbcConnection connection = new OdbcConnection();
OdbcDataAdapter adapter = new OdbcDataAdapter();
path = path.Replace("\\", "//");
connectionString = "Driver={Microsoft Text Driver (*.txt; *.csv)};" +
"Dbq=" + path + ";" +
"Extensions=asc,csv,tab,txt;" +
"Persist Security Info=False";
/*connectionString= "Driver={Microsoft Text Driver (*.txt;*.csv)};" +
* "Dbq=" + path.Trim() + ";" +
* "Extensions=asc,csv,tab,txt;" +
* "Persist Security Info=False";
*/
connection = new OdbcConnection(connectionString);
connection.Open();
adapter = new OdbcDataAdapter("SELECT * FROM [" + userId + "-" + fileName + "]",
connection);
adapter.Fill(dataTable);
connection.Close();
}
else
{
throw new InvalidFileException();
}
if (dataTable.Rows.Count == 0)
{
throw new NoDataException();
}
if (dataTable.Columns.Count != 23)
{
throw new InvalidFormatException();
}
int countOfContacts = 0;
foreach (DataRow dataRow in dataTable.Rows)
{
//Check for a blank row ans skip
bool skip = true;
for (int i = 0; i < dataRow.ItemArray.Length; i++)
{
if (dataRow[i].ToString().Trim() != string.Empty)
{
skip = false;
}
}
if (skip)
{
continue;
}
if (dataRow[12].ToString().Trim() == string.Empty ||
dataRow[13].ToString().Trim() == string.Empty ||
dataRow[14].ToString().Trim() == string.Empty ||
dataRow[15].ToString().Trim() == string.Empty ||
dataRow[17].ToString().Trim() == string.Empty ||
dataRow[18].ToString().Trim() == string.Empty ||
dataRow[19].ToString().Trim() == string.Empty ||
dataRow[20].ToString().Trim() == string.Empty
)
{
throw new InvalidFieldDataException();
}
ContactInfo contact = new ContactInfo();
string temp;
int tem;
if (dataRow[0].ToString().Trim() == string.Empty)
{
contact.ScheduleNumber = 0;
}
else
{
temp = dataRow[0].ToString().Trim();
int.TryParse(temp, out tem);
contact.ScheduleNumber = tem;
}
if (dataRow[1].ToString().Trim() == string.Empty)
{
contact.OwnerFullName = "";
}
else
{
contact.OwnerFullName = dataRow[1].ToString().Trim();
}
if (dataRow[2].ToString().Trim() == string.Empty)
{
contact.Lot = 0;
}
else
{
temp = dataRow[2].ToString().Trim();
int.TryParse(temp, out tem);
contact.Lot = tem;
}
if (dataRow[3].ToString().Trim() == string.Empty)
{
contact.Block = "";
}
else
{
contact.Block = dataRow[3].ToString().Trim();
}
if (dataRow[4].ToString().Trim() == string.Empty)
{
contact.Subdivision = "";
}
else
{
contact.Subdivision = dataRow[4].ToString().Trim();
}
if (dataRow[5].ToString().Trim() == string.Empty)
{
contact.Filing = "";
}
else
{
contact.Filing = dataRow[5].ToString().Trim();
}
if (dataRow[6].ToString().Trim() == string.Empty)
{
contact.SiteAddress = "";
}
else
{
contact.SiteAddress = dataRow[6].ToString().Trim();
}
if (dataRow[7].ToString().Trim() == string.Empty)
{
contact.Bedrooms = 0;
}
else
{
temp = dataRow[7].ToString().Trim();
int.TryParse(temp, out tem);
contact.Bedrooms = tem;
}
if (dataRow[8].ToString().Trim() == string.Empty)
{
contact.FullBath = 0;
}
else
{
temp = dataRow[8].ToString().Trim();
int.TryParse(temp, out tem);
contact.FullBath = tem;
}
if (dataRow[9].ToString().Trim() == string.Empty)
{
contact.ThreeQuarterBath = 0;
}
else
{
temp = dataRow[9].ToString().Trim();
int.TryParse(temp, out tem);
contact.ThreeQuarterBath = tem;
}
if (dataRow[10].ToString().Trim() == string.Empty)
{
contact.HalfBath = 0;
}
else
{
temp = dataRow[10].ToString().Trim();
int.TryParse(temp, out tem);
contact.HalfBath = tem;
}
if (dataRow[11].ToString().Trim() == string.Empty)
{
contact.Acres = 0;
}
else
{
float ftem;
temp = dataRow[11].ToString().Trim();
float.TryParse(temp, out ftem);
contact.Acres = ftem;
}
contact.ActMktComb = dataRow[12].ToString().Trim();
contact.OwnerFirstName = dataRow[13].ToString().Trim();
contact.OwnerLastName = dataRow[14].ToString().Trim();
contact.OwnerAddress1 = dataRow[15].ToString().Trim();
contact.OwnerAddress2 = dataRow[16].ToString().Trim();
contact.OwnerCity = dataRow[17].ToString().Trim();
contact.OwnerState = dataRow[18].ToString().Trim();
contact.OwnerZip = dataRow[19].ToString().Trim();
contact.OwnerCountry = dataRow[20].ToString().Trim();
if (dataRow[21].ToString().Trim() == string.Empty)
{
contact.SaleDate = System.DateTime.Now;
}
else
{
contact.SaleDate = Convert.ToDateTime(dataRow[21].ToString().Trim());
}
//contact.SaleDate = DateTime.ParseExact(dataRow[21].ToString().Trim(), dateFormatSupport, System.Globalization.CultureInfo.CurrentCulture, System.Globalization.DateTimeStyles.None);
if (dataRow[22].ToString().Trim() == string.Empty)
{
contact.TransAmount = 0;
}
else
{
decimal dtem;
temp = dataRow[22].ToString().Trim();
decimal.TryParse(temp, out dtem);
contact.TransAmount = dtem;
}
contact.LastModifyBy = userId;
contacts.Add(contact);
countOfContacts++;
}
if (countOfContacts == 0)
{
throw new Exception("No valid Contact records in the uploaded File.");
}
}
finally
{
// Delete the file.
if (File.Exists(filePath))
{
File.Delete(filePath);
}
}
return(contacts);
}
public Connection()
{
con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\\ROServiceApp\\ROServiceApp\\ro_database.mdb;Jet OLEDB:DataBase Password=123");
}
public static void executeNonQuery(OleDbConnection conn, string insertStatement)
{
OleDbCommand comm = new OleDbCommand(insertStatement, conn);
comm.ExecuteNonQuery();
}
