Link to home
Start Free TrialLog in
Avatar of Simon Green
Simon Green

asked on

C# Database Help

Hi There,

Hope everyone had a good Xmas?.

I am currently developing a application for us to use In-House and am still a newbie with C#.

Im currently using the DataReader to read data into my controls. I now need to be able to use DataSets aswell, and just dont know where to start. I wonder if somebody could perhaps give me a head start with some code or point me in the right direction?

My App is simple, Login Form, then to a Midi Form, which then calls other forms to populate with Data.

I have attached my frmLogin, uctTest, clsDatabase_Actions, & clsDatabase_Static code.

Hope someone can help?

 clsDatabase-Actions.cs
 clsDatabase-Static.cs
 frmLogin.cs
 uctTest.cs
Avatar of Aaron Jabamani
Aaron Jabamani
Flag of United Kingdom of Great Britain and Northern Ireland image

Performance wise datareader better than dataset.  To display GUId/ui controls datareader is sufficient. Dataset ca be used if need to derserialize to xml / query ur results / use the same to add new rows or update modified rows back to database.
Apeter is right about performance, yet if you want then Data adapter is used
to fill dataset, for actual code refer to this link
http://msdn.microsoft.com/en-us/library/bh8kx08z%28v=vs.71%29.aspx
Avatar of Simon Green
Simon Green

ASKER

Hi There,

Thanks for your replys. Its not that i dont know the Data Set code .... im ok with that. I just dont know how to make it work with my code above. I reference the clsDataBase_Actions from all my forms. I dont know how to add code into clsDatabase_Actions to make it work with DataSets.. I.e within my code.

Cheers
Si
add a variable Dataset ds in you action classes.
create a additional method

internal override Dataset DSet()
        {
            SqlDataAdapter = new SqlDataAdapter(this.SqlCommand);
            sqlAdapter.Fill(this.ds);
            return ds
        }
Hi There najam_ftp,

Sorry im a newbie so am probably asing very silly question but what do you mean by variable Dataset ds?

Also im guessing the above goes in my clsDatabase_Actions?

What do i then call in my frmTest? Currently I call:

if (static_database.static_database_object != null)
            {
                static_database.static_database_object.SETGETSQL = "select * from tblUsernames";

                if (static_database.static_database_object is OLEDB)
                {
                    static_database.static_database_object.Create_Command();
                    static_database.static_database_object.Reader();
                    OleDbDataReader OLEDBDataReader = (OleDbDataReader)static_database.static_database_object.Return_Reader();
                    while (OLEDBDataReader.Read())
                    {
                        listBox1.Items.Add(OLEDBDataReader["Username"].ToString());
                    }

                    // Close the Reader Object
                    OLEDBDataReader.Close();
                }

Cheers
Si
Hi Najam_ftp,

Excellent. Many Thanks. I have added the code though and have 2 slight poblems.

1.         internal virtual DataSet DSet()
           {
           }

This throws the following Error - Error      2 clsDatabase_Actions.DSet()': not all code paths return a value

Cheers
Si

internal virtual DataSet DSet()
           {
 return null;
           }
Hi There,

Many thanks again for you help. Excellent that sorted that one.

But ..... I now have an issue on my test form uctTest.cs. I have added the following code to get the DataSet on my form:

            if (static_database.Database_Static_Object != null)
            {
                static_database.Database_Static_Object.SETGETSQL = "select * from tblUsernames";

                if (static_database.Database_Static_Object is OLEDB)
                {
                    DataSet ds = static_database.Database_Static_Object.DSet();
                    if (ds.Tables.Count > 0)
                    {
                        listBox1.DataSource = ds.Tables[0];
                        listBox1.DisplayMember = "Username";
                        listBox1.ValueMember = "Username";
                    }
                }

Now when i run the app though i get a ArgumentNullException was unhandled - Value cannot be null, parameter name - dataSet.

Cheers
Si
 if (ds.Tables.Count > 0)
                    {
if( ds.Tables[0].rows.count>0)
                        listBox1.DataSource = ds.Tables[0];
                        listBox1.DisplayMember = "Username";
                        listBox1.ValueMember = "Username";
                    }
}

modify to this and check if it works
Hi,

On the line:

if( ds.Tables[0].rows.count>0)

I just get:

Error 1 .Data.DataTable' does not contain a definition for 'rows' and no extension method 'rows' accepting a first argument of type 'System.Data.DataTable' could be found (are you missing a using directive or an assembly reference?)

Cheers
Si
Morning Najam_Ftp,

Hope your well? I have tried the above but to no avail. Still comes back as:

ArgumentNullException was unhandled - Value cannot be null, parameter name - dataSet.

Cheers
Si


this is some other exception not related to previous one, send me complete code so that I can fix it
Hi najam_ftp.

I will sent the relivant files. I cannot send the zipped up solution as i am using DevExpress 3rd Party extentions (Unless you have them of course).

Here is the code as above though:)

Many Thanks Again
Si
     clsDatabase-Actions.cs clsDatabase-Static.cs frmLogin.cs uctTest.cs frmMain.cs
Also here is the Database file:)

Cheers Again
Many Thankjs
Si
 Training.accdb
i cannot run here until i have project..
can you post complete error you are facing, with class name and line number
I have actually just noticed to that i get the following warning:

