public void InsertData(string ip, string port, string user, string pass, string dataBaseName, string path)
{
BuildSql builder = new BuildSql();
//List<string> columnsName = new List<string>();
List <string> dataQuery = new List <string>();
DbfColumnGetter(path);
dataQuery = DbfDataGetter(path, columnsName, columnType);
mySqlConnection = CreateConnection(ip, port, user, pass, dataBaseName);
foreach (string query in dataQuery)
{
try {
if (InitSqlCommand(query) == 1)
{
}
else
{
MessageBox.Show("Error inserting data", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
break;
}
}
catch (Exception ex) {
Console.WriteLine("---------------");
Console.WriteLine(ex);
Console.WriteLine("---------------");
}
}
//builder.CreateInsertSql("",columnsName,data);
}
public string DbfConnection(string path)
{
List <string> columnsName = new List <string>();
List <string> columnType = new List <string>();
List <string> isLong = new List <string>();
List <string> columnSize = new List <string>();
OleDbConnection conDBF;
OleDbDataReader myReader;
BuildSql builder = new BuildSql();
DataTable dataTable = new DataTable();
conDBF = new OleDbConnection(@"Provider=VFPOLEDB.1;Data Source=" + path + ";deleted=false;");
conDBF.Open();
string sqlEventos = "select *from " + Path.GetFileName(path);
string tableName = Path.GetFileName(path).Replace(".dbf", "").Replace(".DBF", "");
OleDbCommand query = new OleDbCommand();
query.Connection = conDBF;
query.CommandText = sqlEventos;
//OleDbDataAdapter da = new OleDbDataAdapter(query);
//da = new OleDbDataAdapter(query);
//da.Fill(eventosTable);
myReader = query.ExecuteReader(CommandBehavior.KeyInfo);
//Retrieve column schema into a DataTable.
dataTable = myReader.GetSchemaTable();
//For each field in the table...
foreach (DataRow myField in dataTable.Rows)
{
columnsName.Add(myField["ColumnName"].ToString());
columnType.Add(myField["DataType"].ToString());
isLong.Add(myField["IsLong"].ToString());
columnSize.Add(myField["ColumnSize"].ToString());
}
foreach (DataColumn myProperty in dataTable.Columns)
{
//Display the field name and value.
foreach (DataRow myField in dataTable.Rows)
{
Console.WriteLine(myProperty.ColumnName + " = " + myField[myProperty].ToString());
}
}
string firstCreate = builder.CreateTableSql(tableName.ToLower(), isLong.ToArray(), columnSize.ToArray(), columnsName.ToArray(), columnType.ToArray());
///Console.WriteLine(firstCreate);
//CreateTables(firstCreate);
//Always close the DataReader and connection.
myReader.Close();
conDBF.Close();
return(firstCreate);
}
public void CreateTables(string ip, string port, string user, string pass, string dataBaseName, string path)
{
BuildSql builder = new BuildSql();
mySqlConnection = CreateConnection(ip, port, user, pass, dataBaseName);
if (InitSqlCommand(DbfConnection(path)) == 1)
{
Console.WriteLine("successfully created a database");
}
else
{
MessageBox.Show("Error creating table", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
//public DbConnect(string server,string port, string userID, string pass,string database)
//{
// mySqlConnection = CreateConnection(server,port ,userID,pass, database);
//}
public void CreateDb(string dataBaseName, string ip, string port, string user, string pass)
{
Console.WriteLine("Enterint to the method were is Creating the f*****g database");
BuildSql builder = new BuildSql();
mySqlConnection = CreateConnectionToLocalhost(ip, port, user, pass);
if (InitSqlCommand(builder.CreateDataBase(dataBaseName)) == 1)
{
MessageBox.Show("successfully created a database", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show("There was an error creating the database", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public List <string> DbfDataGetter(string path, List <string> columns, List <string> columnType)
{
List <string> dataList = new List <string>();
List <string> queryList = new List <string>();
OleDbConnection conDBF;
BuildSql builder = new BuildSql();
DataTable columnsTable = new DataTable();
DataTable dataTable = new DataTable();
OleDbDataReader myReader;
conDBF = new OleDbConnection(@"Provider = VFPOLEDB.1; Data Source = " + path + "; Extended Properties = dBase IV;deleted = false;");
//conDBF = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";deleted=false;");
conDBF.Open();
string sqlEventos = "select * from " + Path.GetFileName(path);
string tableName = Path.GetFileName(path).Replace(".dbf", "").Replace(".DBF", "");
OleDbCommand query = new OleDbCommand();
query.Connection = conDBF;
query.CommandText = sqlEventos;
//End column reader
//Start data reader
OleDbDataAdapter data = new OleDbDataAdapter(query);
myReader = query.ExecuteReader(CommandBehavior.KeyInfo);
//Retrieve column schema into a DataTable.
dataTable = myReader.GetSchemaTable();
data = new OleDbDataAdapter(query);
dataTable = FPReaderToDataTable(myReader, tableName);
//data.Fill(dataTable);
size = dataTable.Rows.Count;
foreach (DataRow row in dataTable.Rows)
{
dataList = new List <string>();
for (int i = 0; i < columns.Count; i++)
{
if (columnType[i].Equals("System.String"))
{
if (columns[i].ToString().Equals("descripcion"))
{
dataList.Add("'" + row["desc"].ToString().Replace("'", "''") + "'");
}
else
{
dataList.Add("'" + row[columns[i]].ToString().Replace("'", "''") + "'");
}
}
if (columnType[i].Equals("System.DateTime"))
{
DateTime dt;
DateTime.TryParse(row[columns[i]].ToString().Substring(0, 10), out dt);
string s = dt.ToString("yyyy-MM-dd");
//dataList.Add("'" + row[columns[i]].ToString().Substring(0, 10) + "'");
dataList.Add("'" + s + "'");
}
if (columnType[i].Equals("System.Decimal"))
{
if (row[columns[i]].ToString().Equals(""))
{
dataList.Add("0.0");
}
else
{
dataList.Add(row[columns[i]].ToString());
}
}
if (columnType[i].Equals("System.Boolean"))
{
dataList.Add(row[columns[i]].ToString());
}
}
queryList.Add(builder.CreateInsertSql(tableName, columns, dataList));
}
//Closing dbf connection
conDBF.Close();
return(queryList);
}