We provide various web starter kit products including ASP.NET Video Starter Kit, ASP.NET vPhoto Kit, ASP.NET Video Portal and more. Each of these product include hundreds of basic and advance features to make it a complete portable, customizable and scalable solution.
Each of these products support various database management systems including
- SQL SERVER
Each of these products uses various data access technologies to process data between application business layers and database management system including
- NHibernate (version 8.0 or newer)
- Entity Framework 6.0 or newer (version 8.0 or newer)
Note: These three are separate data access technologies and their implementations are totally different. So we only support one database access technology per project. It means every product is shipped with each dataaccess layer in a separate product.
Each product also target both
- Web Form
- MVC (no support for ADO.NET)
Products packed with ADO.NET support can support using or extending normal sql queries to stored procedures to make your application more scalable.
Now as each product is packed with so many revisions, its very difficult for us to translate all newly released version product with stored procedure packed version as every database management system uses their own style of stored procedure code and implementation. By default we uses normal sql queries. Its easier for us to keep script compatible for multiple database management systems and also most shared hosting providers including godaddy doesn't support stored procedures for mysql. These issues force us to use normal SQL statemenets by default.
SQL to Stored Procedure Conversion:
As in builtin ADO.NET compatible product versions, we wrote all script as normal SQL statements. but it takes few hours to a day to translate all sql queries to stored produced. This article will list all useful stored procedures with implementation that can help you translate your mysql sql statements to stored procedures.
We recommend you to upgrade all stored procedures to sql queries that targets highly available data e.g queries to load data for home pages, preview pages, user profiles and more. There may be 5 - 15 queries in a project thtat can target these areas.
MySQL Stored Procedure Examples:
Example: Mysql stored procedure to update any field in users table based on username or userid query.
CREATE PROCEDURE VSK_Members_UpdateValue(IN FieldName text,IN FieldValue text,IN username varchar(20))
SET @query = CONCAT("Update users Set ", FieldName, "=? WHERE username=?");
PREPARE stmt FROM @query;
SET @FieldValue = FieldValue;
SET @CID = username;
EXECUTE stmt USING @FieldValue, @CID;
DEALLOCATE PREPARE stmt;
Example: Mysql stored procedure to count email address and password for validation purpose
CREATE PROCEDURE VSK_Members_ValidateEmail(IN eml varchar(50), IN pwd varchar(128))
Select Count(UserName) from users Where email=eml AND password=pwd AND isenabled=1;
Example: Mysql stored procedure to fetch photo information based on image id.
CREATE PROCEDURE VSK_Photos_Fetch(IN ImageID bigint)
SELECT p.imageid,p.caption,p.username,p.categoryid,p.liked,p.disliked,p.galleryid,p.description,p.isadult,p.iscomments,p.views,p.tags,p.filename,p.comments,p.added_date,p.isdisabled,p.isapproved,p.iscloud,p.nextid,p.previd,g.title FROM photos as p LEFT JOIN galleries as g on g.galleryid=p.galleryid WHERE p.imageid=ImageID;
Example: Mysql stored procedure to insert data (photo record) in database
CREATEVSK_Photos_Post(OUT ImageID bigint,IN CategoryID int, IN Categories varchar(200),IN UserName varchar(20),IN Caption varchar(100),IN Description varchar(300),IN Tags varchar(200),IN FileName varchar(100),IN isDisabled tinyint, IN isApproved tinyint,IN Added_Date datetime, IN GalleryID bigint, IN iswall tinyint, IN language varchar(20), IN mode tinyint,IN privacy tinyint, IN authkey varchar(10))
Insert Into photos
SET ImageID = last_insert_id();
Example: Mysql stored procedure to update record (q&a record) in database
CREATE VSK_QA_Update(IN Cats varchar(200),IN Tit varchar(100),IN Descr text,IN Tgs varchar(200),IN Qd bigint)
SET Title=Tit, Description=Descr, Tags=Tgs, Categories=Cats
So based on these stored procedure examples you can translate all SQL statements (every product have average of 5 - 10 main and upto 100 normal sql statements)
ADO.NET Code for using MySQL Stored Procedure
You can use normal Mysql.Data library to process all data access layer in your asp.net application that uses mysql database. Example code will uses mysql stored procedure to process a record in asp.net application.
using (SqlConnection con = new SqlConnection(Config.ConnectionString))
using (SqlCommand cmd = new SqlCommand("STOREPROCEDURENAME", con))
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@ContentID", ContentID));
cmd.Parameters.Add(new SqlParameter("@UserName", UserName));
cmd.Parameters.Add(new SqlParameter("@IpAddress", IPAddress));
cmd.Parameters.Add(new SqlParameter("@Reason", Reason));
cmd.Parameters.Add(new SqlParameter("@AddedDate", DateTime.Now));
cmd.Parameters.Add(new SqlParameter("@Type", Type));