private void frmColumnProperties_Load(object sender, EventArgs e) { var dstColProperties = new LoodonDAL.ClsCommon().GetColumnProperties(_serverName, _userName, _passWord, _columnName, _tableName, _database); var dst = new DataSet(); dst.Tables.Add("ROW"); dst.Tables["ROW"].Columns.Add("Column"); dst.Tables["ROW"].Columns.Add("Value"); for (var i = 0; i <= dstColProperties.Tables[0].Columns.Count - 1; i++) { dst.Tables["ROW"].Rows.Add(dstColProperties.Tables[0].Columns[i].ColumnName, dstColProperties.Tables[0].Rows[0][i].ToString()); } dgrdColumnProperties.DataSource = dst.Tables[0]; groupBox1.Text = "Column: " + _columnName + " properties";; }
private 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 = new LoodonDAL.ClsCommon().GetDatasetFromSqlQuery(SQLQuery, con); } catch (Exception) { dataError = true; } var common = new ClsCommon(); if (File.Exists(destination + "\\" + TableName + ".xls")) { File.Delete(destination + "\\" + TableName + ".xls"); } //si Excel, //créer un classeur avec un nom de table EmployeeData. La table a different champs //string tableName = string.Empty; var conn = new OleDbConnection(); conn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + destination + "\\" + TableName + ".xls; Extended Properties='Excel 8.0;HDR=YES'";; conn.Open(); if (dataError == false) { if (dstTemp.Tables.Count > 0) { //extraire les colonnes de la table de données var cols = string.Empty; for (var 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 + "],255) , "; } //colsDest = colsDest.Substring(0, colsDest.Length - 2); //Insérez l'enregistrement de la table 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) { // ignored } colsValues = string.Empty; } } } conn.Close(); }