/// <summary>
/// Retruns the dataset contains the results table names
/// </summary>
/// <param name="columnName"></param>
/// <param name="server"></param>
/// <param name="username"></param>
/// <param name="password"></param>
/// <param name="database"></param>
/// <returns></returns>
public DataSet GetTablefromColumn(string columnName, string server, string username, string password, string database)
{
var sqlConnectionString = "Data Source=" + server + ";User Id=" + username + ";Password="******";Initial Catalog=" + database + ";";
_sqlCon.ConnectionString = sqlConnectionString;
var dst = new ClsCommon().GetDatasetFromSqlQuery(ClsQueries.SqlGettablefromcolumn.Replace("{COLUMNAME}", columnName), _sqlCon);
return(dst);
}
public virtual string GetTableScript(string tableName, string server, string username, string password, string database)
{
var sqlConnectionString = "Data Source=" + server + ";User Id=" + username + ";Password="******";Initial Catalog=" + database + ";";
_sqlCon.ConnectionString = sqlConnectionString;
var common = new ClsCommon();
var dst = common.GetDatasetFromSqlQuery(ClsQueries.SqlGettablescript.Replace("{TABLENAME}", tableName), _sqlCon);
var response = common.ConvertDatasettoString(dst);
return(response);
}
public string GetPrimaryKey(string tableName, string server, string username, string password, string database)
{
var sqlConnectionString = "Data Source=" + server + ";User Id=" + username + ";Password="******";Initial Catalog=" + database + ";";
_sqlCon.ConnectionString = sqlConnectionString;
var common = new ClsCommon();
var dst = common.GetDatasetFromSqlQuery(ClsQueries.SqlGetprimarykey.Replace("{TABLENAME}", tableName), _sqlCon);
var response = dst.Tables[0].Rows[0][1].ToString();
return(response);
}
public void BackupExcelSeparateSheet(string destination, string SQLQuery, string Server, string Username, string Password, string Database, string TableName)
{
var dataError = false;
var sqlConnectionString = "Data Source=" + Server + ";User Id=" + Username + ";Password="******";Initial Catalog=" + Database + ";";
var con = new SqlConnection(sqlConnectionString);
var dstTemp = new DataSet();
try
{
dstTemp = GetDatasetFromSqlQuery(SQLQuery, con);
}
catch (Exception)
{
dataError = true;
}
var common = new ClsCommon();
if (File.Exists(destination + "\\" + Database + ".xls"))
{
File.Delete(destination + "\\" + Database + ".xls");
}
//si Excel,
var conn = new OleDbConnection
{
ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + destination + "\\" + Database +
".xls; Extended Properties='Excel 8.0;HDR=YES'"
};
;
conn.Open();
if (dataError == false)
{
if (dstTemp.Tables.Count > 0)
{
string cols = string.Empty;
for (int i = 0; i <= dstTemp.Tables[0].Columns.Count - 1; i++)
{
cols = cols + "[" + dstTemp.Tables[0].Columns[i].ColumnName.ToString() + "] varchar(255), ";
}
cols = cols.Substring(0, cols.Length - 2);
var cmd = new OleDbCommand();
cmd.Connection = conn;
cmd.CommandText = "CREATE TABLE [" + TableName + "] (" + cols + ")";
cmd.ExecuteNonQuery();
//extraire les colonnes de la table source
var colsDest = string.Empty;
var colsValues = string.Empty;
var fieldValue = string.Empty;
for (var i = 0; i <= dstTemp.Tables[0].Columns.Count - 1; i++)
{
colsDest = colsDest + "left([" + dstTemp.Tables[0].Columns[i].ColumnName.ToString() + "],255) , ";
}
colsDest = colsDest.Substring(0, colsDest.Length - 2);
for (var rowData = 0; rowData <= dstTemp.Tables[0].Rows.Count - 1; rowData++)
{
for (var colData = 0; colData <= dstTemp.Tables[0].Columns.Count - 1; colData++)
{
fieldValue = dstTemp.Tables[0].Rows[rowData][colData].ToString();
if (fieldValue.Contains(""))
{
fieldValue = string.Empty;
}
if (fieldValue.Length >= 250)
{
try
{
colsValues = colsValues + "'" + fieldValue.Substring(1, 250).Replace("'", "''") + "',";
}
catch (Exception)
{
// ignored
}
}
else
{
colsValues = colsValues + "'" + fieldValue.Replace("'", "''") + "',";
}
}
colsValues = colsValues.Substring(0, colsValues.Length - 1);
cmd.CommandText = "INSERT INTO [" + TableName + "] values (" + colsValues + ")";
try
{
cmd.ExecuteNonQuery();
}
catch (Exception)
{
}
colsValues = string.Empty;
}
}
}
conn.Close();
}
