Processed directly or placed in a data set in order to be exposed to users as needed or can be combined with data from multiple sources or remoted between tiers by the end of this series you'll come to know how to connect a read write update delete and or basically manipulate data using.net this will be covered in bots csharp.net and visualbasic.net so you'll have a sample of both before we begin if you haven't please do.
Not forget to subscribe to support my channel and also hit the bell icon to be notified when new videos are uploaded um for the sake of our viewers transitioning from vba to dot net framework will again keep it very simple and detailed so even beginners can follow this series um i'll demonstrate this using the sdo project so we can add buttons to trigger the code or in case we need to print out the data to excel irrespective of the project you choose the code will remain the same so feel.
Free to choose other projects maybe winform or console project which you're comfortable with in this episode we'll go ahead and create our project a sample access database we'll create some tables and also load some data manually so that we can work with those data okay i'm going to create a new project ctrl shift n i'm going to go for vsto excel workbook but you can choose any other project type let's call this.
Um working with maybe and you know like you can choose a framework uh you know i'm gonna choose the latest and we'll use the new workbook instead of using a steam workbook because we have done in the past uh with existing workbook let's try this out i'm gonna just fire this up just to see if the project was created successfully okay everything is good.
Now let's go ahead and create our sample database from windows i'm going to write access and bring this up application i will choose the blank database if you you know like in the future if you have to create prior formats you can go and get it from here by clicking on this button um we're gonna go with 2007 to 2016 okay and i'm gonna save this here let's call this sample baby.
If you want you can keep it or you can also give it another name let's say like prime key or something like that um and then change the data type as well if you want or you can um leave it as it is or you can just delete it by just pressing delete key or the electro here okay so let's say that we want to have a field called first name uh last name you can you know like uh choose the type of data so within the you know like the sort text as well.
There will be uh more uh you know like constraints that you can put in uh the text uh the limitation to the number of characters and so on okay we're not gonna go too deep into this so basically this is how you're gonna go ahead and choose you know like for example let's say this is amount you can go ahead and choose a number and then from here you can choose if you want it to be double decimal uh integer long integer and so on okay uh so that's how you'll create your table.
Like this yeah or um the easiest way if you already have the data somewhere for example here i have a table called client before i demonstrate that if you already have a data set like this you can directly use it to create a table uh access will automatically figure the data type uh and further you can change it so let's see i already have a table like this.
Let's say this is amount and this is called uh onboarding date or something like that and i'm gonna put some i'm sorry i'm gonna put some double number with decimals and here i'm gonna put a date like this okay so let's say you already have a data like this and you want to create a table you don't have to go and create each field one by one if you have a lot of fields so i'm just gonna you know like add a field called id.
Which we can delete again now copy this area and go and create a new table like this and paste it like this by selecting this area okay hold on id then access should automatically um read the data yeah like this so click on yes and then i'm just going to cancel this one if you go to uh design view i'll just.
Leave this as default table too uh you can see here it automatically pick up the double
Data type and the date and time data type here and all these are text okay so this is how you can easily you know like uh replicate the table from your excel now i'm gonna delete these two that was just a demonstration we can also create a table programmatically i will cover that in the next episode so let's.Go ahead and create a sample table called line with all this data set okay so i've copied this entire area and i'm gonna create a new table i'm gonna select this area and ctrl v okay so it pasted 500 records and now we'll go to design mode i'm going to call this as clients uh we don't need this this was just a placeholder so that we can paste it to this table.
And this uh customer id is gonna be the primary key primary key means uh it will not have a duplicate it cannot contain a duplicate here you can also limit the size um you know like um you can index them uh make it required and so on okay uh so we'll talk more about this this postcode was automatically identified as a number but we'll have that also as things everything has text here okay all right so that is uh how we can.
Create a table manually and upload data um in the next episode i'm gonna talk about how to programmatically create a table as well as upload the data about data from csv file in this episode we'll learn how to set up a data connection to our access database that we just created using ole db connection okay for that i'm gonna go ahead and add a module and i'm just gonna call this xstb okay.
So here is our module first let's import system data and then oled okay that will be and then let's also do this import system diagnostic this will be so that we can print it and see our data there in the output and then uh later on we might also need to read from csv so let's do.
This okay that will be it for now um let's set up a public uh variable which will hold our oledb connection let's call this db on as a variable name and then ledb connection and then our pots shift right click copy a spot and let's call this.
i'll just paste this um so that will be our database path now let's create a public function um so that we can reuse this connection uh from any of the sub procedures that we'll be reading uh let's call this open connection i think that should be it and this will return as open connection.
Like that okay now let's try to connect to database and um using tray exception uh so here we'll say um connection dot connection string it's cool too for those of you who have watched the prior you know like other database sql ado you know like all this series you must be already familiar with all this uh if not you can go and grab the connection string from this.
Uh website uh it has all the you know like drivers and uh connection string so it's very useful website for now i'm just gonna get this uh otherwise you can also you know i will talk more at a later stage how to connect you know like if there's a password and all this okay um so i just copied this part and i'm gonna come back here our connection string will be this okay and of course the path will be.
[Music] okay so that's our pods um and then now
We're gonna dbconnection.open now if this part fails then it's going to come down here if this part goes through then we want to return return true else if it goes down here that's where the error happened and then we want to return false okay and we can quickly test this out.So we can say if this if connection is true then we'll say mrs box connected and if it is connected we want to disconnect this we'll close the connection else we'll say fail to connect.
Now let's add a quick ribbon to this uh project let's add the ribbon designer uh let's call just call this ribbon one so we can add button to call this uh otherwise we can also directly add it to the worksheet um if you want to add a button here you can just go to the toolbar and then drag and drop the button okay for example like this.
Uh it takes time to repaint and you can double click here and then you can add the code for yeah so i think for now let's go with this fire this open debug mode so it says fail to connect uh now it's a good time to debug close this and end it uh let's see what is wrong.
Now what we can do is just disable this try catch for now and let's try and run this again just to see the error okay or we can put a message box here without disabling it but okay so it says cannot use this file is already in use okay so the first time when we create a database um it's you know like an exclusive of mode so we'll just close this okay and we can reopen it.
So that the mod is not in exclusive mode allowing others to also connect the same database at the same time okay now let's go ahead and try again it's gonna bring back this is connected okay so uh we are now we have successfully set up our connection to the database in the next episode we will start uh.
Running our commands and uh using more uh other functions and methods in this episode we'll write a small program to create table okay so going back to our let's call this table and we'll create create table sql command let's call this variable as.
Create themes string maybe we can put this at the top so that we can reuse this and then we'll say sql is equal to and here uh so create table okay so this is uh the syntax and then the name of the table um let's say we want to create a table called context okay and then within this um let's say that we want to have a field called person.
Id okay and then after the name of the field you can specify the data type so let's say that the id will be integer and then um we'll have first name last name so let's say first name and then this can be varchar that will be text okay and then i'll show more you know like the types that you can refer to and then here within the brackets you can specify a number of characters that you you know like the limit that you can put to it okay like this.
One okay um and then let's say first name and then comma will be last name and then um let's say we want to have address as well um maybe city and let's leave it at that for now.
Okay so this will be our uh sql command um and now using the connection we will start to uh execute this command so we'll just reuse this let's say so basically when we run this command if connection is open it will open the connection okay uh so if connection is open then we'll say um dim command as new oledb command and then from here we'll specify our sql.
And then this will be our connections okay so we'll say um sorry this will be our connection av connection okay and then uh we will execute this command uh if you're reading the data we'll use the reader otherwise we'll execute the execute on query and then after we execute this we will close out the connection.
So similarly we can execute other different type of update line nodes i'll cover all those examples and then pause this we can say message box maybe completed so let's try and run this earlier we created a ribbon but we didn't add anything so maybe we can add uh the ribbon tab f4 i'll just put the name.
As ev tools or something like that oh sorry um is the caption or label okay tools and then from tools uh we'll first grab and bring in the groups okay and then let's put a button we'll call the gray table from here um let's call this label as.
Table by the way this ribbon part is not important uh the idea is about uh working with database okay so i'm gonna bring this up let's see if this works okay so here is our ribbon this is completed so i think it worked okay press f5 and here is a table that we create from there okay um so that is one sample i'm gonna come back here.
What's this and this is all for now um so now you know how to create a table i'll show you more reference to the data type this is a sample so for now i'll just comment this out so in this example it has all the major data types an example of uh you know like the field this is a field name and the data type so you can specify for example this is.
Uh the field name and this is the data type and then you can also specify you know like the constraint here uh this will be this one is the primary key which is being set up from here okay and then um to the to the specific field you can also specify what sort of data it's going to include and then this will be the name of the table which we already covered here so if you need a reference you'll find this in the video description.
So again all the field names data type uh if you want to set up uh set a constraint then you can use this uh syntax or sql as your reference okay so let's go ahead and create this table to um let's say the table too for this one i hope there's no table ten yeah um so let's try and run this one i'll press shift f5.
Here's your table too and then if you go to design mode you'll see this is primary key um primary key for auto yeah and then this is by it so if you go within it you'll see the field sizes and then the early ones by this is integer law single and so on okay so you'll find all this um money currency and then coming down here uh loan text date and time all this okay so.
That will be pretty much in this episode i'll demonstrate how to upload bulk leader from csv file into database um let me quickly show you the sample first so this is a csv file with about 20 mb of data uh three columns of data with i think this will be about a million records so first we'll import this into a new table and then i'll also demonstrate how to import it to a existing table okay i'm gonna close this.
Without saving um this as well so let's call this above let's call this upload bulk data i'm gonna copy all this this part will rewrite this part the rest would be the same okay so here um first we'll do uh select select into um that will.
Create a new table so select uh all into and then um here we'll specify from where we are getting the data so from and then here we will put in our csv um next string so it will be a text and then the format will be delimited .
Um and then header so if you have header then you specify yes um so we have a header so if you don't have a header you said set this to sorry this will be header if you set this to no then it will create a field like uh field one two and three and so on okay uh so we'll specify yes for our connector and then next you can specify the database.
Path um not the full uh file but just the path okay so here we'll say um database and then which is equal to and then this will be the pods and dot you specify the file name so the file name is this dot and then within the brackets you put this we're saying select into and then here.
We'll specify the table uh let's call this new transaction transaction table like that uh select all into this table and then the format is uh this will be text um delimited just cross checking this image here database.
And then this is gonna be the part and the file name okay so let's try and run this we can add a new button here and let me just call this okay let's try this.
Okay so that thing was quick that was very quick if you look at the records 913 000 records just uploaded in like within a second okay so if you go here you'll see the data type was already automatically created okay so that's uh if you have to import into a new table by creating the table automatically i'm just going to make a copy of this table just a structure.
Copy paste and let me call this as transactions okay let's say that we want to import into existing table so this will be sort text customer id and this will be date and this will be a number and we want this to be double so you can hold decimal points okay so let's create a second part of sql.
Copy this and now instead of um select into we'll use insert into okay so insert into and then you'll specify the table you want to insert into now let me just close this first insert into this table and then you can specify the fields so what are the fields here customer id.