1. Database>Programmabilty>types>User-defined-type
2. Database>View>new view
3. View>Templote Explorer
4. Tool >Option>General
| Attach Database To Web
Application 1. Right click Web
app>Add Asp.NET Folder > App_Data
2. Right click App_Data>Add existing item> broswer
aspnetdb.mdf or New Item>Sql
Database
3. Double click aspnetdb.mcf> open server explorer
4. Right click aspnetdb.mdf> modify connection
5. Change>DataSource=Microsoft Sql server
> server=Soonlim > Test Conn >done
Back
|
Attach DB to Server
1. In VS >Soln>App_Data>RC
DB> Detach USE
master;
USE master;
GO
EXEC sp_detach_db
@dbname = N'AdventureWorks';
GO |
2. Open Microsoft Sql Server Management
Studio > Right click Database>Attach>
Add > browser db.mdf >OK
3. db.mdf is under slim\sqlexpress > Change name
4. In VS, RC DB>Modify Connection > SQL Server > Choose
Database
|
Sqldatabase
Caching (entire db) 3 Steps
1 Enable db&table:cmd>aspnet_regsql
-S slim\sqlexpress
-E -d Student -ed -et -t Student
//create forchangenotification table
2.In Web.config
> Add following
Back
<connectionStrings>
<add name="NW"
connectionString="Data Source=slim\sqlexpress;Initial
Catalog=Student;Integrated Security=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
<caching>
<sqlCacheDependency enabled="true" pollTime="2000">
//2 secs
<databases>
<add name="StudConn"
connectionStringName="NW"
pollTime="20000" />
</databases>
</sqlCacheDependency>
</caching> |
3.In cache.aspx>
Add following
<%@ OutputCache Duration="10" VaryByParam="*"
SqlDependency="StudConn:student;
StudConn:Courses"%>
|
Broker Service
(No polling to DB, less traffic in system)
pls: Only SQL 2005
1. <%@ OutputCache Duration="10" VaryByParam="*"
SqlDependency="CommandNotification"
%>
2. In Global.asax
void Application_Start(object sender, EventArgs e)
{
System.Data.SqlClient.SqlDependency.Start(ConfigurationManager.ConnectionStrings("NW").ConnectionString);
}
Back
|
Setup window Auth
Account to SQL Server.
Determine account IIS use.
1. IIS>Def Web>Dir Security>Edit> anonymou + integrated
In asp.net, IIS use ASPNET. In ASP, it use IUSR_machinename
Add user account
1. Open SQL Mgnt Studio
2. Security>RC
login> New login.
3. Login name: SLIM\ASPNET , check Window Authentication.
Map Window account to SQL Server login
1. Expand Northwind >Security>Users>
RC Users> New login
2. Username:ASPNET
, Login name: SLIM\ASPNET
3. Bottom, Database role membership > choose db_datareader
and db_datawriter. /read and write to NW
4. Grant execute
to Store procedure.
* Securables>Add
> All objects of the types> Stored procedures> Grant
exe.
(SQl polling requires exe store procedure in SQL dependency)
5. Restart database server
Back
|
|
SqlXML (Create XML data from Sql
Server)
use student;
select * from student
where condition
order by ID
For XML Path('student')
, Root('Students');
back
|
Install Northwind/Pub Sql2005
(SQL Server Mgnt Studio)
File>open>C:\Program Files\Microsoft.NET\SDK\v2.0\Samples\Setup\InstNwnd.sql
> Execute
DB location
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
back
|
ConnString Bulder
protected void Button1_Click(object
sender, EventArgs e)
{
SqlConnectionStringBuilder builder = new
SqlConnectionStringBuilder();
if (IntegratedCB.Checked==true)
{
builder.IntegratedSecurity
= true;
builder.DataSource
= ServerTB.Text;
builder.InitialCatalog
= DatabaseTB.Text;
}
else { //builder.IntegratedSecurity = true;
builder.DataSource = ServerTB.Text;
builder.InitialCatalog = DatabaseTB.Text;
builder.UserID = UseridTB.Text;
builder.Password = PasswordTB.Text;
}
DisplayTB.Text = builder.ToString();
}
protected void IntegratedCB_Click(object
sender, EventArgs e)
{
if (IntegratedCB.Checked)
{
// Response.Write(" button is check");
UseridTB.Enabled=
false;
PasswordTB.Enabled= false;
}
} |
Back
|
Add Referential Integrity
of FK & Build Relation (Rec
in PK table can't be deleted)
* Foregin Key constraint : can't add order
item without CID associated with primary CID in Customer Table
* Referential Constraint: Can't delete
customer if there is order associated with CID.
1. Mgnt Studio> RC table ( fk side) > Modify.
2. Table design > RC > Relationship.
3. General>Table and column specification
> to right> click on ...
4. Choose Primary key table and it PK.
To right, Choose FK table and its FK
(must be NOT Identity
increment)
5. Name=FK_Courses_Student> Enforce
Replication=Y > Enforce
FK Constraint=Y>OK >Save
Back
|
| |
