Category Archives: Database

Basic struction — create Database first ASP MVC Application(Models in three way)

Here in the internet have a lot tutorial introduction about create code first asp mvc application, but in the real life world the database always aready is exsiting. I am not mvc experter, I am fresher now. It took me a while to get the basic struction a while.So I like to write it down wish this could help some people like me struggling in the beginning.

OK, let me asume that we are going to create a online store ecormmerce website.

Way one: basic struction

Then you create a new MVC project through VS2013, first thing is add a connection string to your root web.config file.

It is like this:connectionStr

Modelsisnothingjustclassfile

Models is nothing just class file. You map your table to class file, then when you create a controller you could select strongly type.

finally is the Add one line to Global.asax to stop populater some seed data

GlobalNeedAddOneLine

See the null meaning don’t popular seed data.

Way two: basic struction(Models is another project)

I won’t talk about the controller or the views. But Models could anything, Web services, WCF and don’t sit in your project, the model is not neccessary sit in your project.So your project only have controller and Views, but the Models is independent. Here is a simple practise I did in my Demo struction is like this:

separateModelMvc

Way three: basic struction(Model is your entity Framework)

MvcEntityFrameworkStruction

Advertisements

Create marquee news in front page and the news is read from the database with expired data,also included management page to insert news

My boss just saw other company website has a marquee new on the front page, so she asked me if I could make one and arrow her to insert news and the news will dispear at the expired data.

For achieve that task, first I create a table in my database table, here is my table:

breadNewTable

then I write a store proceedure, a little tip for fresh guy, alway use store preceedure to insert data for security reasons.

breadNewStoreProceedure

Now, We could start the front end job. I create a user control:marquee.ascx(so you could put it in anyway you like), to display the new, here is my html code:

frontUCmarquee

here is the backcod, I used ado.net to open the database and qurery what I need, and bind it to my BulledtedList:

databasereadertomarquee

See the code this if(!IsPostBack) is not neccessary, but I use web froms so it wil inherit maintain state so I don’t need retrieve database everytime postback.

here I am also happy to share the css file for style this unorder list to display inline:

MarqueeCssStyle

A tip for fresh guy, put this in the page your use control will stand it.

Now, you just need drag the usercontrol to any page you want it with this css style in top of the header it. You coud see it.

Then We could start create a page to arrow my boss to insert the bread news, I used boostrap format so it save my time to write css. here is the front page look like:

addNews

here is the html code:

htmlCodeAddnews

The form use jQury Validate, the jquery-ui.js, is for my boss click then will pop up the jQuery calender to arrow her to put the expired date. So simple isn’t, I am love JQure very much, save a lot of time. It remind me when I first time write javascript over 10 years ago, for every simply function I need write a lot codes.

Finally I just need write a event could be fired by this button.

storeIntodatabase

Ok, That’s all. Have fun!

A store procedure to insert values into multitable without duplicate records

The previous store procedure have records repeated and here is the improve one.

the table like this:

tables

 


  
CREATE PROCEDURE [dbo].[spStoreLMStatusRecording]       
@parameterJobID varchar(30),  
@parameterLMStatus nvarchar(10),  
@parameterEntry_datetime datetime   
   
AS  
BEGIN  
declare   
 @username varchar(500),  
 @Job_Acceptance bit   
    SET @Job_Acceptance = '1'  
   
   
 IF NOT EXISTS(SELECT * FROM tblJob where JobID = @parameterJobID)    
 begin   
  insert into tblJob values(@parameterJobID,@Job_Acceptance);  
  select @username = username from tblUserAuthentication    
  insert into tblLMStatus values(@parameterLMStatus,@parameterEntry_datetime,@parameterJobID,@username)  
 end  
 else  
 begin  
  select @username = username from tblUserAuthentication    
  insert into tblLMStatus values(@parameterLMStatus,@parameterEntry_datetime,@parameterJobID,@username)  
 end  
   
END