Small is New Big in C#

Coding

Archive for July 2010

Import and Export Excel File to SQL Server

leave a comment »

1 Export data to existing EXCEL file from SQL Server table

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0;Database=D:\testing.xls;', 
    'SELECT * FROM [SheetName$]') select * from SQLServerTable

2 Export data from Excel to new SQL Server table

select * 
into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0;Database=D:\testing.xls;HDR=YES', 
    'SELECT * FROM [Sheet1$]')

3 Export data from Excel to existing SQL Server table

Insert into SQLServerTable Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0;Database=D:\testing.xls;HDR=YES', 
    'SELECT * FROM [SheetName$]')

4 If you dont want to create an EXCEL file in advance and want to export data to it, use


EXEC sp_makewebtask 
	@outputfile = 'd:\testing.xls', 
	@query = 'Select * from Database_name..SQLServerTable', 
	@colheaders =1, 
	@FixedFont=0,@lastupdated=0,@resultstitle='Testing details'

(Now you can find the file with data in tabular format)

5 To export data to new EXCEL file with heading(column names), create the following procedure


create procedure proc_generate_excel_with_columns
(
	@db_name	varchar(100),
	@table_name	varchar(100),	
	@file_name	varchar(100)
)
as

--Generate column names as a recordset
declare @columns varchar(8000), @sql varchar(8000), @data_file varchar(100)
select 
	@columns=coalesce(@columns+',','')+column_name+' as '+column_name 
from 
	information_schema.columns
where 
	table_name=@table_name
select @columns=''''''+replace(replace(@columns,' as ',''''' as '),',',',''''')

--Create a dummy file to have actual data
select @data_file=substring(@file_name,1,len(@file_name)-charindex('\',reverse(@file_name)))+'\data_file.xls'

--Generate column names in the passed EXCEL file
set @sql='exec master..xp_cmdshell ''bcp " select * from (select '+@columns+') as t" queryout "'+@file_name+'" -c'''
exec(@sql)

--Generate data in the dummy file
set @sql='exec master..xp_cmdshell ''bcp "select * from '+@db_name+'..'+@table_name+'" queryout "'+@data_file+'" -c'''
exec(@sql)

--Copy dummy file to passed EXCEL file
set @sql= 'exec master..xp_cmdshell ''type '+@data_file+' >> "'+@file_name+'"'''
exec(@sql)

--Delete dummy file 
set @sql= 'exec master..xp_cmdshell ''del '+@data_file+''''
exec(@sql)

After creating the procedure, execute it by supplying database name, table name and file path

EXEC proc_generate_excel_with_columns 'your dbname', 'your table name','your file path'

Advertisements

Written by nauhil

July 22, 2010 at 12:02 pm

Posted in Sql Server

Access Class

leave a comment »

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data;

namespace Access
{
    class Access
    {
        private OleDbConnection conn;
        private string connstring;

        public Access(string DBPath)
        {
            connstring = "Provider = Microsoft.ACE.OLEDB.12.0;Data Source="; //Microsoft.ACE.OLEDB.12.0  Microsoft.Jet.OleDb.4.0 does not support Access 2007
            connstring += DBPath;
            conn = new OleDbConnection(connstring);
            conn.Open();
        }

        public OleDbConnection DBConn()
        {
            conn.Open();
            return conn;
        }

        public void Close()
        {
            conn.Close();
        }

        public DataTable SelectToDataTable(string sqlcomm)
        {
            OleDbDataAdapter adapter = new OleDbDataAdapter();
            OleDbCommand comm = new OleDbCommand(sqlcomm, conn);
            adapter.SelectCommand = comm;
            DataTable dt = new DataTable();
            adapter.Fill(dt);
            return dt;
         
        }

        public DataSet SelectToDataSet(string sqlcomm, string tableName)
        {
            OleDbDataAdapter adapter = new OleDbDataAdapter();
            OleDbCommand comm = new OleDbCommand(sqlcomm, conn);
            adapter.SelectCommand = comm;
            DataSet ds = new DataSet();
            ds.Tables.Add(tableName);
            adapter.Fill(ds, tableName);
            return ds;
        }

        public DataSet SelectToDataSet(string sqlcomm, string tableName, DataSet dsName)
        {
            OleDbDataAdapter adapter = new OleDbDataAdapter();
            OleDbCommand comm = new OleDbCommand(sqlcomm, conn);
            adapter.SelectCommand = comm;
            DataTable dt = new DataTable();
            DataSet ds = new DataSet();
            ds = dsName;
            adapter.Fill(dsName, tableName);
            return ds;
        }

        public OleDbDataAdapter SelectToOleDbDataAdapter(string sqlcomm)
        {
            OleDbDataAdapter adapter = new OleDbDataAdapter();
            OleDbCommand comm = new OleDbCommand(sqlcomm, conn);
            adapter.SelectCommand = comm;
            return adapter;
        }

        public bool ExecuteSQLNonquery(string sqlcomm)
        {
            OleDbCommand cmd = new OleDbCommand(sqlcomm, conn);
            try
            {
                cmd.ExecuteNonQuery();
                return true;
            }
            catch
            {
                return false;
            }
        }
    }
}

Written by nauhil

July 17, 2010 at 10:28 pm

Posted in Levels Of Details

How to get windows service start property

leave a comment »

using System;
using System.Collections.Generic;
using System.Text;
using System.Management;

namespace ConsoleApplication1
{
    class Program
    {

        static void Main(string[] args)
        {
            ManagementObjectSearcher s =
                new ManagementObjectSearcher("root\\CIMV2","SELECT * FROM Win32_Service",
                new EnumerationOptions(
                null, System.TimeSpan.MaxValue,
                1, true, false, true,
                true, false, true, true));
            foreach (ManagementObject service in s.Get())
            {
                Console.WriteLine(service.Path.ToString());
               Console.WriteLine( service.GetPropertyValue("StartMode").ToString());
            }
            Console.ReadLine();
        }
    }
}

Written by nauhil

July 9, 2010 at 8:52 am

Posted in Levels Of Details