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

  • MySQL


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)

  • ADO.NET


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))
BEGIN
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;
END


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))
BEGIN
Select Count(UserName) from users Where email=eml AND password=pwd AND isenabled=1;
END;;


Example: Mysql stored procedure to fetch photo information based on image id. 


CREATE PROCEDURE VSK_Photos_Fetch(IN ImageID bigint)
BEGIN
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;
END;;


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))
BEGIN
Insert Into photos
(
CategoryID,Categories,UserName,Caption,Description,Tags,FileName,isDisabled,isApproved,Added_Date,GalleryID,iswall,language,mode,privacy,authkey
)
values
(
CategoryID,Categories,UserName,Caption,Description,Tags,FileName,isDisabled,isApproved,Added_Date,GalleryID,iswall,language,mode,privacy,authkey);
SET ImageID = last_insert_id();
END;;


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)
BEGIN
Update qa
SET Title=Tit, Description=Descr, Tags=Tgs, Categories=Cats
WHERE qid=Qd;


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))
{
con.Open();


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));
cmd.ExecuteNonQuery();
}
}

;