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);
}
}
}
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);
}
}
}
static void Main(string[] args)
{
//Using JET provider for xls files, because the newer ones do not work
DataTable table = new DataTable("scores");
string strAccessConn = string.Format(
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0;",
"../../Table.xls");
OleDbConnection dbCon = new OleDbConnection(strAccessConn);
using (dbCon)
{
string selectSql = @"SELECT * FROM [Sheet2$]";
using (OleDbDataAdapter adapter = new OleDbDataAdapter(selectSql, dbCon))
{
adapter.FillSchema(table, SchemaType.Source);
adapter.Fill(table);
}
}
// Console.WriteLine("Name, score:");
foreach (DataRow row in table.Rows)
{
foreach (var item in row.ItemArray)
{
Console.Write(item + " ");
}
Console.WriteLine();
}
}
public void GetTracks()
{
var dt = new DataTable("Track");
using (var conn = new OleDbConnection())
{
conn.ConnectionString = GetConnectionString(Filename);
conn.Open();
var commandText = string.Format("SELECT * FROM [{0}${1}]", SheetName, SheetRange);
using (var cmd = new OleDbCommand(commandText, conn))
{
var adapter = new OleDbDataAdapter();
adapter.SelectCommand = cmd;
adapter.FillSchema(dt, SchemaType.Source);
adapter.Fill(dt);
}
}
_reservedColumnsCount = 0;
_tracks = new List<string>();
foreach (DataColumn col in dt.Columns)
{
if (!MetadataFileFormat.GetReservedColumnNames().Contains(col.ColumnName))
_tracks.Add(col.ColumnName);
else
_reservedColumnsCount++;
}
}
private static void ReadExcelsFromDirectory(string filePath, List<SellsReport> reports)
{
DataTable dt = new DataTable("newtable");
using (OleDbConnection connection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=Excel 12.0;"))
{
connection.Open();
string selectSql = @"SELECT * FROM [Sales$]";
using (OleDbDataAdapter adapter = new OleDbDataAdapter(selectSql, connection))
{
adapter.FillSchema(dt, SchemaType.Source);
adapter.Fill(dt);
}
connection.Close();
}
for (int i = 2; i < dt.Rows.Count - 2; i++)
{
string location = dt.Rows[0][0].ToString();
SellsReport report = new SellsReport()
{
Location = location,
ProductID = int.Parse(dt.Rows[i][0].ToString()),
Quantity = int.Parse(dt.Rows[i][1].ToString()),
UnitPrice = decimal.Parse(dt.Rows[i][2].ToString())
};
reports.Add(report);
}
}
private static void ReadExcelsFromDirectory(string filePath)
{
DataTable dt = new DataTable("newtable");
using (OleDbConnection connection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0;HDR=NO;IMEX=1\""))
{
connection.Open();
string selectSql = @"SELECT * FROM [Sales$]";
using (OleDbDataAdapter adapter = new OleDbDataAdapter(selectSql, connection))
{
adapter.FillSchema(dt, SchemaType.Source);
adapter.Fill(dt);
}
connection.Close();
}
string location = dt.Rows[1][0].ToString();
SuperMarket newSupermarket = new SuperMarket()
{
Name = location,
};
for (int i = 3; i < dt.Rows.Count - 1; i++)
{
int prodId = 0;
string productId = dt.Rows[i][0].ToString();
int.TryParse(productId, out prodId);
if (prodId > 0)
{
using (var ctx = new SupermarketEntities())
{
if (ctx.Products.Find(prodId) != null)
{
var supermarket = ctx.SuperMarkets.Where(s => s.Name == newSupermarket.Name).ToList();
if (supermarket.Count == 0)
{
ctx.SuperMarkets.Add(newSupermarket);
supermarket.Add(newSupermarket);
}
Sale newSale = new Sale()
{
ProductId = prodId,
SuperMarketId = supermarket[0].Id,
Date = DateTime.Now,
Quantity = int.Parse(dt.Rows[i][1].ToString()),
Price = decimal.Parse(dt.Rows[i][2].ToString()),
Sum = decimal.Parse(dt.Rows[i][3].ToString())
};
ctx.Sales.Add(newSale);
ctx.SaveChanges();
}
}
}
}
}
private static List<SaleInfo> ReadCurrentExcel(string path)
{
List<SaleInfo> allSales = new List<SaleInfo>();
using (OleDbConnection conn = new OleDbConnection(path))
{
conn.Open();
string command = @"select * from [Sales$]";
OleDbDataAdapter adapter = new OleDbDataAdapter(command, conn);
DataTable table = new DataTable();
using (adapter)
{
adapter.FillSchema(table, SchemaType.Source);
adapter.Fill(table);
}
int counter = 0;
string[] splittedName = path.Split(new string[] { "-Sales-Report-" }, StringSplitOptions.RemoveEmptyEntries);
string location = splittedName[0].Substring(splittedName[0].LastIndexOf('\\'));
int dotIndex = splittedName[1].IndexOf('.');
string date = splittedName[1].Substring(0, dotIndex);
foreach (DataRow row in table.Rows)
{
SaleInfo currentSale = new SaleInfo();
List<decimal> info = new List<decimal>();
bool getInside = false;
foreach (DataColumn col in table.Columns)
{
if (row[col].ToString()!="")
{
info.Add(decimal.Parse(row[col].ToString()));
// Console.Write(row[col].ToString() + " ");
getInside = true;
}
}
if (getInside && counter != table.Rows.Count-1)
{
currentSale.ProductId = (int)info[0];
currentSale.Quantity = (int)info[1];
currentSale.UnitPrice = info[2];
currentSale.Sum = info[3];
currentSale.Location = location;
currentSale.SaleDate = DateTime.ParseExact(date, "dd-MMM-yyyy", CultureInfo.InvariantCulture);
allSales.Add(currentSale);
}
counter++;
}
}
return allSales;
}
void ReadExcel()
{
var _oleConn = new OleDbConnection(ExcelConnection());
_oleConn.Open();
var _oleCmdSelect = new OleDbCommand("SELECT * FROM Sheet", _oleConn);
OleDbDataAdapter oleAdapter = new OleDbDataAdapter();
oleAdapter.SelectCommand = _oleCmdSelect;
DataTable dt = new DataTable("stock");
oleAdapter.FillSchema(dt, SchemaType.Source);
oleAdapter.Fill(dt);
gridControl1.DataSource = dt;
// select * from [sheetname$[range]
}
public DataSet GetWorkplace()
{
DataSet workplace;
OleDbConnection connection = new OleDbConnection(strConnection);
OleDbDataAdapter adaptor = new OleDbDataAdapter("SELECT * FROM *", connection);
workplace = new DataSet();
adaptor.FillSchema(workplace, SchemaType.Source);
adaptor.Fill(workplace);
adaptor.Dispose();
connection.Close();
return workplace;
}
static void Main(string[] args)
{
string filePath = @"D:\t\edp\Processed\mastinv_47100_4627900.xls";
string connectionstring = @"Provider=Microsoft.Jet.OleDb.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0;";
DataTable table = new DataTable();
OleDbConnection con = new OleDbConnection(connectionstring);
con.Open();
OleDbDataAdapter adap = new OleDbDataAdapter("Select * from [data$]", con);
OleDbCommand InsertC = new OleDbCommand("insert into [data$]([File name], [client], [batch], [scac], [carracct], [invoice number], [invoice date], [currency], [billed amount], [vat]) values('123', '123', '123', '123', '123', '123', '123','123', '123', '1234')", con);
adap.InsertCommand = InsertC;
adap.FillSchema(table, SchemaType.Mapped);
adap.UpdateCommand = new OleDbCommand("update [data$] set batch = 'aasdf' where [file name] = '47100_4627900_00004.PDF'", con);
//adap.UpdateCommand.ExecuteNonQuery();
adap.InsertCommand.ExecuteNonQuery();
con.Close();
}
public DataTable Resultado(string script, string tabla)
{
string sql = "";
if(script == null) sql = "SELECT * FROM " + tabla;
else sql = script;
if (Conectar())
{
this.oda = new OleDbDataAdapter(sql, this.conn);
ds = new DataSet();
oda.FillSchema(ds, SchemaType.Source, tabla);
oda.Fill(ds, tabla);
dt = ds.Tables[tabla];
Desconectar();
return dt;
}
else { return null; }
}
private static void ReadExcelsFromDirectory(string filePath)
{
DataTable dt = new DataTable("newtable");
using (OleDbConnection connection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=Excel 12.0;"))
{
connection.Open();
string selectSql = @"SELECT * FROM [Sales$]";
using (OleDbDataAdapter adapter = new OleDbDataAdapter(selectSql, connection))
{
adapter.FillSchema(dt, SchemaType.Source);
adapter.Fill(dt);
}
connection.Close();
}
List<SellsReport> repotst = new List<SellsReport>();
for (int i = 2; i < dt.Rows.Count-2; i++)
{
string location = dt.Rows[0][0].ToString();
SellsReport report = new SellsReport()
{
Location = location,
ProductID = int.Parse(dt.Rows[i][0].ToString()),
Quantity = int.Parse(dt.Rows[i][1].ToString()),
UnitPrice = decimal.Parse(dt.Rows[i][2].ToString())
};
repotst.Add(report);
}
foreach (var item in repotst)
{
Console.WriteLine(item.UnitPrice);
}
//foreach (DataRow row in dt.Rows)
//{
// for (int i = 0; i < row.ItemArray.Length; i++)
// {
// Console.Write(row.ItemArray[i].ToString()+" ");
// }
// Console.WriteLine();
//}
}
//private static readonly int m_maxSheelSize = 65000;
#region 公用静态方法
#region 从Excel读数据
/// <summary>
/// 从Excel读数据
/// </summary>
/// <param name="filePath">excel文档路径</param>
/// <param name="excelVersion">文档版本</param>
/// <param name="pHDR">第一行是否标题</param>
/// <param name="bMerge">
/// 如果有多页，是否合并数据，合并时必须保证多页的表结构一致
/// </param>
/// <returns>DataTable集</returns>
public static DataTable[] GetExcelData(string filePath, ExcelVersion excelVersion, HeadRowType pHDR, bool bMerge)
{
List<DataTable> dtResult = new List<DataTable>();
string connectionString = string.Format(GetConnectionString(excelVersion, ImportOrExportType.Import),
filePath, pHDR);
using (OleDbConnection con = new OleDbConnection(connectionString))
{
con.Open();
string[] sheels = GetExcelWorkSheets(filePath, excelVersion);
foreach (string sheelName in sheels)
{
try
{
DataTable dtExcel = new DataTable();
OleDbDataAdapter adapter = new OleDbDataAdapter("Select * from [" + sheelName + "$]", con);
adapter.FillSchema(dtExcel, SchemaType.Mapped);
adapter.Fill(dtExcel);
dtExcel.TableName = sheelName;
dtResult.Add(dtExcel);
}
catch
{
//容错处理：取不到时，不报错，结果集为空即可。
}
}
//如果需要合并数据，则合并到第一张表
if (bMerge)
{
for (int i = 1; i < dtResult.Count; i++)
{
//如果不为空才合并
if (dtResult[0].Columns.Count == dtResult[i].Columns.Count &&
dtResult[i].Rows.Count > 0)
{
dtResult[0].Load(dtResult[i].CreateDataReader());
}
}
}
}
return dtResult.ToArray();
}
private static void InsertDataFromXls(string filePath, string dateString)
{
DataTable dt = new DataTable("newtable");
OleDbConnectionStringBuilder csbuilder = new OleDbConnectionStringBuilder();
csbuilder.Provider = "Microsoft.ACE.OLEDB.12.0";
csbuilder.DataSource = filePath;
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();
}
string[] dateParts = dateString.Split('-');
int day = int.Parse(dateParts[0]);
int month = GetMonthAsInt(dateParts[1]);
int year = int.Parse(dateParts[2]);
DateTime reportDate = new DateTime(year, month, day);
int rowsCount = dt.Rows.Count - 1;
for (int i = 2; i < rowsCount; i++)
{
Report report = new Report
{
ProductId = Convert.ToInt32(dt.Rows[i].ItemArray[0]),
Quantity = Convert.ToInt32(dt.Rows[i].ItemArray[1]),
UnitPrice = Convert.ToDecimal(dt.Rows[i].ItemArray[2]),
Sum = Convert.ToDecimal(dt.Rows[i].ItemArray[3]),
Date = reportDate
};
string locationName = dt.Rows[0].ItemArray[0].ToString();
InsertInSqlServer(report, locationName);
}
}
public DataTable ExcelToDataTable(string excelFileName)
{
System.Configuration.AppSettingsReader app = new System.Configuration.AppSettingsReader();
String provider = (String)app.GetValue("Provider", typeof(String));
String extProperties = (String)app.GetValue("Extended Properties", typeof(String));
string connString = "Provider=" + provider + ";Data Source=" + excelFileName + ";Extended Properties='" + extProperties + "'";
DataTable dtExceldata = new DataTable();
try
{
OleDbCommand excelCommand = new OleDbCommand();
OleDbDataAdapter excelDataAdapter = new OleDbDataAdapter();
using (OleDbConnection excelConn = new OleDbConnection(connString))
{
excelConn.Open();
String sheetName = "";
DataTable dtExcelSheets = new DataTable();
dtExcelSheets = excelConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (dtExcelSheets.Rows.Count > 0)
{
sheetName = dtExcelSheets.Rows[0]["TABLE_NAME"].ToString();
}
OleDbCommand OleCmdSelect = new OleDbCommand("SELECT * FROM [" + sheetName + "]", excelConn);
OleDbDataAdapter OleAdapter = new OleDbDataAdapter(OleCmdSelect);
OleAdapter.FillSchema(dtExceldata, System.Data.SchemaType.Source);
OleAdapter.Fill(dtExceldata);
excelConn.Close();
}
return dtExceldata;
}
catch (Exception ex)
{
return null;
}
}
static void Main()
{
DataTable dt = new DataTable("newtable");
using (OleDbConnection connection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=../../../task.xlsx;Extended Properties=Excel 12.0;"))
{
connection.Open();
string selectSql = @"SELECT * FROM [Sheet1$]";
using (OleDbDataAdapter adapter = new OleDbDataAdapter(selectSql, connection))
{
adapter.FillSchema(dt, SchemaType.Source);
adapter.Fill(dt);
}
connection.Close();
}
foreach (DataRow row in dt.Rows)
{
Console.WriteLine(row.ItemArray[0]+" - " + row.ItemArray[1]);
}
}
// Загрузка в DataSet
public static void BuildDataSet(ref OleDbConnection connect, ref DataSet ds, ref OleDbDataAdapter[] adapt)
{
OleDbCommand[] commands = new OleDbCommand[3];
commands[0] = new OleDbCommand("SELECT * from Tovar", connect);
daTovar = new OleDbDataAdapter(commands[0]);
daTovar.FillSchema(ds, SchemaType.Source, "Tovar");
daTovar.Fill(ds, "Tovar");
adapt[0] = daTovar;
commands[1] = new OleDbCommand("SELECT * from Pokupatel", connect);
daPokupatel = new OleDbDataAdapter(commands[1]);
daPokupatel.FillSchema(ds, SchemaType.Source, "Pokupatel");
daPokupatel.Fill(ds, "Pokupatel");
adapt[1] = daPokupatel;
commands[2] = new OleDbCommand("SELECT * from Zakaz", connect);
daZakaz = new OleDbDataAdapter(commands[2]);
daZakaz.FillSchema(ds, SchemaType.Source, "Zakaz");
daZakaz.Fill(ds, "Zakaz");
adapt[2] = daZakaz;
}
private void NoIdTeacher()
{
SqlHelper help = new SqlHelper();
DataTable dtClass = help.getDs("select * from Classes_Data","Classes_Data").Tables[0];
DataRow[] NoIDteacher = dtClass.Select("Teacher_ID='" + "0000000000" + "' ");
daTeachers = help.adapter("select * from Teachers_Data");
dtTeachers = new System.Data.DataTable();
daTeachers.Fill(dtTeachers);
daTeachers.FillSchema(dtTeachers, SchemaType.Source);
List<string> teachers = new List<string> { };
DataTable Idteacher_dt = dtTeachers.Copy(); // 获取Class_Data的架构
Idteacher_dt.Clear();
for (int i = 0; i < NoIDteacher.Length; i++)
{
teachers.Add(NoIDteacher[i][2].ToString());
}
string[] newteachers = teachers.Distinct<string>().ToArray();
teachers.Clear();
for (int i = 0; i < dtTeachers.Rows.Count;i++ )
{
teachers.Add(dtTeachers.Rows[i][1].ToString());
}
string[] oldteachers = teachers.Distinct<string>().ToArray();
for (int z = 0; z < newteachers.Length; z++)
{
for (int i = 0; i < oldteachers.Length;i++ )
{
if (newteachers[z].ToString().Equals(oldteachers[i].ToString()))
{
newteachers[z] = "";
}
}
}
for (int i = 0; i < newteachers.Length;i++ )
{ if(newteachers[i]!=""){
DataRow dr_teacher = Idteacher_dt.NewRow();
dr_teacher[1] = newteachers[i].ToString();
dr_teacher[0] = newteachers[i].ToString();
dr_teacher[7] = 0;
dr_teacher[8] = 0;
dr_teacher[9] = 0;
dr_teacher[10] = 0;
Idteacher_dt.Rows.Add(dr_teacher);
}
}
dtTeachers.Merge(Idteacher_dt, true);
daTeachers.Update(dtTeachers);
}
//=====================================================================
/// <summary>
/// Create the data source for the demo
/// </summary>
/// <remarks>You can use the designer to create the data source and use strongly typed data sets. For
/// this demo, we'll do it by hand.
/// </remarks>
private void CreateDataSource()
{
// The test database should be in the project folder
dbConn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=.\TestData.mdb");
daAddresses = new OleDbDataAdapter();
daPhones = new OleDbDataAdapter();
dsAddresses = new DataSet();
// Set the table name
daAddresses.TableMappings.Add("Table", "Addresses");
// In a real application we wouldn't use literal SQL but we will for the demo
daAddresses.SelectCommand = new OleDbCommand("Select * From Addresses Order By LastName", dbConn);
daAddresses.DeleteCommand = new OleDbCommand("Delete From Addresses Where ID = @paramID", dbConn);
daAddresses.DeleteCommand.Parameters.Add(new OleDbParameter("@paramID", OleDbType.Integer, 0,
ParameterDirection.Input, false, 0, 0, "ID", DataRowVersion.Original, null));
daAddresses.InsertCommand = new OleDbCommand(
"INSERT INTO Addresses (FirstName, LastName, Address, City, State, Zip, SumValue, Domestic, " +
"International, Postal, Parcel, Home, Business, ContactType) " +
"VALUES (@paramFN, @paramLN, @paramAddress, @paramCity, @paramState, @paramZip, " +
"@paramSumValue, @paramDomestic, @paramInternational, @paramPostal, @paramParcel, " +
"@paramHome, @paramBusiness, @paramContactType)", dbConn);
daAddresses.InsertCommand.Parameters.Add(new OleDbParameter("@paramFirstName", OleDbType.VarWChar,
20, "FirstName"));
daAddresses.InsertCommand.Parameters.Add(new OleDbParameter("@paramLastName", OleDbType.VarWChar,
30, "LastName"));
daAddresses.InsertCommand.Parameters.Add(new OleDbParameter("@paramAddress", OleDbType.VarWChar,
50, "Address"));
daAddresses.InsertCommand.Parameters.Add(new OleDbParameter("@paramCity", OleDbType.VarWChar, 20,
"City"));
daAddresses.InsertCommand.Parameters.Add(new OleDbParameter("@paramState", OleDbType.VarWChar, 2,
"State"));
daAddresses.InsertCommand.Parameters.Add(new OleDbParameter("@paramZip", OleDbType.VarWChar, 10,
"Zip"));
daAddresses.InsertCommand.Parameters.Add(new OleDbParameter("@paramSumValue", OleDbType.Integer, 0,
"SumValue"));
daAddresses.InsertCommand.Parameters.Add(new OleDbParameter("@paramDomestic", OleDbType.Boolean, 0,
"Domestic"));
daAddresses.InsertCommand.Parameters.Add(new OleDbParameter("@paramInternational", OleDbType.Boolean,
0, "International"));
daAddresses.InsertCommand.Parameters.Add(new OleDbParameter("@paramPostal", OleDbType.Boolean, 0,
"Postal"));
daAddresses.InsertCommand.Parameters.Add(new OleDbParameter("@paramParcel", OleDbType.Boolean, 0,
"Parcel"));
daAddresses.InsertCommand.Parameters.Add(new OleDbParameter("@paramHome", OleDbType.Boolean, 0,
"Home"));
daAddresses.InsertCommand.Parameters.Add(new OleDbParameter("@paramBusiness", OleDbType.Boolean, 0,
"Business"));
daAddresses.InsertCommand.Parameters.Add(new OleDbParameter("@paramContactType", OleDbType.Char, 1,
"ContactType"));
daAddresses.UpdateCommand = new OleDbCommand(
"UPDATE Addresses SET FirstName = @paramFirstName, LastName = @paramLastName, " +
"Address = @paramAddress, City = @paramCity, State = @paramState, Zip = @paramZip, " +
"SumValue = @paramSumValue, Domestic = @paramDomestic, International = @paramInternational, " +
"Postal = @paramPostal, Parcel = @paramParcel, Home = @paramHome, Business = @paramBusiness, " +
"ContactType = @paramContactType WHERE ID = @paramID", dbConn);
daAddresses.UpdateCommand.Parameters.Add(new OleDbParameter("@paramFirstName", OleDbType.VarWChar,
20, "FirstName"));
daAddresses.UpdateCommand.Parameters.Add(new OleDbParameter("@paramLastName", OleDbType.VarWChar, 30,
"LastName"));
daAddresses.UpdateCommand.Parameters.Add(new OleDbParameter("@paramAddress", OleDbType.VarWChar, 50,
"Address"));
daAddresses.UpdateCommand.Parameters.Add(new OleDbParameter("@paramCity", OleDbType.VarWChar, 20,
"City"));
daAddresses.UpdateCommand.Parameters.Add(new OleDbParameter("@paramState", OleDbType.VarWChar, 2,
"State"));
daAddresses.UpdateCommand.Parameters.Add(new OleDbParameter("@paramZip", OleDbType.VarWChar, 10,
"Zip"));
daAddresses.UpdateCommand.Parameters.Add(new OleDbParameter("@paramSumValue", OleDbType.Integer, 0,
"SumValue"));
daAddresses.UpdateCommand.Parameters.Add(new OleDbParameter("@paramDomestic", OleDbType.Boolean, 0,
"Domestic"));
daAddresses.UpdateCommand.Parameters.Add(new OleDbParameter("@paramInternational", OleDbType.Boolean,
0, "International"));
daAddresses.UpdateCommand.Parameters.Add(new OleDbParameter("@paramPostal", OleDbType.Boolean, 0,
"Postal"));
daAddresses.UpdateCommand.Parameters.Add(new OleDbParameter("@paramParcel", OleDbType.Boolean, 0,
"Parcel"));
daAddresses.UpdateCommand.Parameters.Add(new OleDbParameter("@paramHome", OleDbType.Boolean, 0,
"Home"));
daAddresses.UpdateCommand.Parameters.Add(new OleDbParameter("@paramBusiness", OleDbType.Boolean, 0,
"Business"));
daAddresses.UpdateCommand.Parameters.Add(new OleDbParameter("@paramContactType", OleDbType.Char, 1,
"ContactType"));
daAddresses.UpdateCommand.Parameters.Add(new OleDbParameter("@paramID", OleDbType.Integer, 0,
ParameterDirection.Input, false, 0, 0, "ID", System.Data.DataRowVersion.Original, null));
// Fill in the schema for auto-increment etc.
daAddresses.FillSchema(dsAddresses, SchemaType.Mapped);
// Bind the controls to the data source. Since we are using a data set, we need to use fully
// qualified names.
txtFName.DataBindings.Add(new Binding("Text", dsAddresses, "Addresses.FirstName"));
txtLName.DataBindings.Add(new Binding("Text", dsAddresses, "Addresses.LastName"));
txtAddress.DataBindings.Add(new Binding("Text", dsAddresses, "Addresses.Address"));
txtCity.DataBindings.Add(new Binding("Text", dsAddresses, "Addresses.City"));
cboState.DataBindings.Add(new Binding("SelectedValue", dsAddresses, "Addresses.State"));
txtZip.DataBindings.Add(new Binding("Text", dsAddresses, "Addresses.Zip"));
lblKey.DataBindings.Add(new Binding("Text", dsAddresses, "Addresses.ID"));
// Connect the Row Updated event so that we can retrieve the new primary key values as they are
// identity values.
daAddresses.RowUpdated += daAddresses_RowUpdated;
// The checkboxes in the checkbox list can also be bound to members of a different data source. To
// do this, set the BindingMembersDataSource and specify the members to which each checkbox is bound
// in the BindingMembers collection property. In this demo, the BindingMembers are specified via the
// designer. However, we could do it in code as well as shown here:
//
// cblAddressTypes.BindingMembers.AddRange(new string[] { "Addresses.Domestic",
// "Addresses.International", "Addresses.Postal", "Addresses.Parcel", "Addresses.Home",
// "Addresses.Business"});
//
// As above, since we are binding to a DataSet, we must specify the fully qualified names. Also note
// that there is a BindingMembersBindingContext property that can be set if the binding members data
// source is in a binding context different than the checkbox list's data source.
//
// Note that we could assign a data source for the checkbox list items as well similar to the radio
// button list data source below but in this case, the list is simple so it's added to the Items
// collection via the designer.
cblAddressTypes.BindingMembersDataSource = dsAddresses;
// Create the data source for the radio button list items
List<ListItem> contactTypeList = new List<ListItem>();
contactTypeList.Add(new ListItem("B", "Business"));
contactTypeList.Add(new ListItem("P", "Personal"));
contactTypeList.Add(new ListItem("O", "Other"));
rblContactType.DisplayMember = "Display";
rblContactType.ValueMember = "Value";
rblContactType.DataSource = contactTypeList;
// Bind the radio button list to the ContactType field. Since it can be null, we'll add a Parse
// event to default the value to "Business" if it's null. This wouldn't be needed for fields that
// are never null (i.e. those with a default value).
Binding b = new Binding("SelectedValue", dsAddresses, "Addresses.ContactType");
b.Format += ContactType_Format;
rblContactType.DataBindings.Add(b);
// Set up the phone info data adapter
daPhones.SelectCommand = new OleDbCommand("Select * From Phones Order By ID, PhoneNumber", dbConn);
daPhones.DeleteCommand = new OleDbCommand("Delete From Phones Where PhoneKey = @paramPhoneKey", dbConn);
daPhones.DeleteCommand.Parameters.Add(new OleDbParameter("@paramPhoneKey", OleDbType.Integer, 0,
ParameterDirection.Input, false, 0, 0, "PhoneKey", DataRowVersion.Original, null));
daPhones.InsertCommand = new OleDbCommand("INSERT INTO Phones (ID, PhoneNumber) VALUES (@paramID, " +
"@paramPhoneNumber)", dbConn);
daPhones.InsertCommand.Parameters.Add(new OleDbParameter("@paramID", OleDbType.Integer, 0, "ID"));
daPhones.InsertCommand.Parameters.Add(new OleDbParameter("@paramPhoneNumber", OleDbType.VarWChar, 20,
"PhoneNumber"));
daPhones.UpdateCommand = new OleDbCommand(
"UPDATE Phones SET PhoneNumber = @paramPhoneNumber WHERE PhoneKey = @paramPhoneKey", dbConn);
daPhones.UpdateCommand.Parameters.Add(new OleDbParameter("@paramPhoneNumber", OleDbType.VarWChar, 20,
"PhoneNumber"));
daPhones.UpdateCommand.Parameters.Add(new OleDbParameter("@paramPhoneKey", OleDbType.Integer, 0,
ParameterDirection.Input, false, 0, 0, "PhoneKey", DataRowVersion.Original, null));
// Connect the Row Updated event so that we can retrieve the new primary key values as they are
// identity values.
daPhones.RowUpdated += daPhones_RowUpdated;
// Load the state codes for the row template's shared data source
OleDbDataAdapter daStates = new OleDbDataAdapter("Select State, StateDesc From States", dbConn);
DataTable dtStates = new DataTable();
daStates.Fill(dtStates);
// Add a blank row to allow no selection
dtStates.Rows.InsertAt(dtStates.NewRow(), 0);
cboState.DisplayMember = cboState.ValueMember = "State";
cboState.DataSource = dtStates.DefaultView;
}
public void TestLongSqlExpression()
{
BeginCase("Long SQL string cause java.lang.StackOverflowError (Test case for bug #4708)");
StringBuilder querySb = new StringBuilder();
querySb.Append("SELECT ");
querySb.Append("c.CustomerID as ci1, c.CustomerID as ci2, c.CustomerID as ci3, c.CustomerID as ci4, ");
querySb.Append("c.CompanyName as cn1, c.CompanyName as cn2, c.CompanyName as cn3, c.CompanyName as cn4, ");
querySb.Append("c.ContactName as cntn1, c.ContactName as cntn2, c.ContactName as cntn3, c.ContactName as cntn4, ");
querySb.Append("c.ContactTitle as ct1, c.ContactTitle as ct2, c.ContactTitle as ct3, c.ContactTitle as ct4, ");
querySb.Append("c.Address as ad1, c.Address as ad2, c.Address as ad3, c.Address as ad4, ");
querySb.Append("c.City as ct1, c.City as ct2, c.City as ct3, c.City as ct4, ");
querySb.Append("c.Region as rg1, c.Region as rg2, c.Region as rg3, c.Region as rg4, ");
querySb.Append("c.PostalCode as pc1, c.PostalCode as pc2, c.PostalCode as pc3, c.PostalCode as pc4, ");
querySb.Append("c.Country as co1, c.Country as co2, c.Country as co3, c.Country as co4, ");
querySb.Append("c.Phone as ph1, c.Phone as ph2, c.Phone as ph3, c.Phone as ph4, ");
querySb.Append("c.Fax as fx1, c.Fax as fx2, c.Fax as fx3, c.Fax as fx4 ");
querySb.Append("FROM Customers c");
OleDbDataAdapter adapter = null;
DataSet schemaDs = new DataSet();
try
{
using(adapter = new OleDbDataAdapter(querySb.ToString(), this.connectionString))
{
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
adapter.FillSchema(schemaDs, SchemaType.Source);
Compare(schemaDs.Tables.Count, 1);
}
}
catch(Exception ex)
{
exp = ex;
}
finally
{
EndCase(exp);
exp = null;
}
}
protected DataTable LoadDataTable()
{
RaiseProgressStatus("Reading Xls file");
var dt = new DataTable("Metadata");
using (var conn = new OleDbConnection())
{
conn.ConnectionString = GetConnectionString(Filename);
conn.Open();
string commandText = null;
if (string.IsNullOrEmpty(SheetRange))
commandText = string.Format("SELECT * FROM [{0}$]", SheetName);
else
commandText = string.Format("SELECT * FROM [{0}]${1}", SheetName, string.IsNullOrEmpty(SheetRange) ? "A1:AZ1" : SheetRange);
using (var cmd = new OleDbCommand(commandText, conn))
{
var adapter = new OleDbDataAdapter();
adapter.SelectCommand = cmd;
adapter.FillSchema(dt, SchemaType.Source);
adapter.Fill(dt);
}
}
return dt;
}
public static DataTable ConvertExcelToDataTable(string fileName, string sheetName)
{
using (var objConn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';"))
{
objConn.Open();
var cmd = new OleDbCommand();
var dtable = new DataTable();
var dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (dt != null)
{
var tempDataTable = (from dataRow in dt.AsEnumerable()
where !dataRow["TABLE_NAME"].ToString().Contains("FilterDatabase")
select dataRow).CopyToDataTable();
dt = tempDataTable;
var totalSheet = dt.Rows.Count; //No of sheets on excel file
for (var i = 0; i < totalSheet; i++)
{
var name = dt.Rows[i]["TABLE_NAME"].ToString();
if (name.Contains(sheetName))
{
sheetName = name;
break;
}
}
}
cmd.Connection = objConn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT * FROM [" + sheetName + "]";
var oleda = new OleDbDataAdapter(cmd);
oleda.FillSchema(dtable, SchemaType.Source);
foreach (DataColumn cl in dtable.Columns)
{
if (cl.DataType == typeof(double))
cl.DataType = typeof(decimal);
if (cl.ColumnName.ToUpper().Contains("AMT"))
cl.DataType = typeof(decimal);
if (cl.ColumnName.ToUpper().Contains("DATE"))
cl.DataType = typeof(DateTime);
}
foreach (DataRow dr in dtable.Rows)
{
foreach (DataColumn dc in dtable.Columns)
{
if (dc.ColumnName.ToUpper().Contains("DATE"))
{
dr[dc.ColumnName] = DateTime.ParseExact(dr[dc.ColumnName].ToString(), "dd-MM-yyyy", new CultureInfo(ConfigurationManager.AppSettings["CultureInfo"].ToString()));
}
}
}
oleda.Fill(dtable);
objConn.Close();
return dtable;
}
}
public DataTable GetWorksheet(string worksheet)
{
DataTable ws;
OleDbConnection connection = new OleDbConnection(strConnection);
OleDbDataAdapter adaptor = new OleDbDataAdapter(String.Format("SELECT * FROM [{0}$]", worksheet), connection);
ws = new DataTable(worksheet);
adaptor.FillSchema(ws, SchemaType.Source);
adaptor.Fill(ws);
adaptor.Dispose();
connection.Close();
return ws;
}
/// <summary>
/// Gets an worksheet as a data table.
/// </summary>
///
public DataTable GetWorksheet(string worksheet)
{
DataTable ws;
using (OleDbConnection connection = new OleDbConnection(strConnection))
{
OleDbCommand command = new OleDbCommand("SELECT * FROM [" + worksheet + "$]", connection);
using (OleDbDataAdapter adaptor = new OleDbDataAdapter(command))
{
ws = new DataTable(worksheet);
ws.Locale = CultureInfo.InvariantCulture;
adaptor.FillSchema(ws, SchemaType.Source);
adaptor.Fill(ws);
}
}
return ws;
}
public DataTable GetTable(string strTableName)
{
try
{
//Open and query
if (_oleConn ==null) Open();
if (_oleConn.State != ConnectionState.Open)
throw new Exception("Connection cannot open error.");
if (SetSheetQuerySelect()==false) return null;
//Fill table
OleDbDataAdapter oleAdapter = new OleDbDataAdapter();
oleAdapter.SelectCommand = _oleCmdSelect;
DataTable dt = new DataTable(strTableName);
oleAdapter.FillSchema(dt,SchemaType.Source);
oleAdapter.Fill(dt);
if (this.Headers ==false)
{
if (_strSheetRange.IndexOf(":")>0)
{
string FirstCol = _strSheetRange.Substring(0,_strSheetRange.IndexOf(":")-1);
int intCol = this.ColNumber(FirstCol);
for (int intI=0;intI<dt.Columns.Count;intI++)
{
dt.Columns[intI].Caption =ColName(intCol+intI);
}
}
}
SetPrimaryKey(dt);
//Cannot delete rows in Excel workbook
dt.DefaultView.AllowDelete =false;
//Clean up
_oleCmdSelect.Dispose();
_oleCmdSelect=null;
oleAdapter.Dispose();
oleAdapter=null;
if (KeepConnectionOpen==false) Close();
return dt;
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Update one row
/// </summary>
/// <param name="pFileString">File String</param>
/// <param name="pUpdateSQL">Update SQL Statement</param>
/// <param name="pCommand">Command</param>
/// <returns>bool</returns>
public static bool Update_1_Row(string pFileString, string pSelectSQL, string pKeyString, System.Data.Common.DbDataReader pDataReader)
{
bool result = false;
System.Data.SqlClient.SqlConnection Conn = null;
System.Data.OleDb.OleDbConnection ConnOle = null;
System.Data.SqlClient.SqlDataAdapter Adapter = null;
System.Data.OleDb.OleDbDataAdapter AdapterOle = null;
System.Data.SqlClient.SqlCommandBuilder builderSQL = null;
System.Data.OleDb.OleDbCommandBuilder builderOLE = null;
System.Data.SqlClient.SqlCommand cmdSqL = null;
System.Data.Common.DbCommand cmdOle = null;
DataSet dataSet = new DataSet();
DataTable Temp = new DataTable();
string ConnectionString = DBReadExecute.ParseConnectionString(pFileString);
StringBuilder UpdateSQL = new StringBuilder();
try
{
Type SQLServerType = Type.GetType("Epi.Data.SqlServer.SqlDBFactory, Epi.Data.SqlServer");
if (DBReadExecute.DataSource.GetType().AssemblyQualifiedName == SQLServerType.AssemblyQualifiedName)
{
//case DBReadExecute.enumDataSouce.SQLServer:
Conn = new System.Data.SqlClient.SqlConnection(ConnectionString);
Adapter = new System.Data.SqlClient.SqlDataAdapter(pSelectSQL, Conn);
//Adapter.FillSchema(dataSet, SchemaType.Source, pDestinationTableName);
Adapter.FillSchema(dataSet, SchemaType.Source);
builderSQL = new System.Data.SqlClient.SqlCommandBuilder(Adapter);
Conn.Open();
cmdSqL = Conn.CreateCommand();
cmdSqL = builderSQL.GetInsertCommand();
cmdSqL.CommandTimeout = 1500;
UpdateSQL.Append("Update ");
UpdateSQL.Append(pSelectSQL.Replace("Select * From ", ""));
UpdateSQL.Append(" Set ");
foreach (System.Data.SqlClient.SqlParameter param in cmdSqL.Parameters)
{
//string FieldName = param.ParameterName.TrimStart(new char[] { '@' });
string FieldName = param.SourceColumn;
try
{
StringBuilder TUpdateSQL = new StringBuilder();
if (pDataReader[FieldName] != DBNull.Value && !string.IsNullOrEmpty(pDataReader[FieldName].ToString()))
{
TUpdateSQL.Append("[");
TUpdateSQL.Append(FieldName);
TUpdateSQL.Append("]=");
switch (pDataReader[FieldName].GetType().ToString())
{
case "System.Boolean":
if (Convert.ToBoolean(pDataReader[FieldName]) == false)
{
TUpdateSQL.Append("0");
}
else
{
TUpdateSQL.Append("1");
}
break;
case "System.Int32":
case "System.Decimal":
case "System.Double":
case "System.Single":
case "System.Byte":
TUpdateSQL.Append(pDataReader[FieldName].ToString().Replace("'", "''"));
break;
default:
TUpdateSQL.Append("'");
TUpdateSQL.Append(pDataReader[FieldName].ToString().Replace("'", "''"));
TUpdateSQL.Append("'");
break;
}
TUpdateSQL.Append(",");
}
UpdateSQL.Append(TUpdateSQL);
}
catch (Exception ex)
{
// do nothing
}
}
UpdateSQL.Length = UpdateSQL.Length - 1;
UpdateSQL.Append(" Where ");
UpdateSQL.Append(pKeyString);
//builderOLE = null;
cmdSqL = null;
cmdSqL = Conn.CreateCommand();
cmdSqL.CommandText = UpdateSQL.ToString();
cmdSqL.ExecuteNonQuery();
//break;
}
else
{
//case DBReadExecute.enumDataSouce.MSAccess:
//case DBReadExecute.enumDataSouce.MSAccess2007:
//case DBReadExecute.enumDataSouce.MSExcel:
//case DBReadExecute.enumDataSouce.MSExcel2007:
ConnOle = new System.Data.OleDb.OleDbConnection(ConnectionString);
AdapterOle = new System.Data.OleDb.OleDbDataAdapter(pSelectSQL, ConnOle);
//Adapter.FillSchema(dataSet, SchemaType.Source, pDestinationTableName);
AdapterOle.FillSchema(dataSet, SchemaType.Source);
AdapterOle.Fill(Temp);
builderOLE = new System.Data.OleDb.OleDbCommandBuilder();
builderOLE.DataAdapter = AdapterOle;
ConnOle.Open();
cmdOle = ConnOle.CreateCommand();
cmdOle = builderOLE.GetInsertCommand();
cmdOle.CommandTimeout = 1500;
UpdateSQL.Append("Update ");
UpdateSQL.Append(pSelectSQL.Replace("Select * From ", ""));
UpdateSQL.Append(" Set ");
foreach (System.Data.OleDb.OleDbParameter param in cmdOle.Parameters)
{
//string FieldName = param.ParameterName.TrimStart(new char[] { '@' });
string FieldName = param.SourceColumn;
try
{
StringBuilder TUpdateSQL = new StringBuilder();
if (pDataReader[FieldName] != DBNull.Value && !string.IsNullOrEmpty(pDataReader[FieldName].ToString()))
{
TUpdateSQL.Append("[");
TUpdateSQL.Append(FieldName);
TUpdateSQL.Append("]=");
switch (pDataReader[FieldName].GetType().ToString())
{
case "System.Int32":
case "System.Decimal":
case "System.Boolean":
case "System.Double":
case "System.Single":
case "System.Byte":
TUpdateSQL.Append(pDataReader[FieldName].ToString().Replace("'", "''"));
break;
default:
TUpdateSQL.Append("'");
TUpdateSQL.Append(pDataReader[FieldName].ToString().Replace("'", "''"));
TUpdateSQL.Append("'");
break;
}
TUpdateSQL.Append(",");
}
UpdateSQL.Append(TUpdateSQL);
}
catch (Exception ex)
{
// do nothing
}
}
UpdateSQL.Length = UpdateSQL.Length - 1;
UpdateSQL.Append(" Where ");
UpdateSQL.Append(pKeyString);
builderOLE = null;
cmdOle = null;
cmdOle = ConnOle.CreateCommand();
cmdOle.CommandText = UpdateSQL.ToString();
//DBReadExecute.ExecuteSQL(pFileString, InsertSQL.ToString());
cmdOle.ExecuteNonQuery();
//break;
}
}
catch (System.Exception ex)
{
Logger.Log(DateTime.Now + ": " + ex.Message);
}
finally
{
if (Conn != null)
{
Conn.Close();
}
if (ConnOle != null)
{
ConnOle.Close();
}
}
result = true;
return result;
}
protected void PKHOpenDatabase(string filename)
{
string tableName = "Species";
OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename);
conn.Open();
//Putting things in Try {} Catch{} is the worst form of flow control, or so they say.
//But there seems no other option
DataTable t = new DataTable();
OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM " + tableName, conn);
da.FillSchema(t, SchemaType.Source);
m_SpecieDataTable.PrimaryKey = new DataColumn[0];
m_SpecieDataAdapter = new OleDbDataAdapter("SELECT * FROM " + tableName, conn);
m_SpecieDataTable.Clear();
m_SpecieDataAdapter.FillSchema(m_SpecieDataTable, SchemaType.Source);
m_SpecieDataAdapter.Fill(m_SpecieDataTable);
new OleDbCommandBuilder(m_SpecieDataAdapter);
UpdateFilter();
conn.Close();
//Create a dummy row to prevent any sort of errors...
if (m_SpecieDataTable.Rows.Count == 0)
{
CreateSpecies();
//tcSpecies.SelectedTab = tabDatabase;
}
//specieDatabaseControl1.DatabaseChanged();
}
protected void OldOpenDatabase(string filename)
{
string tableName = "Species";
OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename);
conn.Open();
//Putting things in Try {} Catch{} is the worst form of flow control, or so they say.
//But there seems no other option
DataTable t = new DataTable();
OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM " + tableName, conn);
da.FillSchema(t, SchemaType.Source);
#region Change column layout to what is expected:
Dictionary<string, string> Remapping = new Dictionary<string, string>(); //<To, From>
List<string> NewColumns = new List<string>();
//Check for any columns that must be renamed:
foreach (DataColumn c in t.Columns)
if (c.ColumnName == "Rho")
Remapping.Add("Density", "Rho");
else if (c.ColumnName == "dHf")
Remapping.Add("Hf25", "dHf");
else if (c.ColumnName == "S°298")
Remapping.Add("S25", "S°298");
//Check for any columns that must be added
foreach (string s in ReqColumnList)
if (!t.Columns.Contains(s) && !Remapping.ContainsKey(s))
NewColumns.Add(s);
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
if (NewColumns.Count > 0)
{
for (int i = 0; i < NewColumns.Count; i++)
{
if (NewColumns[i] == "Ts" || NewColumns[i] == "Te")
cmd.CommandText = "ALTER TABLE " + tableName + " ADD " + NewColumns[i] + " single;";
else
cmd.CommandText = "ALTER TABLE " + tableName + " ADD " + NewColumns[i] + " varchar;";
cmd.ExecuteNonQuery();
}
}
if (Remapping.Count > 0)
{
foreach (KeyValuePair<string, string> kvp in Remapping)
{
cmd.CommandText = "ALTER TABLE " + tableName + " ADD COLUMN " + kvp.Key + " varchar;";
cmd.ExecuteNonQuery();
}
StringBuilder sb = new StringBuilder("UPDATE " + tableName + " SET ");
int j = 0;
foreach (KeyValuePair<string, string> kvp in Remapping)
{
sb.Append(kvp.Key + " = " + kvp.Value);
if (j++ != Remapping.Count - 1)
sb.Append(", ");
}
sb.AppendLine(";");
cmd.CommandText = sb.ToString();
cmd.ExecuteNonQuery();
/*foreach (KeyValuePair<string, string> kvp in Remapping)
{
cmd.CommandText = "UPDATE " + tableName + " SET " + kvp.Key + " = " + kvp.Value;
cmd.ExecuteNonQuery();
}*/
foreach (KeyValuePair<string, string> kvp in Remapping)
{
cmd.CommandText = "ALTER TABLE " + tableName + " DROP COLUMN " + kvp.Value + ";";
cmd.ExecuteNonQuery();
}
}
#endregion Column Layout
#region Check Constraints:
List<DataColumn> PK = new List<DataColumn>(t.PrimaryKey);
if (t.PrimaryKey.Length == 0) //If we need to remove or change the primary key...
{
cmd.CommandText = "ALTER TABLE " + tableName + " ADD CONSTRAINT pk PRIMARY KEY(Compound, Phase, Ts, Te)";
cmd.ExecuteNonQuery();
}
foreach (DataColumn c in t.Columns)
if (c.DataType == typeof(string) && !Remapping.ContainsValue(c.ColumnName))
{
cmd.CommandText = "ALTER TABLE " + tableName + " ALTER COLUMN " + c.ColumnName + " varchar(255)";
cmd.ExecuteNonQuery();
}
/*Dictionary<string, string> RequiredConstraints = new Dictionary<string, string>();
//RequiredConstraints.Add("Occ_gls", "CHECK (Occurence IN ('g', 'l', 's', 'G', 'L', 'S'))");
RequiredConstraints.Add("Tgt0", "CHECK (Ts > 0 and Te > 0)"); //We're not going to force Te > Ts, but rather allow the user to make the change...
foreach (string s in RequiredConstraints.Keys)
if (!t.Constraints.Contains(s))
{
cmd.CommandText = "ALTER TABLE " + tableName + " ADD CONSTRAINT " + s + " " + RequiredConstraints[s];
cmd.ExecuteNonQuery();
}*/
//It seems that it really doesn't like these constraints for an unkown reason.
#endregion Check Constraints
m_SpecieDataAdapter = new OleDbDataAdapter("SELECT * FROM " + tableName, conn);
m_SpecieDataTable.Clear();
m_SpecieDataAdapter.FillSchema(m_SpecieDataTable, SchemaType.Source);
m_SpecieDataAdapter.Fill(m_SpecieDataTable);
new OleDbCommandBuilder(m_SpecieDataAdapter);
#region Commented Out Code
//Alternative (2-table) method:
/*
try
{
m_SpecieDataAdapter = new OleDbDataAdapter("SELECT * FROM Species2", conn);
m_SpecieDataAdapter.Fill(m_SpecieDataTable);
new OleDbCommandBuilder(m_SpecieDataAdapter);
}
catch
{
m_SpecieDataAdapter = new OleDbDataAdapter("SELECT * FROM " + tableName, conn);
DataTableMapping mapping = m_SpecieDataAdapter.TableMappings.Add("Table", "" + tableName);
mapping.ColumnMappings.Add("Rho", "Density");
mapping.ColumnMappings.Add("dHf", "Hf25");
mapping.ColumnMappings.Add("S°298", "S25");
OleDbCommandBuilder cb = new OleDbCommandBuilder(m_SpecieDataAdapter);
DataTable intermediate = m_SpecieDataTable.Clone();
m_SpecieDataAdapter.Fill(intermediate);
foreach (DataRow r in intermediate.Rows)
m_SpecieDataTable.ImportRow(r);
MergeRows();
CreateNewTable(conn);
m_SpecieDataAdapter.SelectCommand = new OleDbCommand("SELECT * FROM Species2", conn);
SaveDatabase();
}*/
#endregion Commented Out Code
MergeRows();
SaveDatabase();
//After we have merged the variables, we no longer need to have a 4 column primary key (Ts and Te can be removed).
m_SpecieDataTable.PrimaryKey = new DataColumn[] { m_SpecieDataTable.Columns["Compound"], m_SpecieDataTable.Columns["Phase"] };
cmd.CommandText = "ALTER TABLE " + tableName + " DROP CONSTRAINT pk"; cmd.ExecuteNonQuery();
cmd.CommandText = "ALTER TABLE " + tableName + " ADD CONSTRAINT pk PRIMARY KEY(Compound, Phase)"; cmd.ExecuteNonQuery();
UpdateFilter();
conn.Close();
//Create a dummy row to prevent any sort of errors...
if (m_SpecieDataTable.Rows.Count == 0)
{
CreateSpecies();
//tcSpecies.SelectedTab = tabDatabase;
}
//specieDatabaseControl1.DatabaseChanged();
}
/// <summary>
/// Runs when the Upload Button is clicked.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnUpload_Click(object sender, EventArgs e)
{
//Checking file name if file is uploaded.
if (fuQuestions.HasFile)
{
FileInfo fileInfo = new FileInfo(fuQuestions.PostedFile.FileName);
//If file does not have the extension .csv an error message is displayed.
if (fileInfo.Name.Contains(".csv"))
{
string fileName = fileInfo.Name.Replace(".csv", "").ToString();
string csvFilePath = Server.MapPath("UploadedCSVFiles") + "\\" + fileInfo.Name;
//Save the CSV file in the Server inside 'UploadedCSVFiles'
fuQuestions.SaveAs(csvFilePath);
//Fetch the location of CSV file
string filePath = Server.MapPath("UploadedCSVFiles") + "\\";
string strSql = "SELECT * FROM [" + fileInfo.Name + "]";
string strCSVConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";" + "Extended Properties='text;HDR=YES;'";
//The file is written based on the schema in the writeSchema method of the Question Class.
Question writeQuestion = new Question();
string temp = writeQuestion.writeSchema(filePath, fileInfo.Name);
if (temp != "OK")
{
setLabel(temp, "red");
}
// load the data from CSV to DataTable
OleDbDataAdapter adapter = new OleDbDataAdapter(strSql, strCSVConnString);
DataTable dtCSV = new DataTable();
DataTable dtSchema = new DataTable();
adapter.FillSchema(dtCSV, SchemaType.Mapped);
adapter.Fill(dtCSV);
//Checks of the DataTable has at least one row.
if (dtCSV.Rows.Count > 0)
{
//Checks for Row Headers.
if (dtCSV.Rows[0][0].ToString() == "UploadCatName")
{
dtCSV.Rows[0].Delete();
dtCSV.AcceptChanges();
}
GridView1.DataSource = dtCSV;
GridView1.DataBind();
//The insertCategory, insertQuestion, insertAnswer and insertExplanation methods are all housed
//in the Question class file. Category and Question IDs must be returned for the Answer and Explanation
//insert methods to run since they require foreign keys to insert. If a -1 is returned for either
//of the two, an error message is displayed.
for (int i = 0; i < dtCSV.Rows.Count; i++)
{
int catID = writeQuestion.insertCategory(dtCSV.Rows[i][0].ToString(), dtCSV.Rows[i][1].ToString());
if (catID != -1)
{
User currentUser = (User)Session["User"];
int questionID = writeQuestion.insertQuestion(catID, currentUser.UserName, dtCSV.Rows[i][2].ToString());
if (questionID != -1)
{
temp = writeQuestion.insertAnswer(questionID, dtCSV.Rows[i][3].ToString(), dtCSV.Rows[i][4].ToString(), dtCSV.Rows[i][5].ToString(), dtCSV.Rows[i][6].ToString(), dtCSV.Rows[i][7].ToString(), dtCSV.Rows[i][8].ToString());
if (temp == "OK")
{
temp = writeQuestion.insertExplanation(questionID, dtCSV.Rows[i][9].ToString());
if (temp !="OK")
setLabel(temp, "#F72862");
}
else
setLabel(temp, "#F72862");
}
else
setLabel("An Error has Occurred.", "#F72862");
}
else
setLabel("An Error has Occurred.", "#F72862");
}
setLabel(string.Format("({0}) record(s) have been loaded to the database.", dtCSV.Rows.Count), "#15E626");
}
else
setLabel("File is empty.", "#F72862");
}
else
setLabel("Unable to recognize file.", "#F72862");
}
}
