none
I need course information RRS feed

  • Question

  • I have used MS Access for over 20 years. I have some C# knowledge. I have access to Lynda.com. I am trying to build a front end in Visual Studio to connect to a SQL Server database to manage data, i.e., add new records, edit and update current records, build reports, etc. What classes, websites, or other options do you guys suggest?

    Thank you.

    Friday, May 3, 2019 1:08 PM

Answers

  • Hello,

    Are you asking for VB.NET or C# first needs to be answered? Also when you say front end, is the WinForms or web projects.

    No matter the language VB.NET or C# the main different when coding is moving from use OleDb data provider to SqlClient data provider.

    There are small differences in interacting with data between VB.NET and C# e.g. in SQL-Server you can run two command queries such as shown below to first insert then get the new primary key. Going with that is below I use a class instance rather than a DataTable or DataRow to pass to the insert code as using a class is lighter weight and there is no need for all the extra stuff associated with DataTable and DataRow objects.

    ''' <summary> 
    ''' Insert new customer using command parameters 
    ''' </summary> 
    ''' <param name="pCustomer"></param> 
    ''' On succcess pCustomer CustomerIdentifier will have 
    ''' the new primary key set. 
    ''' <returns></returns> 
    Public Function Add(pCustomer As Customer) As Boolean 
    	mHasException = False 
    
    	Dim insertStatement As String = 
    			<SQL> 
    			INSERT INTO dbo.Customers 
    					   (CompanyName 
    					   ,ContactName 
    					   ,ContactTitle 
    					   ,StreetAddress 
    					   ,City 
    					   ,PostalCode 
    					   ,Country 
    					   ,Phone 
    					   ,JoinDate 
    					   ,ActiveStatus 
    					   ) 
    				 VALUES 
    					   (@CompanyName 
    					   ,@ContactName 
    					   ,@ContactTitle 
    					   ,@StreetAddress 
    					   ,@City 
    					   ,@PostalCode 
    					   ,@Country 
    					   ,@Phone 
    					   ,@JoinDate 
    					   ,@ActiveStatus 
    					   );  
    				SELECT CAST(scope_identity() AS int); 
    			</SQL>.Value 
    
    
    	Using cn = New SqlConnection With {.ConnectionString = ConnectionString} 
    		Using cmd = New SqlCommand With {.Connection = cn} 
    
    			cmd.CommandText = insertStatement 
    
    			cmd.Parameters.AddWithValue("@CompanyName",pCustomer.CompanyName) 
    			cmd.Parameters.AddWithValue("@ContactName",pCustomer.ContactName) 
    			cmd.Parameters.AddWithValue("@ContactTitle",pCustomer.ContactTitle) 
    			cmd.Parameters.AddWithValue("@StreetAddress",pCustomer.StreetAddress) 
    			cmd.Parameters.AddWithValue("@City",pCustomer.City) 
    			cmd.Parameters.AddWithValue("@PostalCode",pCustomer.PostalCode) 
    			cmd.Parameters.AddWithValue("@Country",pCustomer.Country) 
    			cmd.Parameters.AddWithValue("@Phone",pCustomer.Phone) 
    			cmd.Parameters.AddWithValue("@JoinDate",pCustomer.JoinDate) 
    			cmd.Parameters.AddWithValue("@ActiveStatus",pCustomer.ActiveStatus) 
    
    
    			Try 
    
    				cn.Open() 
    				' 
    				' Perform the insert and populate  
    				' CustomerIdentifier with the new primary key 
    				' 
    				pCustomer.CustomerIdentifier = Convert.ToInt32(cmd.ExecuteScalar()) 
    
    				Return True 
    
    			Catch ex As Exception 
    
    				mHasException = True 
    				mLastException = ex 
    
    				Return False 
    
    			End Try 
    		End Using 
    	End Using 
    End Function 
    

    In regards to web sites, the best place is Microsoft docs site to learn the basics. Outside of docs there are plenty of sites but you are going to find several pages that talk about a specific topic and usually each one has their own take on performing a specific operation.

    If using VB.NET learn how to search for information done in C#, you will have much more resources than simply VB.NET. Over the summer and early fall I will be working with Microsoft to write good code samples for WinForms and .NET Core and this is being done because the lack of quality code samples and tutorials.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange


    Friday, May 3, 2019 1:21 PM
    Moderator

All replies

  • Hello,

    Are you asking for VB.NET or C# first needs to be answered? Also when you say front end, is the WinForms or web projects.

    No matter the language VB.NET or C# the main different when coding is moving from use OleDb data provider to SqlClient data provider.

    There are small differences in interacting with data between VB.NET and C# e.g. in SQL-Server you can run two command queries such as shown below to first insert then get the new primary key. Going with that is below I use a class instance rather than a DataTable or DataRow to pass to the insert code as using a class is lighter weight and there is no need for all the extra stuff associated with DataTable and DataRow objects.

    ''' <summary> 
    ''' Insert new customer using command parameters 
    ''' </summary> 
    ''' <param name="pCustomer"></param> 
    ''' On succcess pCustomer CustomerIdentifier will have 
    ''' the new primary key set. 
    ''' <returns></returns> 
    Public Function Add(pCustomer As Customer) As Boolean 
    	mHasException = False 
    
    	Dim insertStatement As String = 
    			<SQL> 
    			INSERT INTO dbo.Customers 
    					   (CompanyName 
    					   ,ContactName 
    					   ,ContactTitle 
    					   ,StreetAddress 
    					   ,City 
    					   ,PostalCode 
    					   ,Country 
    					   ,Phone 
    					   ,JoinDate 
    					   ,ActiveStatus 
    					   ) 
    				 VALUES 
    					   (@CompanyName 
    					   ,@ContactName 
    					   ,@ContactTitle 
    					   ,@StreetAddress 
    					   ,@City 
    					   ,@PostalCode 
    					   ,@Country 
    					   ,@Phone 
    					   ,@JoinDate 
    					   ,@ActiveStatus 
    					   );  
    				SELECT CAST(scope_identity() AS int); 
    			</SQL>.Value 
    
    
    	Using cn = New SqlConnection With {.ConnectionString = ConnectionString} 
    		Using cmd = New SqlCommand With {.Connection = cn} 
    
    			cmd.CommandText = insertStatement 
    
    			cmd.Parameters.AddWithValue("@CompanyName",pCustomer.CompanyName) 
    			cmd.Parameters.AddWithValue("@ContactName",pCustomer.ContactName) 
    			cmd.Parameters.AddWithValue("@ContactTitle",pCustomer.ContactTitle) 
    			cmd.Parameters.AddWithValue("@StreetAddress",pCustomer.StreetAddress) 
    			cmd.Parameters.AddWithValue("@City",pCustomer.City) 
    			cmd.Parameters.AddWithValue("@PostalCode",pCustomer.PostalCode) 
    			cmd.Parameters.AddWithValue("@Country",pCustomer.Country) 
    			cmd.Parameters.AddWithValue("@Phone",pCustomer.Phone) 
    			cmd.Parameters.AddWithValue("@JoinDate",pCustomer.JoinDate) 
    			cmd.Parameters.AddWithValue("@ActiveStatus",pCustomer.ActiveStatus) 
    
    
    			Try 
    
    				cn.Open() 
    				' 
    				' Perform the insert and populate  
    				' CustomerIdentifier with the new primary key 
    				' 
    				pCustomer.CustomerIdentifier = Convert.ToInt32(cmd.ExecuteScalar()) 
    
    				Return True 
    
    			Catch ex As Exception 
    
    				mHasException = True 
    				mLastException = ex 
    
    				Return False 
    
    			End Try 
    		End Using 
    	End Using 
    End Function 
    

    In regards to web sites, the best place is Microsoft docs site to learn the basics. Outside of docs there are plenty of sites but you are going to find several pages that talk about a specific topic and usually each one has their own take on performing a specific operation.

    If using VB.NET learn how to search for information done in C#, you will have much more resources than simply VB.NET. Over the summer and early fall I will be working with Microsoft to write good code samples for WinForms and .NET Core and this is being done because the lack of quality code samples and tutorials.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange


    Friday, May 3, 2019 1:21 PM
    Moderator
  • I need C#. Thank you.
    Friday, May 3, 2019 1:26 PM
  • I need C#. Thank you.
    I will move your question to the C# forum. I have edited my post while wait using VB.NET but the same applies for C# from what I edited my reply to include.

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Friday, May 3, 2019 1:33 PM
    Moderator
  • I just remembered that I do know some SQL, if that helps. What I need is to learn how to use the tools in the Data portion of the toolbar, such as Gridviews, Listviews, Repeater, etc.
    Friday, May 3, 2019 2:18 PM
  • I just remembered that I do know some SQL, if that helps. What I need is to learn how to use the tools in the Data portion of the toolbar, such as Gridviews, Listviews, Repeater, etc.

    Best advice I have is

    • When working with SQL statements first write them using SSMS (SQL-Server Management Studio) which is free.
    • Thinking of toolbars such as a BindingNavigator is knowing you can override events for common buttons (see image below about setting a action to none then double click the button in the BindingNavigator to create your own event).
    • Using a DataGridView is a broad topic but the main advice is to set a DataGridView.DataSource to a container such form level BindingSource where it's data source is a DataTable or a list<T> where T might be a list of Customers.  To access data say in the selected row of the DataGridView cast something like this ((DataRowView)SomeBindingSource.Current).Row then access fields.
    • ListView is also a broad topic, there is plenty on the web.
    • DataRepeater, not going to touch this as I never cared for them.

    BindingNavigator Items property.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Friday, May 3, 2019 2:49 PM
    Moderator