Web Notes

Many times our application uses data from external suppliers. These data, mostly recived by the Internet, is written in XML format and has different from our data mode structure. But it`s still very important for us so we want to process them by extracting data from. Of course we may procesing XML  documents in CLR or simple .NET project and than pass them to database but we should this about performance of each our solution. For example if we recieved 100MB XML  document from supplier and we processed them we still need to send those data to our database which means  that we have to pass data by the network- it`s very costly... Now assume that the same operation, connected with proccessing XMLdocuemnt, can be done in SQL SERVER side. Looks great don`t You? So let`s begin. First of all we should learn something more about three things: master.dbo.sp_xml_preparedocument : start preparing passed text as XML document ...

In many application the database structure can be changed by the user ex. CRM or Reporting. In such situation we may want to know such changes was maded. In SQL SERVER 2008 we have a possibility to react when DML (Data Manipulation Language) AND DDL (Data Definition Language) execution occured in out database. So try to log it. Firstly we want to Create a single table named 'DatabaseChangesLog': use model; GO CREATE SCHEMA LOGS CREATE TABLE DatabaseChangeLogs (    EventId int Identity Primary Key,    EventDate datetime2  Constraint DF_DefaultLogDate DEFAULT(sysdatetime()),    EventType nvarchar(100) NOT NULL,    UserName nvarchar(1050) NOT NULL,    Command nvarchar(max) NOT NULL    ) GO Note that we creata this table in model databse. This means that each newly created database will has this table after creation. For existing databases You need to run this script manullay. Now when we have appropri...

SQL Server offers a lot of system views which allow to get information about each instance without interfering in system tables. All those system views are located in hidden system datadabase name 'Resource'. All system views can be divided into at least two groups: Structure views - which storing information about SQLSERVER properties and custom structure, and begins with schema INFORMATIN_SCHEMA.* . (compilant with ISO) Dynamic views - storing information at runtime from last time instance starts; this begins with sys.dm_* When we need to get information about executed connection in the all current opened session we can query to sys.dm_exec_connections. In this view we see two very important columns: session_id: represent the IDs of all current opened session; note that session ID is presented in each query tab in Managment Studio (ex. 52). most_recent_sql_handle: a handle to the most recent executed query for single session ID. Now assume that we want to preview...

In MS SQL Server Managment Studio we can connect to any instance by using TCP/IP protocol. To do this we can use the following syntax: tcp: server_name\instance, port . For example: local instance:   tcp: .\R2,51550 remote instance: tcp: 192.168.11.5\R2,999 local alias: tcp: .\alias,51550

Sometimes solutiona that we create stores data from external suppliers. These data is stored in database and than presents to end-user. The problem occured when our suppliers user XML basen technology to send data package to us.  The  XML standard do not allow using some special characters in text (node attribute or value) so each occurance of special characters encoded. Each of us should know that and try do decode those entities before wtiring it to database and/or presents to user. But in real many thing may go wrong and in some rows of our product database these signs may appear. When You recognize the problem You can do three things but only two of them are correct. The first idea ( wrong ) is the attempt to create a CLR stored procedure or function with the System.Web.HttpUtility.HtmlDecode function. The problem is that You can`t add reference to System.Web in CLR projects! So this idea can not be executed. Second idea is to create a stand ...

With the development of digital maps such as OpenStreetMaps and Google Earth or Google Maps and other GIS systems many of the softwares need to processing geospatial data which are stores in databases. In the new version of the SQL Server 2008 R2 some new features to working with geographic data were introduced. First of all developers are able to work with two new datatypes which where implemented as a .NET common language runtime (CLR) data type: geography  geometry These types support methods and properties that allow for the creation, comparison, analysis, and retrieval of spatial data. The difference between then is the geometry data type (called planar) supported by SQL Server conforms to the Open Geospatial Consortium (OGC) Simple Features for SQL Specification while the geography data type (geodetic) stores ellipsoidal (round-earth) data, such as GPS latitude and longitude coordinates. After short theoretical introduction it`s time for implementation. First of...

Sometimes it`s easier to reaload only part of the website witout reloading whole page. Thanks to AJAX technology such aproach is possible and easy to code. Let`s begin. Firstly we need to configure our environment so we must have a Visual Studio with MVC 3  Razor isntalled on it. Razor can be obtained from here . After installation process completed , the second step is to set up a new WebStie project (Picture 1.) Picture 1. After you confirm Your project type choise, next window starts. On it You should select the second web project type (with Forms autentication method)  and than choose RAZOR form dropdown (Picture 2.). Picture 2. Now You new web solution contains several folders and files by default. Their description is not a point of this article so allow myself to continue. Next step is to create a simple model for our PartialView. As an example I  created a simple _UserModel.cs in Models folder. The source code for this model is:  public cl...

In SQL SERVER 2005 the most common data type for storing BLOBs was an IMAGE  datatype. When SQL Server 2008 and later SQL SERVER  2008 R2 occured the image datatypes coexists with several new and recomended by Mictosoft BLOB datatypes such varbinary(MAX). Let`s have a look: varbinary(max) /binary(n)  variables store variable-length binary data of approximately n bytes, may store a maximum of 2 gigabytes. image variables store up to 2 gigabytes of data and are commonly used to store any type of data file (not just images). But when you`re designing a new database structure be patient and choose the newest one type according to the Microsoft note: "ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead. Fixed and variable-length data types for storing large ...

Sometimes one of the business requirements of the system is to create a new databse at the runtime. When You using SQL Server this operation can be done easy and quick. The stored procedure below takes two arguments. First 'DbName ' sets a name for new database and the second one gathers information about success of the operation as true or false. Note that second argument is output type which means that in C# code you can get information about create new database. Now its time for some magic. After creating a new DB there is no object inside. But You don`t have to move all of structure to new DB each time you create a new one. In SQL Server one of the four database names 'model'. When you move all of Your buissnes logic (tables, views, stored procedures and even data) to it, all the structured will be move to new DB when it`s creating. Looks great, but remeber that if you have some PK on the table with data in model DB during cration of new DB PK values will be r...