Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# Windows SQL
Hi Good Developers,
 
I have five comboboxes (cmbRestaurant,cmbServiceWindow,cmbEquipments,cmbSubSystems and cmbMeter)
 
and one date time picker. Now on selection of those i need to export data to excel sheet can someone guide me on the same.
 
Code is below.
I need to insert these values into Excel on the basis of combobox selection.
 
How can i write stored procedure for the same.
I tried with this but this stored procedure execution is taking too long time.
Can someone guide me with the same.
 
CREATE PROCEDURE [dbo].[Get_op_meter_data]
@Point_location VARCHAR(50),
@sw_name VARCHAR(30),
@p_equip_id CHAR(10),
@equip_id CHAR(10),
@log_date datetime,
@log_time datetime,
@ASE_Reading numeric,
@G_Reading numeric 
AS
DECLARE Create_Col CURSOR SCROLL FOR 
SELECT T.Point_location,T.sw_name, T.p_equip_id, T.equip_id, log_date, log_time, SUM(E_Reading) ASE_Reading , SUM(G_reading) AS G_Reading
FROM (
SELECT A.Point_location,SW.sw_name, A.p_equip_id, A.equip_id, CONVERT(DATE,C.log_date) AS log_date, CONVERT(VARCHAR(20), C.log_hrs)+ ':' + CONVERT(VARCHAR(20),C.log_min) AS log_time, C.Act_value AS E_Reading, 0 AS G_reading
FROM om_equipment A, om_equip_meter_map B, om_service_window SW, om_sw_location_map S, tt_raw_meter C
WHERE A.equip_id = B.equip_id
AND SW.sw_id=S.sw_id
AND B.point_location = C.point_Location
AND B.point_device = C.point_device
AND B.functionblock = C.FunctionBlock
AND B.source_type_id = 'RAW_POW'
UNION
SELECT A.Point_location,SW.sw_name, A.p_equip_id, A.equip_id, CONVERT(DATE,C.log_date) AS log_date, CONVERT(VARCHAR(20), C.log_hrs)+ ':' + CONVERT(VARCHAR(20),C.log_min) AS log_time, 0 AS E_Reading, C.Act_value AS G_reading
FROM om_equipment A, om_equip_meter_map B, om_service_window SW, om_sw_location_map S, tt_raw_meter C
WHERE A.equip_id = B.equip_id
AND SW.sw_id=S.sw_id
AND B.point_location = C.point_Location
AND B.point_device = C.point_device
AND B.functionblock = C.FunctionBlock
AND B.source_type_id = 'GAS') T
GROUP BY T.Point_location, T.sw_name, T.p_equip_id, T.equip_id, log_date, log_time
ORDER BY T.Point_location, T.sw_name, T.p_equip_id, T.equip_id, log_date, log_time
 
Declare @TempTableVariable TABLE
(
Point_location VARCHAR(50),
sw_name VARCHAR(30), 
p_equip_id CHAR(10), 
equip_id CHAR(10),
log_date datetime,
log_time datetime, 
ASE_Reading numeric, 
G_Reading numeric
)
 
OPEN Create_Col
 
FETCH FIRST FROM Create_Col INTO @Point_location,@sw_name,@p_equip_id,@equip_id,@log_date,@log_time,@ASE_Reading,@G_Reading
 
WHILE @@FETCH_STATUS = 0
Begin
insert into @TempTableVariable(Point_location,sw_name,p_equip_id,equip_id,log_date,log_time,ASE_Reading,G_Reading) 
values(@Point_location,@sw_name,@p_equip_id,@equip_id,@log_date,@log_time,@ASE_Reading,@G_Reading)
End
FETCH NEXT FROM Create_Col INTO @Point_location,@sw_name,@p_equip_id,@equip_id,@log_date,@log_time,@ASE_Reading,@G_Reading
 
CLOSE Create_Col
DEALLOCATE Create_Col
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Configuration;
using System.Data.SqlClient;
using Microsoft.CSharp;
using Excel = Microsoft.Office.Interop.Excel; 
 

