コード例 #1
0
ファイル: ClsSelect.cs プロジェクト: krayknot/Loodon
        /// <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);
        }
コード例 #2
0
ファイル: ClsSelect.cs プロジェクト: krayknot/Loodon
        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);
        }
コード例 #3
0
ファイル: ClsSelect.cs プロジェクト: krayknot/Loodon
        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);
        }
コード例 #4
0
ファイル: ClsCommon.cs プロジェクト: krayknot/Loodon
        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();
        }