private static void ReadFromExcel()
{
DataTable dt = new DataTable("table");
OleDbConnectionStringBuilder csBuilder = new OleDbConnectionStringBuilder();
csBuilder.Provider = "Microsoft.ACE.OLEDB.12.0";
csBuilder.DataSource = @"..\..\Table.xlsx";
csBuilder.Add("Extended Properties", "Excel 12.0 Xml;HDR=YES");
using (OleDbConnection connection = new OleDbConnection(csBuilder.ConnectionString))
{
connection.Open();
string query = @"SELECT * FROM Sample";
using (OleDbDataAdapter adapter = new OleDbDataAdapter(query, connection))
{
adapter.FillSchema(dt, SchemaType.Source);
adapter.Fill(dt);
}
}
foreach (DataRow row in dt.Rows)
{
foreach (var item in row.ItemArray)
{
Console.WriteLine(item);
}
}
}
public AccessDatabase()
{
SyncRoot = new object();
Name = "Access 2013";
CollectionName = "table1";
Category = "SQL";
Description = "Access 2013";
Website = "http://www.microsoft.com/en-us/download/details.aspx?id=13255";
Color = Color.FromArgb(218, 66, 127);
Requirements = new string[]
{
"Microsoft Access Database Engine 2010 Redistributable",
"Microsoft.Office.Interop.Access.Dao.dll"
};
DataDirectory = Path.Combine(MainForm.DATABASES_DIRECTORY, Name);
OleDbConnectionStringBuilder cb = new OleDbConnectionStringBuilder();
cb.Provider = "Microsoft.ACE.OLEDB.12.0";
cb.DataSource = String.Format(@"{0}\{1}.accdb", DataDirectory, Name);
ConnectionString = cb.ConnectionString;
Logger = LogManager.GetLogger(Properties.Settings.Default.TestLogger);
}
/// <summary>
/// Reads the contents of an excel report file into a list of sales arrays;
/// </summary>
/// <param name="connectionString"></param>
/// <param name="salesDate"></param>
/// <returns>rows: Supermarket, Data, ProductID, Quantity, Unit Price</returns>
public static List<string[]> ReadOneExcelFile(OleDbConnectionStringBuilder connectionString, string supermarket, string salesDate)
{
List<String[]> data = new List<String[]>();
using (OleDbConnection connection = new OleDbConnection(connectionString.ToString()))
{
connection.Open();
var command = new OleDbCommand("select * from [Sales$B3:E1000]", connection);
using (OleDbDataReader oneRow = command.ExecuteReader())
{
oneRow.Read(); // Skip row - ProductID, Quantity, Unit Price
while (oneRow.Read())
{
string productID = oneRow[0].ToString();
string quantity = oneRow[1].ToString();
string unitPrice = oneRow[2].ToString();
int result; // only to check if the line containd a sale
if (int.TryParse(productID, out result))
// If not the Last rows
{
string[] line = { supermarket, salesDate, productID, quantity, unitPrice };
data.Add(line);
}
}
}
}
return data;
}
public static IEnumerable<string[]> GetReportsData(string zipPath, string extractPath)
{
if (Directory.Exists(extractPath))
{
Directory.Delete(extractPath, true);
}
using (ZipFile archive = ZipFile.Read(zipPath))
{
archive.ExtractAll(extractPath, ExtractExistingFileAction.OverwriteSilently);
}
IEnumerable<string> excelFiles = DirSearch(extractPath);
var connectionString = new OleDbConnectionStringBuilder();
connectionString.Provider = "Microsoft.ACE.OLEDB.12.0";
connectionString.Add("Extended Properties", "Excel 12.0");
var sales = new List<String[]>();
foreach (var file in excelFiles)
{
connectionString.DataSource = file;
// Get the date from the name of the file.
string pattern = "-Sales-Report-";
string salesDate = file.Substring(file.LastIndexOf(pattern) + pattern.Length);
string supermarket = Path.GetFileNameWithoutExtension(file.Substring(0, file.LastIndexOf(pattern)));
salesDate = salesDate.Substring(0, salesDate.IndexOf(".xls"));
sales.AddRange(ReadOneExcelFile(connectionString, supermarket, salesDate));
}
Directory.Delete(extractPath, true);
return sales;
}
public static DbConnectionStringBuilder CreateConnectionStringBuilder(DataProvider dataProvider)
{
DbConnectionStringBuilder dbConnectionStringBuilder;
switch (dataProvider)
{
case DataProvider.Odbc:
dbConnectionStringBuilder = new OdbcConnectionStringBuilder();
break;
case DataProvider.OleDB:
dbConnectionStringBuilder = new OleDbConnectionStringBuilder();
break;
case DataProvider.SqlServer:
dbConnectionStringBuilder = new SqlConnectionStringBuilder();
break;
default:
dbConnectionStringBuilder = null;
break;
}
return dbConnectionStringBuilder;
}
public void LoadDelegates()
{
OleDbConnectionStringBuilder sb = new OleDbConnectionStringBuilder();
sb.Provider = "Microsoft.ACE.OLEDB.12.0";
sb.DataSource = Server.MapPath("~/../uploads/db1.accdb");
OleDbConnection conn = new OleDbConnection(sb.ConnectionString);
string sqlQuery = "SELECT * FROM Bag";
OleDbCommand command = new OleDbCommand(sqlQuery, conn);
OleDbDataReader myReader;
//Open connection
conn.Open();
//Retrieve the data and display by using a DataList
myReader = command.ExecuteReader();
dlstDataList.DataSource = myReader;
dlstDataList.DataBind();
myReader.Close();
//Close connection
conn.Close();
}
protected void Page_Load(object sender, EventArgs e)
{
//Define connection
OleDbConnectionStringBuilder sb = new OleDbConnectionStringBuilder();
sb.Provider = "Microsoft.ACE.OLEDB.12.0";
sb.DataSource = Server.MapPath("~/../uploads/db1.accdb");
OleDbConnection conn = new OleDbConnection(sb.ConnectionString);
string sqlQuery = "SELECT [BagID],[BagName], [Quantity],[UserName], [Color], [Cost], [Status]FROM [Orders]";
_myAdapter = new OleDbDataAdapter(sqlQuery, conn);
//Event handlers
dlstDataList.ItemCommand += new DataListCommandEventHandler(dlstDataList_ItemCommand);
Save.Click += new EventHandler(Save_Click);
Clear.Click += new EventHandler(Clear_Click);
if (!this.IsPostBack)
{
this.LoadDelegates();
//Load table
//No need to open and close connection. DataAdapter does it.
_myAdapter.Fill(_myDs, _orderItemsName);
_myDs.Clear();
this.Session[_orderName] = _myDs;
}
}
//Ruft die einträge einer Person ab und gibt sie als Eintrag objekt zurück
private Eintrag EintragAbrufen(Person p)
{
Eintrag eintrag = new Eintrag();
OleDbConnectionStringBuilder bldr = new OleDbConnectionStringBuilder("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=H:\\Eigene Dateien\\Visual Studio 2013\\Fi12\\Projekt\\Datenbank11.accdb");
OleDbConnection con = new OleDbConnection();
OleDbCommand com = new OleDbCommand();
con.ConnectionString = bldr.ConnectionString;
con.Open();
com.Connection = con;
com.CommandText = "SELECT Tweet FROM Tweets WHERE (AccID = pId)";
com.Parameters.Add("pId", OleDbType.Numeric);
com.CommandType = CommandType.Text;
com.Parameters["pId"].Value = p.Id;
com.ExecuteNonQuery();
com.Connection = con;
OleDbDataReader reader = com.ExecuteReader();
while (reader.Read())
{
eintrag.addEintrag((string)reader["Tweet"]);
}
reader.Close();
return eintrag;
}
public Connect(string dbhost = "localhost", string dbname = "test", string dbusername = "******", string dbpass = "", string dbdriver = "SQLOLEDB")
{
try
{
OleDbConnectionStringBuilder connection = new OleDbConnectionStringBuilder();
connection["Provider"] = dbdriver;
connection["Data Source"] = dbhost;
connection["Database"] = dbname;
connection["User ID"] = dbusername;
connection["Password"] = dbpass;
_conn = new OleDbConnection();
_conn.ConnectionString = connection.ConnectionString;
_conn.Open();
}
catch (InvalidOperationException ex)
{
Console.WriteLine(ex.Message);
}
catch (OleDbException ex)
{
this.DisplayOleDbErrorCollection(ex);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
protected void showOrders()
{
OleDbConnectionStringBuilder sb = new OleDbConnectionStringBuilder();
sb.Provider = "Microsoft.ACE.OLEDB.12.0";
sb.DataSource = Server.MapPath("/vedb01/uploads/db1.accdb");
OleDbConnection conn = new OleDbConnection(sb.ConnectionString);
string str = "SELECT * FROM Orders";
conn.Open();
OleDbCommand com = new OleDbCommand("", conn);
com.CommandText = str;
OleDbDataReader data = com.ExecuteReader();
string stre = "";
stre += "<table cellpadding='0' cellspacing='0' border='1'>";
stre += "<tr><td>OrderID</td><td>BagID</td><td>BagName</td><td>Quantity</td><td>UserName</td><td>Color</td><td>Cost</td><td>Status</td></tr>";
while (data.Read())
{
stre += "<tr>";
for (int i = 0; i < data.FieldCount; i++)
{
stre += "<td>" + data[i].ToString() + "</td>";
}
stre +="<tr/>";
}
stre += "</table>";
ltlOrders.Text = stre;
}
private static void PrintSpreadsheet(OleDbConnectionStringBuilder connestionString)
{
DataSet sheet1 = new DataSet();
using (OleDbConnection connection = new OleDbConnection(connestionString.ConnectionString))
{
connection.Open();
string selectSql = @"SELECT * FROM [Sheet1$]";
using (OleDbDataAdapter adapter = new OleDbDataAdapter(selectSql, connection))
{
adapter.Fill(sheet1);
}
connection.Close();
}
var table = sheet1.Tables[0];
foreach (DataRow row in table.Rows)
{
foreach (DataColumn column in table.Columns)
{
Console.Write("{0, -20} ", row[column]);
}
Console.WriteLine();
}
}
private static void Main()
{
var connectionBuilder = new OleDbConnectionStringBuilder
{
Provider = "Microsoft.ACE.OLEDB.12.0;"
};
connectionBuilder.Add("Extended Properties", "Excel 12.0 XML");
connectionBuilder.DataSource = File;
var connectToExcel = new OleDbConnection(connectionBuilder.ConnectionString);
var cmdToExcel = new OleDbCommand("SELECT * FROM [Sheet1$]", connectToExcel);
connectToExcel.Open();
using (connectToExcel)
{
var reader = cmdToExcel.ExecuteReader();
while (reader != null && reader.Read())
{
var name = (string)reader["FullName"];
var salary = (double)reader["Salary"];
Console.WriteLine("{0}: {1}", name, salary);
}
}
}
protected void btnAddBag_Click(object sender, EventArgs e)
{
try
{
OleDbConnectionStringBuilder sb = new OleDbConnectionStringBuilder();
sb.Provider = "Microsoft.ACE.OLEDB.12.0";
sb.DataSource = Server.MapPath("/vedb01/uploads/db1.accdb");
OleDbConnection conn = new OleDbConnection(sb.ConnectionString);
conn.Open();
string insertQuery = "insert into Bag ( [BagName], [Supplier], [Color], [Category], [Price], [Description]) values (@name ,@supplier ,@color ,@category ,@price ,@description)";
OleDbCommand com = new OleDbCommand(insertQuery, conn);
com.Parameters.AddWithValue("@name", txtBagName.Text);
com.Parameters.AddWithValue("@supplier", txtSupplier.Text);
com.Parameters.AddWithValue("@color", txtColor.Text);
com.Parameters.AddWithValue("@category", txtCategory.Text);
com.Parameters.AddWithValue("@price", txtPrice.Text);
com.Parameters.AddWithValue("@description", txtDescription.Text);
com.ExecuteNonQuery();
lblMessage.Text = "The bag was added successfully " + txtBagName.Text + " !";
conn.Close();
}
catch (Exception ex)
{
Response.Write("Error: " + ex.ToString());
lblMessage.Text = "Error" + txtBagName.Text + " !";
}
}
/// <summary>
/// Constructor for the class for Epi 2000 projects
/// </summary>
/// <param name="proj">Project the metadata belongs to</param>
public MetadataDbProvider(Project proj)
{
IDbDriverFactory dbFactory = DbDriverFactoryCreator.GetDbDriverFactory(Configuration.AccessDriver);
OleDbConnectionStringBuilder cnnStrBuilder = new OleDbConnectionStringBuilder();
cnnStrBuilder.DataSource = proj.FilePath;
this.db = dbFactory.CreateDatabaseObject(cnnStrBuilder);
}
private static void Main()
{
Console.WriteLine("Name:");
string name = Console.ReadLine();
Console.WriteLine("Salary for {0}:", name);
string salary = Console.ReadLine();
var connectionBuilder = new OleDbConnectionStringBuilder
{
Provider = "Microsoft.ACE.OLEDB.12.0;"
};
connectionBuilder.Add("Extended Properties", "Excel 12.0 XML");
connectionBuilder.DataSource = File;
var connectToExcel = new OleDbConnection(connectionBuilder.ConnectionString);
var cmdAppendRow = new OleDbCommand("INSERT INTO [Sheet1$](FullName, Salary) VALUES('" + name + "','" + salary + "')", connectToExcel);
try
{
connectToExcel.Open();
using (connectToExcel)
{
int rowsAffected = cmdAppendRow.ExecuteNonQuery();
Console.WriteLine("{0} rows affected", rowsAffected);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
public void ApplyEventDialogs(Architecture a)
{
this.Scenario = a.Scenario;
if (this.OnApplyEvent != null)
{
this.OnApplyEvent(this, a);
}
if (nextScenario.Length > 0)
{
base.Scenario.EnableLoadAndSave = false;
List<int> factionIds = new List<int>();
foreach (Faction f in this.Scenario.PlayerFactions)
{
factionIds.Add(f.ID);
}
OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder
{
DataSource = "GameData/Scenario/" + nextScenario,
Provider = "Microsoft.Jet.OLEDB.4.0"
};
this.Scenario.LoadGameScenarioFromDatabase(builder.ConnectionString, factionIds);
this.Scenario.GameScreen.ReloadScreenData();
base.Scenario.EnableLoadAndSave = true;
}
}
private void btPotvrdi_Click(object sender, EventArgs e)
{
if (String.IsNullOrEmpty(tbPutanja.Text) || (op.CheckPathExists == false || op.CheckFileExists == false))
{
MessageBox.Show("Proverite da li ste uneli sve podatke kako treba!", "Greška!", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
string newConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + tbPutanja.Text;
OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder();
builder.Add("Provider", "Microsoft.ACE.OLEDB.12.0");
builder.Add("Data source", tbPutanja.Text);
Program.ChangeConnectionString(builder.DataSource, builder.Provider);
Properties.Settings.Default.Save();
try
{
OleDbConnection dbConn = new OleDbConnection(builder.ConnectionString);
dbConn.Open();
dbConn.Close();
}
catch
{
MessageBox.Show("Lokacija baze ili naziv fajla nije ispravan! Moraćete ponovo da je definišete.", "Informacija", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
LoadingScreen s = new LoadingScreen();
Form1 f = new Form1(s);
this.Hide();
f.Show();
}
private static void InputDataFromExl(string directoryPath, string excelFileName)
{
DataTable dt = new DataTable("newtable");
OleDbConnectionStringBuilder csbuilder = new OleDbConnectionStringBuilder();
csbuilder.Provider = "Microsoft.ACE.OLEDB.12.0";
csbuilder.DataSource = directoryPath + ReportsDirectory + excelFileName;
csbuilder.Add("Extended Properties", "Excel 12.0 Xml;HDR=YES");
using (OleDbConnection connection = new OleDbConnection(csbuilder.ConnectionString))
{
connection.Open();
string selectSql = @"SELECT * FROM [Sales$]";
using (OleDbDataAdapter adapter = new OleDbDataAdapter(selectSql, connection))
{
adapter.FillSchema(dt, SchemaType.Source);
adapter.Fill(dt);
}
connection.Close();
}
Console.WriteLine(dt.Rows[0].ItemArray[0]);
int rowsCount = dt.Rows.Count - 1;
for (int i = 2; i < rowsCount; i++)
{
foreach (var item in dt.Rows[i].ItemArray)
{
Console.WriteLine(item);
}
}
}
static void Main(string[] args)
{
DataTable dt = new DataTable("newtable");
OleDbConnectionStringBuilder csbuilder = new OleDbConnectionStringBuilder();
csbuilder.Provider = "Microsoft.ACE.OLEDB.12.0";
csbuilder.DataSource = @"..\..\Table.xlsx";
csbuilder.Add("Extended Properties", "Excel 12.0 Xml;HDR=YES");
using (OleDbConnection connection = new OleDbConnection(csbuilder.ConnectionString))
{
connection.Open();
string selectSql = @"SELECT * FROM [Sheet2$]";
using (OleDbDataAdapter adapter = new OleDbDataAdapter(selectSql, connection))
{
adapter.FillSchema(dt, SchemaType.Source);
adapter.Fill(dt);
}
connection.Close();
}
foreach (DataRow row in dt.Rows)
{
foreach (var item in row.ItemArray)
{
Console.WriteLine(item);
}
}
}
protected void Button1_Click(object sender, EventArgs e)
{
try
{
OleDbConnectionStringBuilder sb = new OleDbConnectionStringBuilder();
sb.Provider = "Microsoft.ACE.OLEDB.12.0";
sb.DataSource = Server.MapPath("/vedb01/uploads/db1.accdb");
OleDbConnection conn = new OleDbConnection(sb.ConnectionString);
conn.Open();
string updateQuery = "UPDATE Bag SET BagName=@name, Supplier=@supplier, Color=@color, Category=@category, Price=@price, Description=@description WHERE BagID= @ID";
OleDbCommand com = new OleDbCommand(updateQuery, conn);
com.Parameters.AddWithValue("@ID", txtBagId.Text);
com.Parameters.AddWithValue("@name", txtBagName.Text);
com.Parameters.AddWithValue("@supplier", txtSupplier.Text);
com.Parameters.AddWithValue("@color", txtColor.Text);
com.Parameters.AddWithValue("@category", txtCategory.Text);
com.Parameters.AddWithValue("@price", txtPrice.Text);
com.Parameters.AddWithValue("@description", txtDescription.Text);
com.ExecuteNonQuery();
lblMessage.Text = "The bag No " + txtBagId.Text +" was updated successfully " + txtBagName.Text + " !";
conn.Close();
}
catch (Exception ex)
{
Response.Write("Error: " + ex.ToString());
lblMessage.Text = "Error !";
}
}
public static string GetConnectionString(string fileName)
{
OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder();
string[] newExtensions = new string[]
{
".xlsx", ".xlsb", ".xlsm"
};
string[] oldExtensions = new string[]
{
".xls"
};
string extension = Path.GetExtension(fileName);
if (newExtensions.Contains(extension))
{
builder.Provider = "Microsoft.ACE.OLEDB.12.0";
builder.Add("Extended Properties", "Excel 12.0 Xml; HDR=No; READONLY=true; IMEX=1");
}
else if (oldExtensions.Contains(extension))
{
builder.Provider = "Microsoft.Jet.OLEDB.4.0";
builder.Add("Extended Properties", "Excel 8.0; HDR=No; READONLY=true; IMEX=1");
}
else
{
throw new ArgumentException(Resources.UnknownExcelExtension, "fileName");
}
builder.DataSource = fileName;
return builder.ConnectionString;
}
protected void Page_Load(object sender, EventArgs e)
{
if (IsPostBack)
{
OleDbConnectionStringBuilder sb = new OleDbConnectionStringBuilder();
sb.Provider = "Microsoft.ACE.OLEDB.12.0";
sb.DataSource = Server.MapPath("/vedb01/uploads/db1.accdb");
OleDbConnection conn = new OleDbConnection(sb.ConnectionString);
conn.Open();
string checkUser = "******" + txtUserName.Text + "'";
OleDbCommand com = new OleDbCommand(checkUser, conn);
temp = Convert.ToInt32(com.ExecuteScalar().ToString());
if (temp != 0)
{
Response.Write("User Already Exists !");
}
conn.Close();
}
}
private static void InsertDataToExcel(OleDbConnectionStringBuilder csbuilder, Data data)
{
OleDbConnection connection = new OleDbConnection(csbuilder.ConnectionString);
connection.Open();
using (connection)
{
OleDbCommand command = new OleDbCommand("INSERT INTO [Sheet1$] (Id, Name, Score) " +
"VALUES (@id, @name, @score)", connection);
OleDbParameter id = new OleDbParameter("@id", data.Id);
OleDbParameter name = new OleDbParameter("@name", data.Name);
OleDbParameter score = new OleDbParameter("@score", data.Score);
command.Parameters.AddRange(new OleDbParameter[] { id, name, score });
try
{
command.ExecuteNonQuery();
Console.WriteLine("Row inserted successfully.");
}
catch (OleDbException exception)
{
Console.WriteLine("SQL Error occured: " + exception);
}
}
}
static void Main()
{
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
try
{
OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder();
builder.Add("Provider", Properties.Settings.Default.provider);
builder.Add("Data Source", Properties.Settings.Default.path);
string connString = builder.ConnectionString;
OleDbConnection conn = new OleDbConnection(connString);
conn.Open();
conn.Close();
LoadingScreen splashy = new LoadingScreen();
splashy.Show();
Application.Run(new Form1(splashy));
/*Form1 f = new Form1();
f.Show();*/
}
catch
{
Application.Run(new ConnectionDefinition());
/*ConnectionDefinition c = new ConnectionDefinition();
c.Show();*/
}
//Application.Run(/*new ConnectionDefinition()*/);
}
protected void btnCS_Click(object sender, EventArgs e)
{
try
{
OleDbConnectionStringBuilder sb = new OleDbConnectionStringBuilder();
sb.Provider = "Microsoft.ACE.OLEDB.12.0";
sb.DataSource = Server.MapPath("/vedb01/uploads/db1.accdb");
OleDbConnection conn = new OleDbConnection(sb.ConnectionString);
conn.Open();
string updateQuery = "UPDATE Orders SET Status=@status WHERE ID= @ID";
OleDbCommand com = new OleDbCommand(updateQuery, conn);
com.Parameters.AddWithValue("@status", ddlOrderStatus.SelectedValue.ToString());
com.Parameters.AddWithValue("@ID", txtOrderID.Text);
com.ExecuteNonQuery();
lblMessage.Text = "The Order No " + txtOrderID.Text + " was updated successfully !";
conn.Close();
Response.AppendHeader("Refresh", "2;url=Orders.aspx");
}
catch (Exception ex)
{
Response.Write("Error: " + ex.ToString());
lblMessage.Text = "Error !";
}
}
/// <summary>
/// Creates a connection string to an Excel (version 8) file.
/// </summary>
/// <param name="filePath">Specifies the path to the Excel file.</param>
/// <returns>A string that can be used as a connection string in an ADO.NET connection.</returns>
public static string CreateExcel8FileConnectionString(string filePath)
{
var resultBuilder = new OleDbConnectionStringBuilder(ConnectionStringTemplates.Excel8){
DataSource = filePath };
return resultBuilder.ConnectionString;
}
static void Main(string[] args)
{
var fileName = string.Format("{0}\\..\\..\\..\\file.xlsx", Directory.GetCurrentDirectory());
DataSet sheet1 = new DataSet();
OleDbConnectionStringBuilder conString = new OleDbConnectionStringBuilder();
conString.Provider = "Microsoft.ACE.OLEDB.12.0";
conString.DataSource = fileName;
conString.Add("Extended Properties", "Excel 12.0 Xml;HDR=YES");
using (OleDbConnection connection = new OleDbConnection(conString.ConnectionString))
{
connection.Open();
string selectSql = @"SELECT * FROM [Sheet1$]";
using (OleDbDataAdapter adapter = new OleDbDataAdapter(selectSql, connection))
{
adapter.Fill(sheet1);
}
connection.Close();
}
var table = sheet1.Tables[0];
foreach (DataRow row in table.Rows)
{
foreach (DataColumn column in table.Columns)
{
Console.Write("{0, 15} ", row[column]);
}
Console.WriteLine();
}
}
protected void AddBug(string BugData)
{
System.Data.OleDb.OleDbConnectionStringBuilder objBuilder;
System.Data.OleDb.OleDbConnection objCon;
System.Data.OleDb.OleDbCommand objComm;
string strInsert = "";
objBuilder = new System.Data.OleDb.OleDbConnectionStringBuilder();
objBuilder.ConnectionString = "Data Source= C:\\BugData.mdb";
objBuilder.Add("Provider", "Microsoft.Jet.Oledb.4.0");
objBuilder.Add("Jet OLEDB:Database Password", "");
objBuilder.Add("Jet OLEDB:Database Locking Mode", 1);
objCon = new System.Data.OleDb.OleDbConnection(objBuilder.ConnectionString);
strInsert = " Insert Into ReportedBugs ";
strInsert += " (Tester, AppName, Build, DateReported, Description) ";
strInsert += " Values( " + BugData + " ) ";
objComm = new System.Data.OleDb.OleDbCommand();
objComm.Connection = objCon;
objComm.CommandType = System.Data.CommandType.Text;
objComm.CommandText = strInsert;
try
{
objCon.Open();
objComm.ExecuteNonQuery();
objCon.Close();
Response.Redirect("Default.aspx");
}
catch (Exception ex)
{
Response.Write(ex.ToString());
}
}
public ExcelAccess(string filePath)
{
this.filePath = filePath;
this.oleDbConnectionSb = new OleDbConnectionStringBuilder();
this.oleDbConnectionSb.Provider = "Microsoft.ACE.OLEDB.12.0";
this.oleDbConnectionSb.DataSource = this.filePath;
this.oleDbConnectionSb.Add("Extended Properties", "Excel 12.0 Xml;HDR=YES;IMEX=1");
}
public PracticalBusiness()
{
OleDbConnectionStringBuilder sb = new OleDbConnectionStringBuilder();
sb.Provider = "Microsoft.ACE.OLEDB.12.0";
sb.DataSource = HttpContext.Current.Server.MapPath("~/../uploads/db1.accdb");
_data = new PracticalData(sb.ConnectionString);
}
private void ConvertDBFToCSV(string dbfPath)
{
if (File.Exists(dbfPath))
{
OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder()
{
DataSource = Path.GetDirectoryName(dbfPath),
Provider = "Microsoft.Jet.OLEDB.4.0"
};
builder.Add("Extended Properties", "dBase III");
using (OleDbConnection connection = new OleDbConnection() { ConnectionString = "Provider=vfpoledb;Data Source=" + dbfPath + ";Collating Sequence=machine;" })
{
using (OleDbCommand cmd = new OleDbCommand() { Connection = connection })
{
cmd.CommandText = "SELECT MR_BKNO,CONS_ACC,COL_DATE,COL_DATE,AMT_PAID,MR_RPNO FROM [" + Path.GetFileName(dbfPath) + "] where AMT_PAID<>0.00 and CONS_ACC<>" + (char)34 + " " + (char)34;
connection.Open();
DataTable dt = new DataTable();
dt.Load(cmd.ExecuteReader());
string csvData = string.Empty;
if (dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
csvData +=
"NULL" + "}" + //SBM ID
"NULL" + "}" + //Collector Name
//"NULL" + "}" + //Collector ID
row[0].ToString().Trim() + "}" +
row[1].ToString().Trim() + "}" +
row[2].ToString().Trim().Replace(" 00.00.00", "") + "}" +
row[3].ToString().Trim().Replace(" 00.00.00", "") + "}" +
row[4].ToString().Trim() + "}" +
"NULL" + "}" + //Receipet No.
"NULL" + "}" + //Cheque No.
"01/01/2001" + "}" + //Cheque Date.
"NULL" + "}" + //BankNameCode
"NULL" + "}" + //M<anual Book No
row[5].ToString().Trim() +
"}Cash}0"
+ Environment.NewLine;
}
}
connection.Close();
string csvFilePath = Server.MapPath("Uploads") + "//CSV//" + Path.GetFileNameWithoutExtension(dbfPath) + ".csv";
File.WriteAllText(csvFilePath, csvData);
UploadCSV(csvFilePath);
}
}
}
}
public override object ConvertTo(ITypeDescriptorContext context, CultureInfo culture, object value, Type destinationType)
{
if (destinationType == null)
{
throw ADP.ArgumentNull("destinationType");
}
if (typeof(InstanceDescriptor) == destinationType)
{
OleDbConnectionStringBuilder options = value as OleDbConnectionStringBuilder;
if (options != null)
{
return(this.ConvertToInstanceDescriptor(options));
}
}
return(base.ConvertTo(context, culture, value, destinationType));
}
public override object ConvertTo(ITypeDescriptorContext context, CultureInfo culture, object value, Type destinationType)
{
if (destinationType == null)
{
throw ADP.ArgumentNull("destinationType");
}
if (typeof(System.ComponentModel.Design.Serialization.InstanceDescriptor) == destinationType)
{
OleDbConnectionStringBuilder obj = (value as OleDbConnectionStringBuilder);
if (null != obj)
{
return(ConvertToInstanceDescriptor(obj));
}
}
return(base.ConvertTo(context, culture, value, destinationType));
}
public static DbConnection DbConnectionFromConnectionString(string connectionString)
{
DbConnection connection = null;
OleDbConnectionStringBuilder builder = new System.Data.OleDb.OleDbConnectionStringBuilder(connectionString);
string provider = builder["Provider"].ToString();
if (provider.StartsWith("MSDASQL"))
{
//Provider=MSDASQL.1;Persist Security Info=False;Extended Properties="DSN=mysql2;SERVER=localhost;UID=root;DATABASE=sakila;PORT=3306";Initial Catalog=sakila
//Provider=MSDASQL.1;Persist Security Info=True;Data Source=mysql;Initial Catalog=sakila
//Provider=MSDASQL.1;Persist Security Info=False;Extended Properties="DSN=brCRM;DBQ=C:\tem\adb.mdb;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;UID=admin;"
//Extract the real ODBC connection string...to be able to use the OdbcConnection
string odbcConnectionString = "";
if (builder.ContainsKey("Extended Properties"))
{
odbcConnectionString = builder["Extended Properties"].ToString();
}
else if (builder.ContainsKey("Data Source") && !string.IsNullOrEmpty(builder["Data Source"].ToString()))
{
odbcConnectionString = "DSN=" + builder["Data Source"].ToString();
}
if (odbcConnectionString != "" && builder.ContainsKey("Initial Catalog"))
{
odbcConnectionString += ";DATABASE=" + builder["Initial Catalog"].ToString();
}
if (odbcConnectionString != "" && builder.ContainsKey("User ID"))
{
odbcConnectionString += ";UID=" + builder["User ID"].ToString();
}
if (odbcConnectionString != "" && builder.ContainsKey("Password"))
{
odbcConnectionString += ";PWD=" + builder["Password"].ToString();
}
connection = new OdbcConnection(odbcConnectionString);
}
else
{
connection = new OleDbConnection(connectionString);
}
return(connection);
}
private InstanceDescriptor ConvertToInstanceDescriptor(OleDbConnectionStringBuilder options)
{
Type[] types = new Type[] { typeof(string) };
return(new InstanceDescriptor(typeof(OleDbConnectionStringBuilder).GetConstructor(types), new object[] { options.ConnectionString }));
}
private System.ComponentModel.Design.Serialization.InstanceDescriptor ConvertToInstanceDescriptor(OleDbConnectionStringBuilder options)
{
Type[] ctorParams = new Type[] { typeof(string) };
object[] ctorValues = new object[] { options.ConnectionString };
System.Reflection.ConstructorInfo ctor = typeof(OleDbConnectionStringBuilder).GetConstructor(ctorParams);
return(new System.ComponentModel.Design.Serialization.InstanceDescriptor(ctor, ctorValues));
}
private DataTable ReadData()
{
string ConnectionString = "";
if (this.Context.FileType == ImportFileType.excelx || this.Context.FileType == ImportFileType.excel)
{
System.Data.OleDb.OleDbConnectionStringBuilder builder = new System.Data.OleDb.OleDbConnectionStringBuilder();
builder.DataSource = this.Context.DataLocation + this.Context.DataFilePath;
builder.Provider = "Microsoft.ACE.OLEDB.12.0";
if (this.Context.FileType == ImportFileType.excel)
{
builder.Add("Extended Properties", "Excel 8.0;HDR=Yes;IMEX=1");
}
else
{
builder.Add("Extended Properties", "Excel 12.0;HDR=Yes;IMEX=1");
}
ConnectionString = builder.ConnectionString;
var con = new System.Data.OleDb.OleDbConnection(ConnectionString);
System.Data.DataSet ds = new System.Data.DataSet();
System.Data.DataTable dt = new DataTable();
con.Open();
var dtSheets = new System.Data.DataTable();
dtSheets = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (dtSheets.Rows.Count == 0)
{
throw new Exception("No Sheet exist in the excel file");
}
else if (dtSheets.Rows[0].IsNull(2))
{
throw new Exception("No Data Avaiable");
}
System.Data.OleDb.OleDbDataAdapter MyCommand;
MyCommand = new System.Data.OleDb.OleDbDataAdapter("Select * from " + "[" + dtSheets.Rows[0][2] + "]", con);
// MyCommand.TableMappings.Add("Table", "TestTable");
MyCommand.Fill(ds);
dt = ds.Tables[0];
List <DataColumn> dc = new List <DataColumn>();
foreach (DataColumn c in dt.Columns)
{
if (c.DataType == typeof(string))
{
dc.Add(c);
}
}
con.Close();
con.Dispose();
foreach (DataColumn c in dc)
{
foreach (DataRow dr in dt.Rows)
{
if (dr[c.ColumnName] != null)
{
dr[c.ColumnName] = dr[c.ColumnName].ToString().Trim();
}
}
}
return(dt);
}
else if (this.Context.FileType == ImportFileType.csv)
{
ConnectionString = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + this.Context.DataLocation + this.Context.DataFilePath + ";Extensions=asc,csv,tab,txt;";
}
return(new DataTable());
}
public MyOledbHandler()
{
StringBuilder = new OleDbConnectionStringBuilder("provider=MSDAORA;USER ID=xxgs/xxgs; PERSIST SECURITY INFO=FALSE");
StringBuilder["Data Source"] = DataSource;
oDRDBConnectionString = StringBuilder.ConnectionString;
}
protected void Page_Load(object sender, EventArgs e)
{
System.Data.OleDb.OleDbConnectionStringBuilder objBuilder;
System.Data.OleDb.OleDbConnection objCon;
System.Data.OleDb.OleDbCommand objComm;
string strSelect = "";
//Create a connection to the database
objBuilder = new System.Data.OleDb.OleDbConnectionStringBuilder();
objBuilder.ConnectionString = "Data Source= C:\\BugData.mdb";
objBuilder.Add("Provider", "Microsoft.Jet.Oledb.4.0");
objBuilder.Add("Jet OLEDB:Database Password", "");
objBuilder.Add("Jet OLEDB:Database Locking Mode", 1);
objCon = new System.Data.OleDb.OleDbConnection(objBuilder.ConnectionString);
//Build the SQL Select command
//Note the Spaces are important!
strSelect = " Select ";
strSelect += " Tester, AppName, Build, DateReported, Description ";
strSelect += " From ReportedBugs";
Trace.Write(strSelect);
//Build a Command object to send your command
objComm = new System.Data.OleDb.OleDbCommand();
objComm.Connection = objCon;
objComm.CommandType = System.Data.CommandType.Text;
objComm.CommandText = strSelect;
//Response.Write(objComm.CommandText.ToString)
//Create a DataReader variable to referance the results
//we get back from the database.
System.Data.OleDb.OleDbDataReader objDR;
//Open the connection and run the command
try
{
objCon.Open();
objDR = objComm.ExecuteReader();
//Print out the results
Response.Write("<table border='1'>");
Response.Write("<tr>");
Response.Write("<th>Tester</th>");
Response.Write("<th>App Name</th>");
Response.Write("<th>Build</th>");
Response.Write("<th>DateReported</th>");
Response.Write("<th>Description</th>");
Response.Write("</tr>");
while (objDR.Read() == true)
{
Response.Write("<tr>");
Response.Write("<td>" + objDR["Tester"] + "</td>");
Response.Write("<td>" + objDR["AppName"] + "</td>");
Response.Write("<td>" + objDR["Build"] + "</td>");
Response.Write("<td>" + objDR["DateReported"] + "</td>");
Response.Write("<td>" + objDR["Description"] + "</td>");
Response.Write("</tr>");
}
Response.Write("</table>");
objDR.Close();
}
catch (Exception ex)
{
Response.Write(ex.ToString());
}
finally
{
if (objCon.State == System.Data.ConnectionState.Open)
{
objCon.Close();
}
}
}