namespace ImportDataToExcelSP
{
public partial class Form2 : Form
{
 
private static readonly string connectionStringName = ConfigurationManager.AppSettings.Get("ConnectionString");
private static readonly string connectionString = connectionStringName;
 
public Form2()
{
InitializeComponent();
 
this.cmbRestaurant.SelectedIndexChanged -= new System.EventHandler(this.cmbRestaurant_SelectedIndexChanged);
Restaurant();
this.cmbRestaurant.SelectedIndexChanged += new System.EventHandler(this.cmbRestaurant_SelectedIndexChanged);
 
}
 
public DataSet Restaurant()
{
 

SqlConnection connection;
SqlDataAdapter adapter;
SqlCommand cmd = new SqlCommand();
DataSet D = new DataSet();
 

connection = new SqlConnection(connectionString);
 
connection.Open();
cmd.Connection = connection;
// cmbRestaurant.Items.Insert(0, new List<>"Select Region", "NA"));

//Load user list
cmd.CommandType = CommandType.Text;
cmd.CommandText = "dbo.om_Equipment";
adapter = new SqlDataAdapter(cmd);
string Q = "select distinct point_location from dbo.om_Equipment";
adapter.SelectCommand = new SqlCommand(Q, connection);
adapter.SelectCommand.ExecuteNonQuery();
DataSet DS5 = new DataSet();
adapter.Fill(DS5, "DS5");
DataTable DSTbl5 = DS5.Tables["DS5"];
cmbRestaurant.DataSource = DS5.Tables[0].DefaultView;
cmbRestaurant.DisplayMember = "point_location";
cmbRestaurant.ValueMember = "point_location";
connection.Close();
connection.Dispose();
cmd.Parameters.Clear();
return D;
 

}
 
public DataSet ServiceWindow()
{
 
String selectedStoreName = ((DataRowView)cmbRestaurant.SelectedItem).Row["point_location"].ToString();
 
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "dbo.om_service_window, dbo.om_sw_location_map, dbo.om_Equipment";
connection.Open();
 
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
 
string Q2 = "select distinct sw.sw_name from dbo.om_service_window sw join dbo.om_sw_location_map s on sw.sw_id=s.sw_id join dbo.om_Equipment e on s.point_location=e.point_location where s.point_location='" + selectedStoreName.ToString() + "'";
adapter.SelectCommand = new SqlCommand(Q2, connection);
adapter.SelectCommand.ExecuteNonQuery();
DataSet DS2 = new DataSet();
adapter.Fill(DS2, "DS2");
DataTable DSTbl2 = DS2.Tables["DS2"];
cmbServiceWindow.DataSource = DS2.Tables[0].DefaultView;
cmbServiceWindow.DisplayMember = "sw_name";
cmbServiceWindow.ValueMember = "sw_name";
connection.Close();
connection.Dispose();
cmd.Parameters.Clear();
return DS2;
}
 
public DataSet Equipments()
{
// String selectedStoreName = ((DataRowView)cmbRestaurant.SelectedItem).Row["point_location"].ToString();
String ServiceWindow = ((DataRowView)cmbServiceWindow.SelectedItem).Row["sw_name"].ToString();
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "dbo.om_service_window, dbo.om_sw_location_map, dbo.om_Equipment";
connection.Open();
 
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
 
string Q2 = "select distinct e.equip_id from dbo.om_Equipment e join dbo.om_sw_location_map s on s.point_location=e.point_location join dbo.om_service_window sw on sw.sw_id=s.sw_id where e.p_equip_id is null and sw.sw_name='" + ServiceWindow.ToString() + "'";
adapter.SelectCommand = new SqlCommand(Q2, connection);
adapter.SelectCommand.ExecuteNonQuery();
DataSet DS2 = new DataSet();
adapter.Fill(DS2, "DS2");
DataTable DSTbl2 = DS2.Tables["DS2"];
cmbEquipments.DataSource = DS2.Tables[0].DefaultView;
cmbEquipments.DisplayMember = "sw_name";
cmbEquipments.ValueMember = "equip_id";
connection.Close();
connection.Dispose();
cmd.Parameters.Clear();
return DS2;
}
 

public DataSet SubSystems()
{
// String selectedStoreName = ((DataRowView)cmbRestaurant.SelectedItem).Row["point_location"].ToString();
String Equipments = ((DataRowView)cmbEquipments.SelectedItem).Row["equip_id"].ToString();
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "dbo.om_Equipment";
connection.Open();
// cmd = new SqlCommand("select equip_id from dbo.om_equip_functionblock", connection);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
 
string Q3 = "select distinct equip_id from dbo.om_Equipment where p_equip_id='" + Equipments.ToString() + "'";
adapter.SelectCommand = new SqlCommand(Q3, connection);
adapter.SelectCommand.ExecuteNonQuery();
DataSet DS3 = new DataSet();
adapter.Fill(DS3, "DS3");
DataTable DSTbl3 = DS3.Tables["DS3"];
cmbSubSystems.DataSource = DS3.Tables[0].DefaultView;
cmbSubSystems.DisplayMember = "equip_id";
cmbSubSystems.ValueMember = "equip_id";
connection.Close();
connection.Dispose();
cmd.Parameters.Clear();
return DS3;
}
 
public DataSet Meter()
{
// String selectedStoreName = ((DataRowView)cmbRestaurant.SelectedItem).Row["point_location"].ToString();
String SubSystems = ((DataRowView)cmbSubSystems.SelectedItem).Row["equip_id"].ToString();
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "dbo.r_equp_meter_map, dbo.om_Equipment";
connection.Open();
// cmd = new SqlCommand("select equip_id from dbo.om_equip_functionblock", connection);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
 
string Q4 = "select distinct source_type_id from dbo.r_equp_meter_map r join dbo.om_Equipment e on r.point_location=e.point_location where e.equip_id='"+SubSystems.ToString()+"'";
adapter.SelectCommand = new SqlCommand(Q4, connection);
adapter.SelectCommand.ExecuteNonQuery();
DataSet DS4 = new DataSet();
adapter.Fill(DS4, "DS4");
DataTable DSTbl4 = DS4.Tables["DS4"];
cmbMeter.DataSource = DS4.Tables[0].DefaultView;
cmbMeter.DisplayMember = "source_type_id";
cmbMeter.ValueMember = "source_type_id";
connection.Close();
connection.Dispose();
cmd.Parameters.Clear();
return DS4;
}
 
private void Form2_Load(object sender, EventArgs e)
{
 
}
 
private void cmbRestaurant_SelectedIndexChanged(object sender, EventArgs e)
{
this.cmbServiceWindow.SelectedIndexChanged -= new System.EventHandler(this.cmbServiceWindow_SelectedIndexChanged);
ServiceWindow();
this.cmbServiceWindow.SelectedIndexChanged += new System.EventHandler(this.cmbServiceWindow_SelectedIndexChanged); 
 
}
 
private void cmbServiceWindow_SelectedIndexChanged(object sender, EventArgs e)
{
this.cmbEquipments.SelectedIndexChanged -= new System.EventHandler(this.cmbEquipments_SelectedIndexChanged);
Equipments();
this.cmbEquipments.SelectedIndexChanged += new System.EventHandler(this.cmbEquipments_SelectedIndexChanged); 
 
}
 
private void cmbEquipments_SelectedIndexChanged(object sender, EventArgs e)
{
this.cmbSubSystems.SelectedIndexChanged -= new System.EventHandler(this.cmbSubSystems_SelectedIndexChanged);
SubSystems();
this.cmbSubSystems.SelectedIndexChanged += new System.EventHandler(this.cmbSubSystems_SelectedIndexChanged); 
}
 
private void cmbSubSystems_SelectedIndexChanged(object sender, EventArgs e)
{
Meter();
}
private void button1_Click(object sender, EventArgs e)
{
//SqlConnection cnn;
//string connectionString = null;
//string sql = null;
string data = null;
int i = 0;
int j = 0;
 
Excel.Application xlApp;
Excel.Workbook xlWorkbook;
Excel._Worksheet xlWorksheet;
object misValue = System.Reflection.Missing.Value;
 
xlApp = new Excel.Application();
xlWorkbook = xlApp.Workbooks.Add(misValue);
xlWorksheet = (Excel.Worksheet)xlWorkbook.Worksheets.get_Item(1);
 
this.button1.Click -= new System.EventHandler(this.button1_Click);
String selectedStoreName = ((DataRowView)cmbRestaurant.SelectedItem).Row["point_location"].ToString();
String ServiceWindow = ((DataRowView)cmbServiceWindow.SelectedItem).Row["sw_name"].ToString();
String Equipments = ((DataRowView)cmbEquipments.SelectedItem).Row["equip_id"].ToString();
String SubSystems = ((DataRowView)cmbSubSystems.SelectedItem).Row["equip_id"].ToString();
String Meter = ((DataRowView)cmbMeter.SelectedItem).Row["source_type_id"].ToString();
this.button1.Click += new System.EventHandler(this.button1_Click);
 
SqlConnection connection = new SqlConnection(connectionString);
connection.Open();
SqlCommand command = new SqlCommand("SELECT * FROM dbo.om_equipment WHERE DateColumn=:DateValue");
SqlParameter parameter = new SqlParameter();
parameter.DbType = DbType.Date;
parameter.Value = dateTimePicker1.Value;
SqlDataAdapter dscmd = new SqlDataAdapter(command);
DataSet ds = new DataSet();
dscmd.Fill(ds);
 
for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
{
for (j = 0; j <= ds.Tables[0].Columns.Count - 1; j++)
{
data = ds.Tables[0].Rows[i].ItemArray[j].ToString();
xlWorksheet.Cells[i + 1, j + 1] = data;
}
}
 
xlWorkbook.SaveAs("Template_Energy_Data_Analysis.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkbook.Close(true, misValue, misValue);
xlApp.Quit();
 
releaseObject(xlWorksheet);
releaseObject(xlWorkbook);
releaseObject(xlApp);
 
MessageBox.Show("Excel file created , you can find the file C:\\Template_Energy_Data_Analysis.xls");
}
 
private void releaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch (Exception ex)
{
obj = null;
MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
}
finally
{
GC.Collect();
}
}
Posted 16-Jun-12 22:50pm
Edited 16-Jun-12 22:55pm
v2
Comments
Mohamed Mitwalli at 17-Jun-12 3:59am
   
Hi Kiran Try to make it simple Try to improve your Questions
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

I have five comboboxes (cmbRestaurant,cmbServiceWindow,cmbEquipments,cmbSubSystems and cmbMeter)

and one date time picker. Now on selection of those i need to export data to excel sheet can someone guide me on the same.

Code is below.
I need to insert these values into Excel on the basis of combobox selection.

How can i write stored procedure for the same.
I tried with this but this stored procedure execution is taking too long time.
Can someone guide me with the same.
 
CREATE PROCEDURE [dbo].[Get_op_meter_data]
  	@Point_location VARCHAR(50),
	@sw_name VARCHAR(30),
	@p_equip_id CHAR(10),
	@equip_id CHAR(10),
	@log_date datetime,
	@log_time datetime,
	@ASE_Reading numeric,
	@G_Reading numeric
	
 
		
AS
DECLARE  Create_Col CURSOR SCROLL FOR 
SELECT T.Point_location,T.sw_name, T.p_equip_id, T.equip_id, log_date, log_time, SUM(E_Reading) ASE_Reading , SUM(G_reading) AS G_Reading
FROM (
SELECT A.Point_location,SW.sw_name, A.p_equip_id, A.equip_id, CONVERT(DATE,C.log_date) AS log_date, CONVERT(VARCHAR(20), C.log_hrs)+ ':' + CONVERT(VARCHAR(20),C.log_min) AS log_time, C.Act_value AS E_Reading, 0 AS G_reading
FROM om_equipment A, om_equip_meter_map B, om_service_window SW, om_sw_location_map S, tt_raw_meter C
WHERE A.equip_id = B.equip_id
AND SW.sw_id=S.sw_id
AND   B.point_location = C.point_Location
AND   B.point_device = C.point_device
AND   B.functionblock = C.FunctionBlock
AND B.source_type_id = 'RAW_POW'
UNION
SELECT A.Point_location,SW.sw_name, A.p_equip_id, A.equip_id, CONVERT(DATE,C.log_date) AS log_date, CONVERT(VARCHAR(20), C.log_hrs)+ ':' + CONVERT(VARCHAR(20),C.log_min) AS log_time, 0 AS E_Reading, C.Act_value AS G_reading
FROM om_equipment A, om_equip_meter_map B, om_service_window SW, om_sw_location_map S, tt_raw_meter C
WHERE A.equip_id = B.equip_id
AND SW.sw_id=S.sw_id
AND   B.point_location = C.point_Location
AND   B.point_device = C.point_device
AND   B.functionblock = C.FunctionBlock
AND B.source_type_id = 'GAS') T
GROUP BY T.Point_location, T.sw_name, T.p_equip_id, T.equip_id, log_date, log_time
ORDER BY T.Point_location, T.sw_name, T.p_equip_id, T.equip_id, log_date, log_time
 
Declare @TempTableVariable TABLE
(
Point_location  VARCHAR(50),
sw_name VARCHAR(30), 
p_equip_id CHAR(10), 
equip_id CHAR(10),
log_date datetime,
log_time datetime, 
ASE_Reading  numeric, 
G_Reading  numeric
)
 
OPEN Create_Col
 
FETCH FIRST FROM Create_Col INTO @Point_location,@sw_name,@p_equip_id,@equip_id,@log_date,@log_time,@ASE_Reading,@G_Reading
 
WHILE @@FETCH_STATUS = 0
Begin
insert into @TempTableVariable(Point_location,sw_name,p_equip_id,equip_id,log_date,log_time,ASE_Reading,G_Reading) 
values(@Point_location,@sw_name,@p_equip_id,@equip_id,@log_date,@log_time,@ASE_Reading,@G_Reading)
	End
 
