C# .NET - Creating Temporary Database Table - Asked By Basha on 01-Jun-10 06:06 AM

Hi,

I need to create Dynamically Temporary Database Table in my web application.

Once the user logins then a temporary database table has to be created and upon logout the table has to be deleted.

Can anybody please provide me a sample code in C#.net

Thanks in advance.
Regards,
Basha
Mash B replied to Basha on 01-Jun-10 06:17 AM
You can use DATATABLE as ur temporary datatable , where you can create ur table and insert information row by row.

DataTable dt = new DataTable( "Product" );
DataColumn dcProductID = new DataColumn( "ProductID", typeof( int ) );
DataColumn dcProductName =
new DataColumn( "ProductName", typeof( String ) );
dcProductName.MaxLength = 100;
DataColumn dcReleaseDate =
new DataColumn( "ReleaseDate", typeof( DateTime ) );
dt.Columns.Add( dcProductID );
dt.Columns.Add( dcProductName );
dt.Columns.Add( dcReleaseDate );
dt.PrimaryKey = new DataColumn[] { dcProductID };
dt.Constraints.Add( new UniqueConstraint( dcProductName) );
dt.Rows.Add( new object[] { 1, "4x4 EVO 2", DateTime.Now } );
dt.Rows.Add( new object[] { 2, "Halo 3", DateTime.Now } );
dt.Rows.Add( new object[] { 3, "Paperboy", DateTime.Now } );
Super Man replied to Basha on 01-Jun-10 06:21 AM

here creating temporary table result:

IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results`
GO
CREATE TABLE #Results ( Company CHAR(3), StepId TINYINT, FieldId TINYINT, )
GO
select company, stepid, fieldid from #Results
GO
ALTER TABLE #Results ADD foo VARCHAR(50) NULL
GO
select company, stepid, fieldid, foo from #Results
GO
IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results
GO

you want to create table for each user when he/she logins.

better way is that you can maintain one datatable and use in session.

so when user logins it creates datatable and when logout means session is over datatable is destroyed,

Vasanthakumar D replied to Basha on 01-Jun-10 07:12 AM
Hi,

can you explain your requirement clearly?

Thanks,
Vasanth
Basha replied to Super Man on 01-Jun-10 07:19 AM

Thanks for your time and help.

I am working on a Web Application in C#.Net and my requirement is that I need to have Tables & Buttons.

TABLES  : Table-1, Table-2  and a Temp_Table(need to be created Dynamically)
BUTTONS : Button-1, Button-2, Button-3, Button-4, etc.

After the user logged in to the portal, upon BUTTON-1 press.

Step-1: A temporary Table Temp_Table will be created.
Step-2: Matching Records from Table-1 & Table-2 will be Inserted in to Temp_Table.
Step-3: upon Press BUTTON-2, BUTTON-3, BUTTON-4, etc. records from the Temp_Table will be displayed one by one.
Step-4: Upon end of the session, Temp_Table will be dropped.

Could you please tell me what is the best solution for this.

Thank You.

Regards,
Basha

Basha replied to Vasanthakumar D on 01-Jun-10 07:21 AM

Thanks for your time and help.

I am working on a Web Application in C#.Net and my requirement is that I need to have Tables & Buttons.

TABLES  : Table-1, Table-2  and a Temp_Table(need to be created Dynamically)
BUTTONS : Button-1, Button-2, Button-3, Button-4, etc.

After the user logged in to the portal, upon BUTTON-1 press.

Step-1: A temporary Table Temp_Table will be created.
Step-2: Matching Records from Table-1 & Table-2 will be Inserted in to Temp_Table.
Step-3: upon Press BUTTON-2, BUTTON-3, BUTTON-4, etc. records from the Temp_Table will be displayed one by one.
Step-4: Upon end of the session, Temp_Table will be dropped.

Could you please tell me what is the best solution for this.

Thank You.

Regards,
Basha

Vasanthakumar D replied to Basha on 01-Jun-10 07:33 AM
Hi,

If its a web application, you have to handle situation, if user close the browser without clicking the logoff button. to overcome this try the below one...

1. Create a permanant table to store the records
2. Insert the user related details along with UserId(since you may have multiple user logged in at a sameitme)
3. clear the recods from the table after clicking the logoff button
4. If user has not click the logoff button, you have to create a Job or someother method to clear the inactive user records.

Thanks,
Vasanth
Super Man replied to Basha on 01-Jun-10 07:34 AM

there is two points which we have to notice :

1)

There may be n number of users.

For creating each table for particular user is not advisable.

For that you can create datatable and store in session.

2)

if there are many records in tables for a particular users.

then use of session with datatable creates lots of load in server.

so choose which is suitable to you as per your requirement,

Basha replied to Super Man on 01-Jun-10 08:33 AM

Hi Khan,

Thanks for your help, as per my requirement i think I need to create datatable.

Thank You.

Have a Great Day.

Regards,

Basha


Basha replied to Mash B on 01-Jun-10 08:35 AM

Hi Mash,

Thanks for your help and time.

Regards,

Basha


Goniey N (Mr. G) replied to Basha on 03-Jun-10 01:34 AM
I Think You Don't Need To Create Temporary database for everytime & Delete It Everytime.

You Have To Make Database permanently & take One Gridview.

Depending On Button Click By User Display Data From The Table Of Database.

If User Press Button-2 It Will Fetch Data From Table-2 Into The GridView.


If You Create a table temporary every time it will take more time for creating database temporary at runtime & It Will required space in memory also so it will slow your processing speed.




Binding Data At Runtime In Gridview From table Use Below Code :
// I Am Using Here Database Of Ms-Access

System.Data.OleDb.OleDbConnection cn = new System.Data.OleDb.OleDbConnection();

System.Data.OleDb.OleDbCommand cm = new System.Data.OleDb.OleDbCommand();

System.Data.OleDb.OleDbDataReader  rd;

System.Data.OleDb.OleDbDataAdapter  adp;



private void Form1_Load(object sender, EventArgs e)

{

this.Text = "Column Access by Clicking on columns";

 

        cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=test.mdb";

        try

        {

         cn.Open();

        }

        catch (Exception Ex)

        {

                MessageBox.Show(Ex.Message);

        }

        cm.Connection = cn;

        cm.CommandText = "select * from tbl1";

        try

        {

                adp = new System.Data.OleDb.OleDbDataAdapter();

                DataSet ds = new DataSet();

                adp.SelectCommand = cm;

                adp.Fill(ds, "dataset");

                dataGridView1.DataSource = ds;

                dataGridView1.DataSource = ds.Tables[0];

                dataGridView1.Refresh();

        }

        catch (Exception Ex)

        {

                 MessageBox.Show(Ex.Message);

        }

        finally

        {

                cn.Close();

        }

}



//I Hope This Will Very Helpful To you.......