Chat with AI to Generate SQL Queries and Data in Blazor .NET | save your money

so let's get start Demo First of database a assistance with SQL server and relation database right so here I just create an Aizer application right and if I give that some prompt for example if I ask okay so let me give me uh all the Student Records so it will actually create that query and it will get the result so let me hit enter now see I'm getting that result right which is uh this return return our open AI right which is completely free and also if you see whatever they generated they generated query like this this query ret all the student record from the database right this is the he actually understand as per my qu prompt right after that what he generated he again generated the query select like whatever you know all that column names right and also equal that this and then after that see I'm getting that all that student result with that there enrollment date as well right so not only this if you ask something okay give me top five Student Records so let me check see now again they retre uh they understand okay e generated sumary is like that retrieve top five student records and also he generated this nice SQL query and once they generated the query I'm trying to get all that records like that whatever you know person ID enrollment date first name and last name it is just top five you know student records and also if I ask so let me ask okay give me total student right let me remove the records okay give me total student so it should be return whatever total record uh student we have right so see again it generate it select star count from that whatever our you know student table right and then they find it okay it is equal to student and then he generated this query and give that result it is total student is 25 right so like that actually we can generate or we can generate a query and we can get the data using that you know SQL AI assistant or you can say it is a any database not only just SQL any database relation database you can just uh give that information so they will generate it uh based on your prompt right based on your query right so let's uh start to implementation so before implementation start I'm especially recommend okay so visit my channel which is net real word example and here I'm especially recommend this guy right so this one semantic kernel actually which we have Implement in our Blazer application or any net application you can just integrate right and it is helps to connect it with your you know uh open a and all right so it is easy to integrate so especially recommend you have to go to this guy first right and same series actually we will continue so that will be help for you to understand okay how we can integrate in our net right with using the centic kernel so use that and then move it to start to implementation so I will take that same example okay which I already provide it is nothing just I have just create a Blazer application and here I just Implement know one chatbot over here with using local uh L Lama 3 model and here we have just Implement right so what I will do simply I'll show you what we have in this uh current uh project and then we will Implement so let me start run now it is running see it is just giving a chatbot right and it is also completely locally right so I have already running in my Loc local open AI which is over here if we don't know how we can set up in local AI in our uh open AI in our locally so please watch my uh some of the series related to AI Series so definitely will get the some idea and here what what I can show you simply I will ask okay uh what is SQL AI right if I ask something like that so it will return that answer right so see it is giving some answer based on this what whatever uh our prompt right so similar kind of things we will Implement if I ask okay related to database things so how we can actually assistant assistant our uh our database query so that we will Implement in this session now I'm getting that response see very quickly uh getting all that response only just asking this question right and let's start then implementation so what I need to first first actually I will just create uh another component right so let me create that component let me create a new component razor component and here the component name would suggest over here that SQL AI right so let me create that SQL AI SQL Ai and let me create yeah now it's good now first I will just decorate with this guy as a page directory and here just add that SQL a okay so this is one thing we have done now now we have to inject a service as a kernel right so which I already cover in last session already we have configured everything so that's why I suggesting please watch my previous session Rel to centic Kel in Blazer so you will get an idea how we can set up everything centic Kel and our open eii which is completely free and we don't reord any open open API key and all right so that we have done already so go back here and just inject your centic and you know import that whatever name space so once it's done now next thing actually we have to create some uh some property over here so what I have to create first first I will just create a you know what would be the prompt right so this will be my prompt which I will ask okay uh let me do something so that will expect accept actually this input prompt right so it is one thing and now second thing actually whatever data like SQL data we will store that will store in this year our row data which will using the list of the string right and then another things whatever summary as I see the demo we have a summary and query right so those two field actually we have to create it so that we can store th those things over here so now uh query and summaries done generated now one more thing actually we have to add it over here suppose we are getting some enter and all right so that also we have to capture and keep it over here and then last things actually we have to add so whatever response we are getting on that stream right so we will store over here and we'll show you uh on the on on the top right and that is done once it's done now we will actually uh um we will just design our UI right so for the UI perspective so I will not actually uh give I will not give to any details but yeah I will just it is a just a design right so what I will do simply I'll just create a design over here right so this is a simple design over here I'll just create a class row of okay and here this is a top section where I just mention okay what we are trying to achieve we are trying to achieve any database EI assistance with for the relation database and with the locally right this is the header and then here I just added some API response whatever API response I get it I just add it over here so that whatever response we are getting on the stream we we are able to show over here and then we take as a input right so this is as a input uh field which we are asking okay user can input and we can store in this user prompt right which I already just created in this here right so this is the input and then also we have just created a one submit as of now we have not create know this uh uh function but yeah let me quickly create neither it will complaining and error right so let me quickly create a function over here as of now I will not implement but yeah uh first let me you know describe of that design then we will start Implement so here is the button uh whatever actually input we are provide that user then we will hit enter and hit enter we will call this function and this function will be do whatever calling our API open EI and then get that response and we'll do further right and then few things actually also we have over here if any error so I'll show that error if any summary of here so we will show that summary if any query we are getting so I will show that query and then here so see here if it is U it is not null data so this is actually data which we are getting from the SQL Server so if it getting the data it is not then we have to show over here right and then whatever data we are getting right so that we actually you know binding over that know as a table so this is a simple logic over here which we can just set it as a table and B it actually as a table right so like that we can just create our design right and now once you create a design now we can start our implementation so what I need to do first first let me create a TR catch right so in this try catch definitely uh uh if I get on some error so we can capture over here and we will show over here right so let me add it over here and now in this here first of all I'll just uh whatever you know that assignation you know property right so that we will initialize as a null so that once I click this method every time will be null right so all that property which I just ass assigned so let me create a assigning as a null empty right and then we will start over here so first of all I have to just create you know uh one uh system message over here right so let me create a system message where our system message and then we will start our implementation over here so first what I need to write over here as a is is like as a script okay so which I will tell in our open okay please do something for me like that okay so that I have to tell usse so first I have to tell okay you are you are a helpful and cheerful database assistants okay so this is I am giving to him okay and then first let me quickly done that okay terminate that okay so this is one line and second line okay I also it will ask okay uh what you need to do use the following database when I creating your answer okay so I'll just know uh give that some idea okay how you have to proceed right so that I have to just creating over here and then here I have to give some schema so as of now uh database schema right so as of now I don't have a schema over here right so let me I have to just create that schema as well but yeah as of now let me uh leave it at as at at at a time but yeah I will just create a schema and then we will replace over here but yeah I'll show you how we can create uh in your own schema right so after the schema section done what I will do I have to just ask okay include column name header in the query and the result so also they have to consider this point like all the columns header they have to include right and then what I need to do tell I have to tell Okay so always provide your answer in the Json format right so whatever the answer they will provide they will give it to Json format that I have to tell right and then also I guess just give that format okay what kind of format actually I am expecting so I'm expecting okay give this format as a summary then your summary means our answer over here right and then query and your query right whatever query actually a will generate it right and then I'll just ask okay like that for example okay output only Json right so that I'm just also consider okay give me output only as a zon and then further also I'll just give some in instruction over here for example this guy right so let me format it first okay yeah in the proceding Json response subtitle like that and Microsoft SQL Server query retrieving the request data like that okay so those are actually common things which will we can use it right and also again I'll just uh give that another instruction over here this guy so in this proceeding again uh something I'm just asking uh for here and the summing the summary of the query right and then another things actually I will ask okay what they have to do further so they have to do always include all the column in the tables right that also I just and then last uh two three things expanding uh I will not go into more script but yeah I will tell I will tell in a short okay so if the resulting query is a non-executable replace so query be the na and what is still uh subtitle and query and all right so this is a common script you can use it uh but yeah this have to we have to do that and okay so and one more things actually what did uh whatever negative prompt right so that also I will tell okay don't do that right so do not use the my SQL syntax because we are using a SQL Server right so do not use that my SQL syntax and also the negative prompt also we can add multiple things over here like for example uh do not use limit syntax and all right so whatever things actually you want you can keep it but as of now yeah I will leave it this part but and then uh another things okay so see here just I using this guy like don't use that MySQL syntax right also actually we have to change our DB as well like suppose we have to use know my SQL so you can just change it to SQL to my SQL that's it right like example for me here so I can change our data provider whatever data provider I want I want to just change the data provider things and that remaining part will be same okay so now this section is spending right so whatever schema right so how we can generate a schema that part actually so save it everything let me save it okay and what I need to do I have to just create another tool or just say okay let me create another component quickly over here right so that uh I can generate my schema right so let me create over here generate schema right and let me create quickly and in the generat schema what I need to do simply uh first let me decorate this guy right so let me add this guy and here first of all I just add know few uh property so this is First Property which will actually add it our row and columns over here and then also another one we'll just add it for that storing a DB schema so that uh they will also be have to just create it so let me create this table schema over here in our project suppose we have a model right so let me create quickly inside this model uh class and let me create table schema and inside this table schema what we have to record is a table name and that columns right so quickly just create it go back bring back that name space and that's it right so once you done so once you're done now final things actually we have to store as a schema right so this is actually our schema so we will store uh all the tables and then store in a schema and we'll show up over here so how we show over here simply I just use this style over here right so see I just create a schema if it is not null then it create that schema and just show over here that's it right so now we have to put some logic right or or we can just create a one method so let me quickly create a one method I'm not going to uh more details but yeah let me quickly just create that method so this is using know our uh I just create this method using ado.net or let me add a new get package for that you know SQL connection or you know SQL client right so go to your application or project go to that new get package over here here let me looking for SQL yeah this guy right so look for this CL uh this this new get package Microsoft data SQL client and import that apply accept so once you uh you know add your new get package now go back to your here and just import or just you know uh bring back that name space so this is SQL Server connection string right and here what I need to do simply what I am actually trying to do over here see I just create a SQL connection and passes that connection string over here don't worry about that as of now we don't have but I'll passing over here and open the connection get that all that you know schema name right over here as uh is this SQL ising and then here we just know read and just try to add all that row and columns inside the you know uh this uh this one right so where is our row and columns we have generated here list of the key value pairs right so we just store over here and once I store then also again just you know try to grouping over here uh as a table schema and then once is done now finally is a table schema we trade through and we have ADD in our here and the schema list right and once I add I'm able to you know um show over here right so like that we can just quickly generate it over here okay it is complaining some issue right okay so it is saying whatever name we have suggesting that is as a not should not be a common right so yeah we can change it as a now generate a schema uh any data right so like that actually we just change it our method name that is fine okay but rest of things will be remaining same over here and then finally we have to call over here override and on initialize you know that life cycle hook method of that component I'll I have to call this guy so let me quickly call this guy and pass as a variable for example our connection string so let me add my connection string right so this is my connection Str and just pass it over here here right and that's it right so okay so one more thing actually see actually if you're thinking okay where I will I have to just create my DB and all right so I already just created it so let me open my SQL Server management studio and I just created my you know DB on know School DB so if you want I can share you my script also so this is my SQL Server instance right and here if you look SQL School DV so this is my school DV and it have you know all the tables for example see these are the tables we have course course Department office person student grade and all right so this is a complete student School database so which I will I can share you with the script so that actually you can just import and then you can start but not only that is school DV whatever DV you want you can just just change your you know DV name and connection thing it will generate your own schema whatever you know in this day we have that schema right so all the schema will will generated okay so I think this is done right so once is done what I will do simply I will call this component in our uh layout right in this navigation menu go to that navigation menu and just change it to something okay let me change this counter to this guy and also it should be generated schema right and save it and let me build it now will succeed let me run and generate that schema so see uh this is my default page right and then this is uh we we have just newly created so once I click this guy now see our schema is generated like whatever connection you will provide based on whatever tables right we will saw we have all these tables right so based on this tables actually we will generate like this schema so we requir this schema whatever you know database complete schema we required to use that SQL assistance query so copy this guy go back to your uh that SQL assistant RoR component file and here I will just replace that guy right and also we can give that some space also on the top okay let me stop that first okay and then okay so let me formatting again uh because that is not matter but yeah we can format okay so this is done now see whatever know suggesting you can suest and then you can just put your schema and these are all that information you have to provide right so this is done right and then one more thing actually just Deb we have to mention right so some some way actually I found actually some confusing uh once you retrieve the data using the debut and all so what you can do simply I just remove it right and that's it so this is done so once you done your things okay uh that assistance so now moving to next uh to implementation and here I I have to just create prompt object right so in this prompt object let me create a one prompt object where as a prompt right and in this prompt object actually we required you know uh if you see I'll show you that in our details so see this is our API right so in this API actually we requir this as a mod as a Jon object right and it is actually required model property and then message in this message actually we can add a role and contain role is a user and then again another role is a system so this two message object actually we have to create it so like that actually we have to build our object right so what I can do I can just quickly create uh those class right so let me quickly create those class is a model actually in this model section right so go to in the model here let me create as a root object let me create a root object and in this root object what I need to do what I need to require I need to record as a one model and then uh know object you know collection of that messages right so as of now again I have to just create this message also so let me copy this guy and go to that model again another class we have to create it a message right and in this message what we required we required these two things right roll and contain so let me quickly create this object and go back to your SQL component Reser space and here I have to just you know initialize uh that object right so let me create initializing over here and bring back that name space so this is the name space and then what I have to do I have to just uh whatever know first actually uh just initialize model right so model you can just adding the model whatever model we have right so I'm just currently using the model of L Lama 3 right so this is my model actually I'll show you so this is my model name right so whatever model you have you can just use it or if you using that open so open API right so we don't actually taking care of that because if I pass empty so automatically will uh they will be taken care right they will take okay whatever model they will taken care and they don't need to do anything okay so I just keep it as empty and then I have to just create uh that uh collection of that message right and then in here I have to just create all that message things right so first I will just create you know this message like new message the first actually the role I'll just assign the user and whatever prompt right so see this is my prompt so whatever user just prompt I'll just assign in this content right this is one now second role I will assign as a that guy uh assistance right so see rooll I will tell okay this is a system and in this system content what would be the content content would be a system message which I just created this guy right so whatever actually schema whatever instruction I just provide that I will keep it on our system right so these two things you have to taken care and then finally close that so that is done now our prompt also is ready prompt object is ready right now we are good to go to create uh call in our kernel right so if you see and the starting I just use this kernel and in this kernel actually they allow to calling our open AI all the methods and all right so let me show you how we can do that in here I'll just use a wait right I already explain everything but yeah uh let me show you uh for right in this forage let me use as a uh variable for example uh response right or or just you know result result and then in and then use that kernel right which we have injected and in here see there's a many method so I will use in inbo prompt uh prompt streaming assing this guy so this will actually returning the data as a string data and return type would be as a string right whatever data type is a string and input we also passing as a see there's a many overload method like prom template as a string then conal uh argument right so I will just pass the string but as of now I have this as a object right so what I need to do simply I have to just convert to that string to or object to as a string so this is a simple I can use the system. Json and jeta right and this is from prompt prompt string right so I can use as a prompt string and simply I can just pass it to our in this method parameter and that's it right and once you get I can retrieve that response as a result one by one and then we can proceed further right so what I will do I have to just you know replace few things over here so whatever I know see I'm getting this result and in this result I to replace something why because some of the know special character which will not in use in our Json so that's why I have to replace and also they will returning black/ and black/ T so I just replace on that right and just add it on this API response so that once I added this API response I can show in the user instantly on this user screen directly right so don't worry about that once I output I'll show you what does mean and then uh once I get it uh response one by one I just also State change so that it should be update our UI right that is done now once I get this final response storing over here what I need to do I have to just deize right because it will returning two things as I asking to this know assistance it will returning as a summary and the query right so in this summary I just you know deriz the summary and quiry and then we can we have to use use that right so what I need to do in this case again I have to just deserialize so let me use the system. text. deserialize object and this object actually we have to create a one class which have that you know this query and summary so let me quickly create this model so that you can understand so go to that model go to that class and create AI query class right and in this AI query class what we have to two fields we have to require two property over here it is a summary and query right and once I get it over here copy this guy and go back to here now see I'm getting this guy and finally whatever response we are getting over here we just dis realize on the response right and in this response we have to set our uh summary and query right so see uh whatever summary and query we have to specify in our property over here and so also in our UI right so see this is a summary and query we have to specify so we have to update on this property so let me quickly update once I update so it will good to go to show right it is good to go to show right so that is done now now final things now see we have a query right so as as per our prompt uh that AI SQL AI or know that AI assistance give me that result okay this is your query so once I get the query what I will do I will F the data from that our database and we'll show that result right so this is actually goal so what I need to do in this case now I have to just create you know um one static class so that I can just uh get that query and uh returning back the data so that actually we have to just create it so what I can do if we have any okay we don't have any class so yeah let me create another root uh that uh class but yeah we can we can keep it whatever place we want but yeah we can keep it so yeah I gu just create a data service right so I make it as a static right so after that static what I will do I'll just create you know one method like call it to this guy right and what we are returning it is returning as a list of the list of the list string right and this is is a get data table and whatever query we are using So based on the query we will just you know F the data and returning the data right so let me create an stance of that list of the list string right and then again we have to use uh you know ado.net to retrieve the data so let me quickly because I'm just using. net but yeah later part we will use you know deer and all but as of now we just for that initial level so that we are good to go okay so what I trying to do again I just create a connection string whatever our connection isting with our database right so this is my database same school database and our all that connection isting and then again I open right and pass using that SQL command pass that query and this command we will return the all the execute reader right and we get that SQL reader and the SQL reader we are reading the data right one by one and we have just set okay columns and headers we just adding as a headers value and also we are using that adding as a columns right so we are just adding a column and header value and simply return this data right so this is a simple okay I think we have to add extra things okay let me remove that okay now we are good actually to call this know static method go back to your uh to component right so this is my component and in this component I simply have to assign this whatever row data I have a row data right so which I just created over here see I just created as a row data and this row data also retrieve the data and binding over our here as a table right so we get that data over here and binding over here that's it and finally I can change the state change again I can call the state change method so that our UI can update it properly and over here uh then in exception if I got an exception so I can update our API response and error right so this is done right this is completely done and we are good to call our implementation or we can check it right so let me build it and see is there any error and all now see build success there is no error and one last thing actually what I will do I have to call this component in our navigation so go to that component go to that navigation and here I can just create another uh navigation bar navigation menu and simply I just call this component over here so this is my route name and here I can just mention the name okay it is a SQL AI assistance right so that is done and now finally run this application now uh this is fine right and then go to the SQL AI okay now see this is uh look like actually we have just created now now this is our header right and also this is a prompt where I just asking the prompt and result will be appearing over here right right so let me ask this guy and submit okay so see uh once I submit nothing happening right so let me quickly go through or just debugging something what actually happening once I submit nothing happening right why because I'm using you know uh this component as a server rendering right so so we have to actually it is a buer concept like we have to specify the server uh interactive mode right so we have to add that guys so go close that go to your component and here if you see if you go to that you know yeah go to that SQL system now see we have to specify that R render mode right which we have missed so that's why our you know whatever you know uh submit button actually not working due to this so what you need to do in this case you also facing so you have to just add this guy rendering mode as an interactive server right so that once you add now let me run and see now I go to that SQL assistance and here let me ask the question right give me all the student records and once I submit now see I'm getting the response in instantly right so this is a retrieving all the datas and all and also they're getting this response right and now you see or to see over here see they understand okay ring all the student records and they are also creating a Genting generating as a query right and this query also if I want to show you let me show first in our SQL Server this query perfectly working in our that uh DB as well so if you go to here right and let me bring back so that you are able to see now see this is a query if you execute I'm able to say get that all that response right similar I just get that response over here as like as a table format right so like that okay so now let me change it for example give me top five Student Records right and let me submit now see I'm getting the top five reord as well now see I'm getting this uh complete this uh join statement as well now go to back in a here let me try to join as well and let me bring back so see already they are preparing join for me order why everything actually they created based on that response now I'm getting the same response same response also I'm getting over here right so like that actually you can achieve uh your SQL assistance in our openi with completely free in our locally right and uh yeah and also you can just generating your schema whatever your schema so you can just change your DB uh generate your schema and just change it over here right so like that actually you can just use this AI SQL a assistance completely free so before closing this session uh I just you know push this code my GitHub repository so that you can take it from there right now this is that Repository name right Blazer app SQL AI assistance centic kernel and push it so you can just use it this one right so that's all for this session have a nice day and don't forget to like subscribe and share for more motivation

Channel: DotNet Real world example

Views: 340

Keywords: 100% Local Solution, Chat with AI, Generate SQL Queries, Chat with AI to Generate SQL Queries, Chat with AI to Generate SQL Queries and Data in Blazor .NET, save your money, Free SQL Query Generation and Data, Chat with AI for Free Queries and Data, AI-Powered SQL Query Generation and Data, Free AI Chat for Queries and Data, Free and 100% Local Solution, ree AI-Driven Query Generation, Free AI Chat for SQL Queries and Data, Free SQL Query Generation and Data for All

Id: UmwVwINhgjA

Length: 39min 41sec (2381 seconds)

Published: Tue May 14 2024