Warning1 clsDatabase_Actions.ds' is never assigned to, and will always have its default value null.

Just wondered if that related?

Cheers
Si
warning should not be a problem, other wise you can wirte DataSet ds = null; or DataSet ds = new DataSet();
No worries.

I`ve changed and its still giving the same exception anyways:(

Cheers
Si
Hi Najam_ftp,

Please see attached Error. I get this when i run my app and call uctTest form.

Excellence ISO.exe!Excellence.ISO.OLEDB.DSet() Line 115

System.ArgumentNullException was unhandled
  Message="Value cannot be null.\r\nParameter name: dataSet"
  Source="System.Data"
  ParamName="dataSet"
  StackTrace:
       at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
       at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
       at Excellence.ISO.OLEDB.DSet() in C:\Users\simon.green\Desktop\Simon\Excellence\Excellence.ISO.Application\clsDatabase_Actions.cs:line 115
       at Excellence.ISO.uctTest.uctTest_Load(Object sender, EventArgs e) in C:\Users\simon.green\Desktop\Simon\Excellence\Excellence.ISO.Application\User Controls\Health & Safety\uctTest.cs:line 37
       at System.Windows.Forms.UserControl.OnLoad(EventArgs e)
       at DevExpress.XtraEditors.XtraUserControl.OnLoad(EventArgs e)
       at System.Windows.Forms.UserControl.OnCreateControl()
       at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
       at System.Windows.Forms.Control.CreateControl()
       at System.Windows.Forms.Control.ControlCollection.Add(Control value)
       at Excellence.ISO.frmMain.barButtonItem1_ItemClick(Object sender, ItemClickEventArgs e) in C:\Users\simon.green\Desktop\Simon\Excellence\Excellence.ISO.Application\Forms\Base\frmMain.cs:line 145
       at DevExpress.XtraBars.BarItem.OnClick(BarItemLink link)
       at DevExpress.XtraBars.BarBaseButtonItem.OnClick(BarItemLink link)
       at DevExpress.XtraBars.BarItemLink.OnLinkClick()
       at DevExpress.XtraBars.BarItemLink.OnLinkAction(BarLinkAction action, Object actionArgs)
       at DevExpress.XtraBars.BarButtonItemLink.OnLinkAction(BarLinkAction action, Object actionArgs)
       at DevExpress.XtraBars.BarItemLink.OnLinkActionCore(BarLinkAction action, Object actionArgs)
       at DevExpress.XtraBars.ViewInfo.BarSelectionInfo.ClickLink(BarItemLink link)
       at DevExpress.XtraBars.ViewInfo.BarSelectionInfo.UnPressLink(BarItemLink link)
       at DevExpress.XtraBars.Ribbon.Handler.BaseRibbonHandler.OnUnPressItem(DXMouseEventArgs e, RibbonHitInfo hitInfo)
       at DevExpress.XtraBars.Ribbon.Handler.BaseRibbonHandler.OnUnPress(DXMouseEventArgs e, RibbonHitInfo hitInfo)
       at DevExpress.XtraBars.Ribbon.Handler.BaseRibbonHandler.OnMouseUp(DXMouseEventArgs e)
       at DevExpress.XtraBars.Ribbon.Handler.RibbonHandler.OnMouseUp(DXMouseEventArgs e)
       at DevExpress.XtraBars.Ribbon.RibbonControl.OnMouseUp(MouseEventArgs e)
       at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at DevExpress.Utils.Controls.ControlBase.WndProc(Message& m)
       at DevExpress.XtraBars.Ribbon.RibbonControl.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at DevExpress.Utils.Win.Hook.ControlWndHook.CallWindowProc(IntPtr pPrevProc, IntPtr hWnd, Int32 message, IntPtr wParam, IntPtr lParam)
       at DevExpress.Utils.Win.Hook.ControlWndHook.WindowProc(IntPtr hWnd, Int32 message, IntPtr wParam, IntPtr lParam)
       at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
       at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.Run(Form mainForm)
       at Excellence.ISO.Program.Main() in C:\Users\simon.green\Desktop\Simon\Excellence\Excellence.ISO.Application\Program.cs:line 18
       at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException:

Cheers
Si
 User generated image
ASKER CERTIFIED SOLUTION
Avatar of Najam Uddin
Najam Uddin
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You My freind are a Star:)

Working like a treat:)

Just a little Off Topic a quick couple of questions how would i handle the Update, Delete, and Add Commands for this? and also do i need to close my Database connection after ever call of DSet?

Cheers Again
Si
Hi Again Najam,

Sorry i meant within my code. I know how the Update/Delete/Add works just dont know again how to use in my code?

Do i need to create these commands in clsDatabase_Actions or do i create within each form?

Cheers
Si
like u have
protected string strSqlSelect; u can create protected string strSqlUpdate; & protected string strSqlDelete
in action class then from the forms as currently u pass select statement pass update & delete also, and in cunstructor assign to them above defined vairable.

Rest is in the link i posted for update and delete
Hi Najam,

Could you possible give me a quick bit of code for say Delete for my clsDatabase_Actions and uctTest? I should the be able to work the rest out for update and add etc?

Cheers Again for all you valuble help.

Cheers
Si