	FETCH NEXT FROM Create_Col INTO @Point_location,@sw_name,@p_equip_id,@equip_id,@log_date,@log_time,@ASE_Reading,@G_Reading
 

CLOSE Create_Col
DEALLOCATE Create_Col
  Permalink  
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

You can use OLEDB Class to do so,
 
Create you query string
Set the selected values in the insert query and then execute the non query
 
//Note I have missed some code out for simplicities sake, this all works fine however
OleDbConnection oledbConn = null;
 
OleDbCommand cmd = null;
 
OleDbConnection = new OleDbConnection(connString);           
OleDbConnection.Open();
 
string connString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0; \"", TargetFile);
 
sting InsertCommand = string.Format("INSERT INTO [{0}${1}:{1}] Values({2})", WorksheetName, Coord, valuestring);
 
cmd = new OleDbCommand(InsertCommand, oledbConn);
 
cmd.ExecuteNonQuery();
 
//close etc
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

Sourabh,
 
Its working fine thanks for your help. Need more help in future also.
I am new to this field and trying to learn the things. Please send your Email ID so that i can often take tips from you.
 
Regards,
Kiran
  Permalink  
Comments
sinhasourabh at 17-Jun-12 10:19am
   
Kiran you are welcomed. My email ID is sourabh.sinha.ss@gmail.com
 
Anyways dont forget to click 'accept this answer' this is mark this thread as answered. :)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 240
1 Kamal Rocks 184
2 PIEBALDconsult 150
3 BillWoodruff 148
4 Jochen Arndt 135
0 OriginalGriff 5,695
1 DamithSL 4,506
2 Maciej Los 4,007
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,190


Advertise | Privacy | Mobile
Web02 | 2.8.141216.1 | Last Updated 17 Jun 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100