In this post I will explain you, How to make calendar extender control to show month / year view by default and instead of selecting dates how can we use calendar extender to select months.
Before I start, let me say that I got extensive support from this forum post http://forums.asp.net/t/1349086.aspx. Thanks to Zhi-Qiang Ni, but the way he follow was a little bit lengthy. However, all credit still goes to him because I gain the exact idea from his post.
Let me start by creating a calendar extender control and attach it to a textbox.
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
<cc1:CalendarExtender ID="TextBox1_CalendarExtender" runat="server" OnClientHidden="onCalendarHidden" OnClientShown="onCalendarShown" BehaviorID="calendar1"
Enabled="True" TargetControlID="TextBox1">
</cc1:CalendarExtender>
Now, in extender markup, notice onClientHidden and OnClientShown event which I implemented as below.
function onCalendarShown() {
var cal = $find("calendar1");
//Setting the default mode to month
cal._switchMode("months", true);
//Iterate every month Item and attach click event to it
if (cal._monthsBody) {
for (var i = 0; i < cal._monthsBody.rows.length; i++) {
var row = cal._monthsBody.rows[i];
for (var j = 0; j < row.cells.length; j++) {
Sys.UI.DomEvent.addHandler(row.cells[j].firstChild, "click", call);
}
}
}
}
function onCalendarHidden()
{
var cal = $find("calendar1");
//Iterate every month Item and remove click event from it
if (cal._monthsBody) {
for (var i = 0; i < cal._monthsBody.rows.length; i++) {
var row = cal._monthsBody.rows[i];
for (var j = 0; j < row.cells.length; j++) {
Sys.UI.DomEvent.removeHandler(row.cells[j].firstChild,"click",call);
}
}
}
}
Pretty simple, In onCalendarShown method I just set the default mode to month and then iterate the control to get month item and attach on click event to it. So that, it will not go further to show us dates of that month and select the first day of that month instead.
Where as, In onCalendarHidden I am simply detaching the click event from month items. Now notice the last parameter of Sys.UI.DomEvent.addHandler function, it is the name of the function which will do the rest of the magic as below.
function call(eventElement)
{
var target = eventElement.target;
switch (target.mode) {
case "month":
var cal = $find("calendar1");
cal._visibleDate = target.date;
cal.set_selectedDate(target.date);
cal._switchMonth(target.date);
cal._blur.post(true);
cal.raiseDateSelectionChanged();
break;
}
}
Here we are simply selecting the month as the selected date of calendar control. and finally the control will look like as below.
You can get the source code from here :
http://cid-cdbfe38dc780f729.skydrive.live.com/self.aspx/.Public/Calendar%20Extender%20Month.zip
Background :
In my last post about SQL Server 2008 new feature File Stream (Saving and Retrieving File Using FileStream SQL Server 2008), we did an example of saving an image to the file stream and then retrieve it back and make it available for download.
The result of that example looks like as below.
But, one has to press the button to download the image file. One of my blog reader raise a point that he wants to display the same image instead of Get File button which is going to download.
Introduction :
So, in this post I will explain you, how can we rendered the image before actually downloading it and show that in the grid (Maybe as thumbnail, but this post will not discuss any thing about generating thumbnails).
Note : If you want to know. How to add files to the file stream please see my post Saving and Retrieving File Using FileStream SQL Server 2008
Getting Started:
We will complete this goal by using HttpHandler. lets first alter our gridview.
1: <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
2: onrowcommand="GridView1_RowCommand">
3: <Columns>
4: <asp:BoundField DataField="ID" HeaderText="ID" />
5: <asp:BoundField DataField="SystemNumber" HeaderText="System Id" />
6: <asp:TemplateField>
7: <ItemTemplate>
8: <asp:LinkButton ID="lbGetFile" runat="server" CommandName="GetFile" CommandArgument='<%#Eval("ID") %>' ><img src='<%#Eval("ID") %>.jpg' /></asp:LinkButton>
9: </ItemTemplate>
10: </asp:TemplateField>
11: </Columns>
12:
13: </asp:GridView>
Notice the link button on line no 8. I have now specify an image tag inside Link button and pass the primarykey of tbl_files as the file name along with random “.jpg”. So that, it can finally looks like as follows
8e7af927-cc7e-4515-8409-d94566246de8.jpg
a3de6abb-382f-484c-822c-7f93e0ede0c7.jpg
4ad64bf1-ea6e-4228-bdc0-300a0cd90f5a.jpg
The idea is, I will attach the handler with jpg file type to accommodate the incoming requests.
Now, lets create HttpHandler and name it “imageHandler”
public class imageHandler : IHttpHandler
{
#region IHttpHandler Members
public bool IsReusable
{
get { return false; }
}
public void ProcessRequest(HttpContext context)
{
//Getting file name from incoming request.
string url = Path.GetFileName(context.Request.Path);
Guid FileId;
try
{
//Since we have all our primary keys stored in GUID
//Try parsing the file name to Guid
FileId = new Guid(Path.GetFileNameWithoutExtension(url));
}
catch (FormatException)
{
//If some other JPG file is requested
FileId = Guid.Empty;
}
if (FileId != Guid.Empty) // If the call is for valid Image File Stream
{
SqlConnection objSqlCon = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
objSqlCon.Open();
SqlTransaction objSqlTran = objSqlCon.BeginTransaction();
SqlCommand objSqlCmd = new SqlCommand("FileGet", objSqlCon, objSqlTran);
objSqlCmd.CommandType = CommandType.StoredProcedure;
SqlParameter objSqlParam1 = new SqlParameter("@ID", SqlDbType.VarChar);
objSqlParam1.Value = FileId.ToString();
objSqlCmd.Parameters.Add(objSqlParam1);
string path = string.Empty;
string fileType = string.Empty;
using (SqlDataReader sdr = objSqlCmd.ExecuteReader())
{
while (sdr.Read())
{
path = sdr[0].ToString();
fileType = sdr[1].ToString();
}
}
objSqlCmd = new SqlCommand("SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()", objSqlCon, objSqlTran);
byte[] objContext = (byte[])objSqlCmd.ExecuteScalar();
SqlFileStream objSqlFileStream = new SqlFileStream(path, objContext, FileAccess.Read);
byte[] buffer = new byte[(int)objSqlFileStream.Length];
objSqlFileStream.Read(buffer, 0, buffer.Length);
objSqlFileStream.Close();
objSqlTran.Commit();
context.Response.AddHeader("Content-disposition", "attachment; filename=" + Path.GetFileName(path) + fileType);
// Here you need to manage the download file stuff according to your need
context.Response.ContentType = "application/octet-stream";
context.Response.BinaryWrite(buffer);
}
else
{ // If the call is for some other JPG file, nothing to do with file stream.
context.Response.WriteFile(context.Request.Path);
}
}
Well, read the comments I wrote in the code. That will of course help you to understand what actually I have done.
And then register the HttpHandler.
<httpHandlers>
<add verb="*" path="*.jpg" type="LearningApp.imageHandler, LearningApp"/>
</httpHandlers>
Conclusion:
There we go, In this way we can show the images stored on file stream in grid view. You can download both VS 2008 and VS 2010 project files.
So, it takes too long for me to write this post. even though I completed the coding stuff a week back but it is really hard to manage time these days.
In this post, I will explain and demonstrate you how to create custom paging in Grid view control. Paging which works like the Google Paging. Let me elaborate more, say for example you have a record set of 500 items and you want to display 10 items per page. Now what happen to the pages numbers. Either you use the default with “..” sign after 10th page link which cause the post back and then get some new page numbers. Or you want to make it like Google. i.e as soon as user move forward on page index, hide the previous pages and show the new numbers and when user is getting back hide new numbers and show the previous page links. following image can give you some idea what we are going to do.
To start, lets first create a Grid view .
1: <asp:GridView ID="GridView1" runat="server" AllowPaging="True"
2: onrowcreated="GridView1_RowCreated" >
3: </asp:GridView>
Yes, we have allow the paging but we are not going to use the default paging of asp.net. That is why we have write onrowcreated implementation in which we will simply detect and hide the pager row.
1: protected void GridView1_RowCreated(object sender, GridViewRowEventArgs e)
2: {
3: if (e.Row.RowType == DataControlRowType.Pager)
4: {
5: e.Row.Visible = false;
6: }
7: }
Now lets move to some global variables which we need through out our code.
1: const int pageSize = 10;
2: const int pageDispCount = 10;
3: private DataTable dt = new DataTable();
pagesSize : number of records we want to display per page.
pageDispCount : number of page numbers we want to display on custom paging.
dt : A datatable which we will use to store data and use it on different post backs.
Ok, now we need to get data from database, dump it to datatable and define the datasource of grid view.
1: protected void bindData()
2: {
3: SqlConnection objSqlCon = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["AdventureWorksConnectionString"].ToString());
4: objSqlCon.Open();
5:
6: SqlDataAdapter objSqlDa = new SqlDataAdapter("select * from Production.Product", objSqlCon);
7:
8: objSqlDa.Fill(dt);
9:
10: GridView1.DataSource = dt;
11: GridView1.DataBind();
12:
13: managePaging(dt);
14: }
On line number 13, managePaging function will be used later to implement the paging logic. But before that, lets understand that we have taken the datatable as a global variable and each time after postback when we try to read our datatable we will get no results because there is no state management for this object.
So lets override the LoadViewState and SaveViewState function of System.Web.UI.Page to save and restore the datatable by using viewstate.
1: protected override object SaveViewState()
2: {
3: object baseState = base.SaveViewState();
4: return new object[] { baseState, dt };
5: }
6: protected override void LoadViewState(object savedState)
7: {
8: object[] myState = (object[])savedState;
9: if (myState[0] != null)
10: base.LoadViewState(myState[0]);
11:
12: if (myState[1] != null)
13: {
14: dt = (DataTable) myState[1];
15: GridView1.DataSource = dt;
16: GridView1.DataBind();
17:
18: managePaging(dt);
19: }
20:
21: }
Well, SaveViewState function is simply putting the base.SaveViewState object and datatable in and object and returning it. A Simple Logic :)
Where as, LoadViewState is retrieving and type casting the object exactly in the sequence it was save in the SaveViewState method.
1: protected void managePaging(DataTable _dt)
2: {
3: if (_dt.Rows.Count > 0)
4: {
5:
6: // Variable declaration
7: int numberOfPages;
8: int numberOfRecords = dt.Rows.Count;
9: int currentPage = (GridView1.PageIndex);
10: StringBuilder strSummary = new StringBuilder();
11:
12:
13: // If number of records is more then the page size (specified in global variable)
14: // Just to check either gridview have enough records to implement paging
15: if (numberOfRecords > pageSize)
16: {
17: // Calculating the total number of pages
18: numberOfPages = (int)Math.Ceiling((double)numberOfRecords / (double)pageSize);
19: }
20: else
21: {
22: numberOfPages = 1;
23: }
24:
25:
26: // Creating a small summary for records.
27: strSummary.Append("Displaying <b>");
28:
29: // Creating X f X Records
30: int floor = (currentPage * pageSize) + 1;
31: strSummary.Append(floor.ToString());
32: strSummary.Append("</b>-<b>");
33: int ceil = ((currentPage * pageSize) + pageSize);
34:
35: //let say you have 26 records and you specified 10 page size,
36: // On the third page it will return 30 instead of 25 as that is based on pageSize
37: // So this check will see if the ceil value is increasing the number of records. Consider numberOfRecords
38: if (ceil > numberOfRecords)
39: {
40: strSummary.Append(numberOfRecords.ToString());
41: }
42: else
43: {
44: strSummary.Append(ceil.ToString());
45: }
46:
47: // Displaying Total number of records Creating X of X of About X records.
48: strSummary.Append("</b> of About <b>");
49: strSummary.Append(numberOfRecords.ToString());
50: strSummary.Append("</b>Records</br>");
51:
52:
53: litPagingSummary.Text = strSummary.ToString();
54:
55:
56: //Variable declaration
57: //these variables will used to calculate page number display
58: int pageShowLimitStart = 1;
59: int pageShowLimitEnd = 1;
60:
61:
62:
63: // Just to check, either there is enough pages to implement page number display logic.
64: if (pageDispCount > numberOfPages)
65: {
66: pageShowLimitEnd = numberOfPages; // Setting the end limit to the number of pages. Means show all page numbers
67: }
68: else
69: {
70: if (currentPage > 4) // If page index is more then 4 then need to less the page numbers from start and show more on end.
71: {
72: //Calculating end limit to show more page numbers
73: pageShowLimitEnd = currentPage + (int)(Math.Floor((decimal)pageDispCount / 2));
74: //Calculating Start limit to hide previous page numbers
75: pageShowLimitStart = currentPage - (int)(Math.Floor((decimal)pageDispCount / 2));
76: }
77: else
78: {
79: //Simply Displaying the 10 pages. no need to remove / add page numbers
80: pageShowLimitEnd = pageDispCount;
81: }
82: }
83:
84: // Since the pageDispCount can be changed and limit calculation can cause < 0 values
85: // Simply, set the limit start value to 1 if it is less
86: if (pageShowLimitStart < 1)
87: pageShowLimitStart = 1;
88:
89:
90: //Dynamic creation of link buttons
91:
92: // First Link button to display with paging
93: LinkButton objLbFirst = new LinkButton();
94: objLbFirst.Click += new EventHandler(objLb_Click);
95: objLbFirst.Text = "First";
96: objLbFirst.ID = "lb_FirstPage";
97: objLbFirst.CommandName = "pgChange";
98: objLbFirst.EnableViewState = true;
99: objLbFirst.CommandArgument = "1";
100:
101: //Previous Link button to display with paging
102: LinkButton objLbPrevious = new LinkButton();
103: objLbPrevious.Click += new EventHandler(objLb_Click);
104: objLbPrevious.Text = "Previous";
105: objLbPrevious.ID = "lb_PreviousPage";
106: objLbPrevious.CommandName = "pgChange";
107: objLbPrevious.EnableViewState = true;
108: objLbPrevious.CommandArgument = currentPage.ToString();
109:
110:
111: //of course if the page is the 1st page, then there is no need of First or Previous
112: if (currentPage == 0)
113: {
114: objLbFirst.Enabled = false;
115: objLbPrevious.Enabled = false;
116: }
117: else
118: {
119: objLbFirst.Enabled = true;
120: objLbPrevious.Enabled = true;
121: }
122:
123:
124: //Adding control in a place holder
125: plcPaging.Controls.Add(objLbFirst);
126: plcPaging.Controls.Add(new LiteralControl(" | ")); // Just to give some space
127: plcPaging.Controls.Add(objLbPrevious);
128: plcPaging.Controls.Add(new LiteralControl(" | "));
129:
130:
131: // Creatig page numbers based on the start and end limit variables.
132: for (int i = pageShowLimitStart; i <= pageShowLimitEnd; i++)
133: {
134: if ((Page.FindControl("lb_" + i.ToString()) == null) && i <= numberOfPages)
135: {
136: LinkButton objLb = new LinkButton();
137: objLb.Click += new EventHandler(objLb_Click);
138: objLb.Text = i.ToString();
139: objLb.ID = "lb_" + i.ToString();
140: objLb.CommandName = "pgChange";
141: objLb.EnableViewState = true;
142: objLb.CommandArgument = i.ToString();
143:
144: if ((currentPage + 1) == i)
145: {
146: objLb.Enabled = false;
147: }
148:
149:
150: plcPaging.Controls.Add(objLb);
151: plcPaging.Controls.Add(new LiteralControl(" | "));
152: }
153: }
154:
155: // Last Link button to display with paging
156: LinkButton objLbLast = new LinkButton();
157: objLbLast.Click += new EventHandler(objLb_Click);
158: objLbLast.Text = "Last";
159: objLbLast.ID = "lb_LastPage";
160: objLbLast.CommandName = "pgChange";
161: objLbLast.EnableViewState = true;
162: objLbLast.CommandArgument = numberOfPages.ToString();
163:
164: // Next Link button to display with paging
165: LinkButton objLbNext = new LinkButton();
166: objLbNext.Click += new EventHandler(objLb_Click);
167: objLbNext.Text = "Next";
168: objLbNext.ID = "lb_NextPage";
169: objLbNext.CommandName = "pgChange";
170: objLbNext.EnableViewState = true;
171: objLbNext.CommandArgument = (currentPage + 2).ToString();
172:
173: //of course if the page is the last page, then there is no need of last or next
174: if ((currentPage + 1) == numberOfPages)
175: {
176: objLbLast.Enabled = false;
177: objLbNext.Enabled = false;
178: }
179: else
180: {
181: objLbLast.Enabled = true;
182: objLbNext.Enabled = true;
183: }
184:
185:
186: // Adding Control to the place holder
187: plcPaging.Controls.Add(objLbNext);
188: plcPaging.Controls.Add(new LiteralControl(" | "));
189: plcPaging.Controls.Add(objLbLast);
190: plcPaging.Controls.Add(new LiteralControl(" | "));
191: }
192:
193: }
Yes, the code is complex that is why I wrote proper comments which will let you understand the stuff easily.
One last thing which is left, is the implementation of dynamically created link button onclick event.
1: void objLb_Click(object sender, EventArgs e)
2: {
3: plcPaging.Controls.Clear();
4: LinkButton objlb = (LinkButton)sender;
5: GridView1.PageIndex = (int.Parse(objlb.CommandArgument.ToString()) - 1);
6:
7: managePaging(dt);
8: }
There it is, we have now completed
Custom Paging in Grid View. If you want to download the source code,
here is the
VS 2008 Solution.
Modification:
I have been receiving emails regarding the issues of this post. Especially with the initial five page numbers. I have modified this post to fix the bug it had. Please feel free to point further issues. Also, the download links are also modified.
In this post I will explain you, how can we serialize Datatable to JSON. So that, it can easily pass to JavaScript to get the AJAX done.
First of all fill a Datatable with some results.
DataTable dt = new DataTable();
SqlConnection objSqlCon = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["AdventureWorksConnectionString"].ToString());
objSqlCon.Open();
SqlDataAdapter objSqlDa = new SqlDataAdapter("select * from Production.Product", objSqlCon);
objSqlDa.Fill(dt);
Now create a String Builder object that will contain the JSON text and JavascriptSerializer which will serialize the output in JSON.
StringBuilder objSb = new StringBuilder();
JavaScriptSerializer objSer = new JavaScriptSerializer();
Now here we are going to iterate each row and column of data table and put all of them in Dictionary
Dictionary<string, object> resultMain = new Dictionary<string, object>();
int index = 0;
foreach (DataRow dr in dt.Rows)
{
Dictionary<string, object> result = new Dictionary<string, object>();
foreach (DataColumn dc in dt.Columns)
{
result.Add(dc.ColumnName, dr[dc].ToString());
}
resultMain.Add(index.ToString(), result);
index++;
}
Notice that, I have created a new dictionary object for every row and finally put all of the dictionaries in Another dictionary I.E. resultMain.
In the end, I have simply Serialize the resultMain Dictionary to render JSON.
A complete post of utilizing Datatable in JavaScript through AJAX is in the process, I will post that soon.
In this post I will explain you how authenticate the request directly coming to access a file that is downloadable. some thing like *.pdf or *.zip.
Mostly, people make it working by creating an *.aspx page and then write binary of that file in Response.WriteFile. So, user will have no idea where the file is coming from. now this is the fair approach but what if somebody, somehow know the path of downloadable files.
So, to stop the un authenticated access to our files, we will first create a session enable HTTP handler.
public class MyHttpHandler : IHttpHandler, IReadOnlySessionState
{
public void ProcessRequest(HttpContext context)
{
if (context.Session["userId"] == null)
// I am using a session variable you can also use context.User.Identity.IsAuthenticated
{
context.Response.Redirect("/login.aspx?retUrl=" + context.Request.RawUrl);
//Redirecting to the login page ... alternatively you can also set context.Response.StatusCode
}
}
public bool IsReusable
{
get { return false; }
}
}
Now, once we have created that. Let me register my newly creater handler for *.zip and *.pdf files in web.config.
<httpHandlers>
<add verb="*" path="*.zip" type="LearningApp.MyHttpHandler, LearningApp"/>
<add verb="*" path="*.pdf" type="LearningApp.MyHttpHandler, LearningApp"/>
</httpHandlers>
That’s it. If you want more file types to be authenticated add more verbs in handler section of HttpHandler.
Don’t try to put *.* : That can create some serious problem because then each of your *.aspx, *asmx and all your logic stuff will need authentication.
Today, when I was planning to write an article on Grid View. I got a message from a very good friend of mine who is asking to disable the special keys(Windows Keys) in his application. When I start researching on it, I was thinking that it can be done using e.KeyChar but unfortunately, it is not showing any information about windows keys.
So in this post I will explain you, how can we disable the special keys (in our case windows keys) in C# Application.
1. Crete a c# windows application project
2. On the code behind of your default form add the following references
1: using System.Diagnostics;
2: using System.Runtime.InteropServices;
3. Now before the constructor of your form place the following code.
1: // Structure contain information about low-level keyboard input event
2: [StructLayout(LayoutKind.Sequential)]
3: private struct KBDLLHOOKSTRUCT
4: {
5: public Keys key;
6: public int scanCode;
7: public int flags;
8: public int time;
9: public IntPtr extra;
10: }
11:
12: //System level functions to be used for hook and unhook keyboard input
13: private delegate IntPtr LowLevelKeyboardProc(int nCode, IntPtr wParam, IntPtr lParam);
14: [DllImport("user32.dll", CharSet = CharSet.Auto, SetLastError = true)]
15: private static extern IntPtr SetWindowsHookEx(int id, LowLevelKeyboardProc callback, IntPtr hMod, uint dwThreadId);
16: [DllImport("user32.dll", CharSet = CharSet.Auto, SetLastError = true)]
17: private static extern bool UnhookWindowsHookEx(IntPtr hook);
18: [DllImport("user32.dll", CharSet = CharSet.Auto, SetLastError = true)]
19: private static extern IntPtr CallNextHookEx(IntPtr hook, int nCode, IntPtr wp, IntPtr lp);
20: [DllImport("kernel32.dll", CharSet = CharSet.Auto, SetLastError = true)]
21: private static extern IntPtr GetModuleHandle(string name);
22: [DllImport("user32.dll", CharSet = CharSet.Auto)]
23: private static extern short GetAsyncKeyState(Keys key);
24:
25:
26: //Declaring Global objects
27: private IntPtr ptrHook;
28: private LowLevelKeyboardProc objKeyboardProcess;
4. Now add the following code on your constructor.
1: public Form1()
2: {
3: ProcessModule objCurrentModule = Process.GetCurrentProcess().MainModule; //Get Current Module
4: objKeyboardProcess = new LowLevelKeyboardProc(captureKey); //Assign callback function each time keyboard process
5: ptrHook = SetWindowsHookEx(13, objKeyboardProcess, GetModuleHandle(objCurrentModule.ModuleName), 0); //Setting Hook of Keyboard Process for current module
6:
7:
8: InitializeComponent();
9: }
5. Now Implement the callback function
1: private IntPtr captureKey(int nCode, IntPtr wp, IntPtr lp)
2: {
3: if (nCode >= 0)
4: {
5: KBDLLHOOKSTRUCT objKeyInfo = (KBDLLHOOKSTRUCT)Marshal.PtrToStructure(lp, typeof(KBDLLHOOKSTRUCT));
6:
7: if (objKeyInfo.key == Keys.RWin || objKeyInfo.key == Keys.LWin) // Disabling Windows keys
8: {
9: return (IntPtr)1;
10: }
11: }
12: return CallNextHookEx(ptrHook, nCode, wp, lp);
13: }
6. Now go to your designer class and replace your dispose method.
1: /// <summary>
2: /// Clean up any resources being used.
3: /// </summary>
4: /// <param name="disposing">true if managed resources should be disposed; otherwise, false.</param>
5: protected override void Dispose(bool disposing)
6: {
7: if (disposing && (components != null))
8: {
9:
10: components.Dispose();
11: }
12: if (ptrHook != IntPtr.Zero)
13: {
14: UnhookWindowsHookEx(ptrHook);
15: ptrHook = IntPtr.Zero;
16: }
17: base.Dispose(disposing);
18: }
So, in this way we can stop the windows key operation till your application is running.You can find the VS 2008 Source code here.
In this post, I will explain how you can pass parameter to the dynamically added (from code behind) User Control. Most of you might aware of how we can achieve this in web application project. Following is the code for that
1: Dim objCon As Control = Page.LoadControl("~/Controls/MyControl.ascx")
2: Ctype(objCon,MyControl).PropertyOne = "Test"
3: Ctype(objCon,MyControl).PropertyTwo = "USAM"
4: MyPanel.Controls.add(objCon)
Now what if you have a web site project which does not have the pre-compiled assemblies and you are no more able to access the class of your user control. That is what the sum of last two ays.
Here is how you can do that by using System.reflection.
1: Dim objCon As Control = Page.LoadControl("~/Controls/MyControl.ascx")
2: 'Creating Dynamic Assmebly which holds control
3: Dim objAssembly As Assembly = Compilation.BuildManager.GetCompiledAssembly("~/Controls/MyControl.ascx")
4: 'You should definately know the name of your user control class
5: Dim objType As Type = objAssembly.GetType("Controls_MyControl")
6:
7: 'Properties
8: Dim objPropOne As PropertyInfo = objType.GetProperty("PropertyOne")
9: Dim objPropTwo As PropertyInfo = objType.GetProperty("PropertyTwo")
10:
11: 'Setting Value of Properties
12: bjPropOne.SetValue(objCon, 1, Nothing)
13: objPropTwo.SetValue(objCon, 13, Nothing)
14:
15: 'Finally placing control on the page
16: pnlCommentsCon.Controls.Add(objCon)
So in this way you can pass parameters to the dynamically added user control.
In this post I will share with you a small code snippet which will help you to get the repeater control output in string variable.
1: Dim sb As New StringBuilder()
2: Dim objHtml As New HtmlTextWriter(New System.IO.StringWriter(sb))
3:
4: If dt.Rows.Count > 0 Then
5: Repeater1.DataSource = dt
6: Repeater1.DataBind()
7: End If
8:
9: Repeater1.RenderControl(objHtml)
10: Return sb.ToString()
Well, I have used a little trick here. The RenderControl method of repeater control can put all the HTML in HtmlTextWriter Object and from HtmlTextWriter Object we have simply dump the output the to the string builder. That’s it ….
FileStream data type is a very important feature of SQL Server 2008 and gradually getting popular amongst developer for it’s feasibility. And in the past few days specially after “Configure SQL Server 2008 for File Stream” post. I received several feedbacks regarding the usage of FileStream with Ado.net and Frankly there is not much stuff available on Google for this topic.
In this post, I will guide you to use FileStream Data type in Ado.net. But before we start make sure you have configure your SQL Server 2008 instance to use File Stream Data type and for this you can read this post.
Once you finish with the Configuration execute the following script
1: CREATE TABLE [dbo].[tbl_Files](
2: [Id] [uniqueidentifier] ROWGUIDCOL NOT NULL,
3: [SystemNumber] [int] NOT NULL,
4: [SystemFile] [varbinary](max) FILESTREAM NULL,
5: [FileType] [varchar](5) NULL,
6: UNIQUE NONCLUSTERED
7: (
8: [Id] ASC
9: )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
10: ) ON [PRIMARY] FILESTREAM_ON [FileStreamGroup1]
11:
12: GO
13:
14: ALTER TABLE [dbo].[tbl_Files] ADD CONSTRAINT [DF_tbl_Files_Id] DEFAULT (newid()) FOR [Id]
This will create a table with FileStream Data type. Notice the FileType field I have used here to determine the type of file which we will use when we were downloading the file.
Now that we have created a table, lets now move on to the Stored Procedures by which we will access this newly created table.
Security Setting:
Don’t get confused with the heading, there is no additional setting required. You need to do one of the two available options. Either you need to specify “Integrated Security = true” in Connection String or you need to implement Asp.net Impersonation. It is there because SQL Server 2008 will not allow un authenticated user or instance to read/modify the file.
Most of the developers usually aware of Integrated Security stuff but let me give a little detail about asp.net impersonation. Actually, it is a way to Authorize the Instance of your asp.net application on SQL Server by using Credential Information.
Following links will help you to understand or implement impersonation.
http://blogs.msdn.com/shawnfa/archive/2005/03/21/400088.aspx
http://blogs.msdn.com/saurabhkv/archive/2008/05/29/windowsidentity-impersonation-using-c-code.aspx
http://www.west-wind.com/WebLog/posts/1572.aspx
Add Procedure:
Lets create a procedure call it “”FileAdd” and past the following script.
1: Create PROCEDURE [dbo].[FileAdd]
2: @SystemNumber int,
3: @FileType varchar(5),
4: @filepath varchar(max) output
5: AS
6: BEGIN
7: -- SET NOCOUNT ON added to prevent extra result sets from
8: -- interfering with SELECT statements.
9: SET NOCOUNT ON;
10:
11: DECLARE @ID UNIQUEIDENTIFIER
12: SET @ID = NEWID()
13:
14: INSERT INTO [dbo].[tbl_Files]
15: ([Id],[SystemNumber],SystemFile,FileType)
16: VALUES (@ID ,@SystemNumber,CAST('' AS VARBINARY(MAX)),@FileType)
17:
18: select @filepath = SystemFile.PathName() from tbl_Files where Id = @ID
19:
20:
21:
22: END
In the above procedure, we add new records in our table and just pass empty (null) to the FileStream field because we first want our SQL Server to create an empty file on NTFS location which we can access from our code behind by using the path which we have taken as Output Parameter here.
notice the SystemFile.PathName(), it is a new function introduced in SQL Server 2008 which will return the NTFS location of the file.
Get Procedure:
Create a procedure and call it “FileGet”
1: CREATE PROCEDURE [dbo].[FileGet]
2: @Id varchar(50)
3: AS
4: BEGIN
5: select SystemFile.PathName(),FileType from tbl_Files where Id = @ID
6: END
This is a simple stuff, we are returning PathName and FileType by specifying ID. Just to read the record.
Upload and Store:
To save the file in the file stream, we will use FileUpload control to upload the file and then save it to FileStream field. For that we have created a page and drag FileUpload control with an Upload button.
Now on the click event of the button write the following code.
1: byte[] buffer = new byte[(int)FileUpload1.FileContent.Length];
2: FileUpload1.FileContent.Read(buffer, 0, buffer.Length);
3:
4:
5: if (FileUpload1.FileContent.Length > 0)
6: {
7: SqlConnection objSqlCon = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
8: objSqlCon.Open();
9: SqlTransaction objSqlTran = objSqlCon.BeginTransaction();
10:
11: SqlCommand objSqlCmd = new SqlCommand("FileAdd",objSqlCon,objSqlTran);
12: objSqlCmd.CommandType = CommandType.StoredProcedure;
13:
14: SqlParameter objSqlParam1 = new SqlParameter("@SystemNumber", SqlDbType.Int);
15: objSqlParam1.Value = "1";
16:
17: SqlParameter objSqlParam2 = new SqlParameter("@FileType", SqlDbType.VarChar,4);
18: objSqlParam2.Value = System.IO.Path.GetExtension(FileUpload1.FileName);
19:
20: SqlParameter objSqlParamOutput = new SqlParameter("@filepath", SqlDbType.VarChar, -1);
21: objSqlParamOutput.Direction = ParameterDirection.Output;
22:
23: objSqlCmd.Parameters.Add(objSqlParam2);
24: objSqlCmd.Parameters.Add(objSqlParam1);
25: objSqlCmd.Parameters.Add(objSqlParamOutput);
26:
27:
28: objSqlCmd.ExecuteNonQuery();
29:
30: string Path = objSqlCmd.Parameters["@filepath"].Value.ToString();
31:
32: objSqlCmd = new SqlCommand("SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()", objSqlCon, objSqlTran);
33:
34: byte[] objContext = (byte[])objSqlCmd.ExecuteScalar();
35:
36:
37: SqlFileStream objSqlFileStream = new SqlFileStream(Path, objContext, FileAccess.Write);
38:
39: objSqlFileStream.Write(buffer, 0, buffer.Length);
40: objSqlFileStream.Close();
41:
42: objSqlTran.Commit();
Well, in the first two lines we have saved the uploaded file in byte and call this variable “buffer”.
As we are simply using ADO.net, that is why in line 7 and 8 we have created and open a connection. Where as it is worth to mention here, we need to use transaction when we want to do any operation on FileStream field that is why we have begin a new transaction in line no 9.
On line number 11 to 30, we have a simply setup command object and parameter stuff and then execute the procedure and save the output parameter in a variable called “Path”.
This new variable will contain the NTFS location of the file which is stored on SQL Server FileStream. It should be clear that, this file is empty yet as we have not stored any thing in it.
Now on line number 32 we have reused command object and this time we are executing a simple statement “GET_FILESTREAM_TRANSACTION_CONTEXT”. It is also a newly added feature in SQL Server 2008 which will return current transaction context to be used in the next few lines. Now, on line number 34 we have stored the output of the above statement in byte.
In line number 37, here is some thing new which is called “SqlFileStream”. It is a new class which you can find under “System.Data.SqlTypes”. It seems more like FileStream of “System.IO” but it should be cleared here that the file stored in FileStream field cannot be access using regular file stream object of “System.IO” we need to use SqlFileStream to access those files which are stored in FileStream field.
In line no 39 and on, we are writing the file with the content of uploaded file (Remember we have stored our uploaded file in bytes and call it “buffer”). and that’s it.
Read The Stored File:
We have finished with storing the file, now lets see how can we read this file back. To do this, Drag a Grid View and make it similar to the following
1: <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
2: onrowcommand="GridView1_RowCommand">
3: <Columns>
4: <asp:BoundField DataField="ID" HeaderText="ID" />
5: <asp:BoundField DataField="SystemNumber" HeaderText="System Id" />
6: <asp:TemplateField>
7: <ItemTemplate>
8: <asp:LinkButton ID="lbGetFile" runat="server" CommandName="GetFile" CommandArgument='<%#Eval("ID") %>' Text="Get File"></asp:LinkButton>
9: </ItemTemplate>
10: </asp:TemplateField>
11: </Columns>
12:
13: </asp:GridView>
And bind the GridView using the following code.
1: protected void bindData()
2: {
3: SqlConnection objSqlCon = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
4: objSqlCon.Open();
5:
6: SqlCommand objSqlCmd = new SqlCommand("Select * from tbl_Files", objSqlCon);
7: SqlDataAdapter objSqlDat = new SqlDataAdapter(objSqlCmd);
8: DataTable objdt = new DataTable();
9: objSqlDat.Fill(objdt);
10:
11: GridView1.DataSource = objdt;
12: GridView1.DataBind();
13: }
Well, the above markup and the code is enough self explaining but the little important stuff to mention here is the link button. We will use the same link button to download the stored file. Lets quickly move on to the RowCommand implementation of the GridView.
1: protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
2: {
3: if (e.CommandName == "GetFile")
4: {
5:
6: SqlConnection objSqlCon = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
7: objSqlCon.Open();
8: SqlTransaction objSqlTran = objSqlCon.BeginTransaction();
9:
10: SqlCommand objSqlCmd = new SqlCommand("FileGet", objSqlCon, objSqlTran);
11: objSqlCmd.CommandType = CommandType.StoredProcedure;
12:
13: SqlParameter objSqlParam1 = new SqlParameter("@ID", SqlDbType.VarChar);
14: objSqlParam1.Value = e.CommandArgument;
15:
16: objSqlCmd.Parameters.Add(objSqlParam1);
17: string path = string.Empty;
18: string fileType = string.Empty;
19:
20: using (SqlDataReader sdr = objSqlCmd.ExecuteReader())
21: {
22: while (sdr.Read())
23: {
24: path = sdr[0].ToString();
25: fileType = sdr[1].ToString();
26: }
27:
28: }
29:
30: objSqlCmd = new SqlCommand("SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()", objSqlCon, objSqlTran);
31:
32: byte[] objContext = (byte[])objSqlCmd.ExecuteScalar();
33:
34:
35: SqlFileStream objSqlFileStream = new SqlFileStream(path, objContext, FileAccess.Read);
36:
37: byte[] buffer = new byte[(int)objSqlFileStream.Length];
38: objSqlFileStream.Read(buffer, 0, buffer.Length);
39: objSqlFileStream.Close();
40:
41: objSqlTran.Commit();
42: Response.AddHeader("Content-disposition", "attachment; filename=" + Path.GetFileName(path) + fileType);
43: // Here you need to manage the download file stuff according to your need
44: Response.ContentType = "application/octet-stream";
45:
46: Response.BinaryWrite(buffer);
47:
48:
49:
50: }
51: }
Well, in the first 8 lines, we have created and opened a connection and then begin the transaction. from line no 10 to 28, we are setting the parameter stuff, executing the procedure and save the output in the two variable called “path” and “fileType”.
In line no 30 to 32, we are executing the the transaction context statement and then save the output the bytes. (Same we have done when we were writing the file)
In line no 35 to 40, we have used the same SqlFileStream and instead of writing, we are reading the file this time(notice line no 38) and save the content of the file to the bytes. Now we have file content in bytes, So we have now commit the transaction in line no 41.
In line no 42 and 44, we are setting the content type and specifying the file name with the extension. That is why, we have also saved file type in the database so that at the time of downloading we can make it available in its original state.
And in line no 46, we are simply writing the binary of the file to the browser so that it can be downloaded.
Conclusion:
I have tried my best to explain the integration of FileStream field with ADO.net, and I found this is an easy way to accomplish the task. You can download the VS 2008 solution which contain the complete source code along with procedures and table SQL.
Since, it is a new featured in SQL Server 2008 which is still in CTP by the time I am posting this stuff that is why we can expect some modifications in the method of reading and saving files using FileStream. If somebody face any challenge in the above code. Please feel to contact me.
The requirement of the day is to extract the name of the columns returned by procedures. Stored Procedures are dynamic that is why we need to create a function that takes Stored Procedure name as parameter and return the column names in string. So here is the quick snippet for that
1: Public Shared Function getMetaData(ByVal spName As String) As String()
2: Dim sqlCon As New SqlConnection(ConfigurationManager.ConnectionStrings("lmString").ConnectionString)
3: sqlCon.Open()
4:
5: Dim sqlCmd As New SqlCommand("sp_helptext " + spName, sqlCon)
6: Dim sqlDataAdapter As New SqlDataAdapter(sqlCmd)
7: Dim dt As New DataTable
8: Dim strTempQuery As String = String.Empty
9: Dim strColumns As String()
10: Dim strCol As String = String.Empty
11:
12: sqlDataAdapter.Fill(dt)
13: If dt.Rows.Count > 0 Then
14: For Each dr As DataRow In dt.Rows
15: strTempQuery += dr.Item(0)
16: Next
17: End If
18:
19: If Not strTempQuery = "" Then
20:
21: 'Dim objRegex As New Regex("select([^<]*)from")
22:
23:
24: Dim objMatches As MatchCollection = Regex.Matches(strTempQuery, "select([^<]*)from", RegexOptions.IgnoreCase)
25:
26: For Each mymatch As Match In objMatches
27: strCol += mymatch.Groups(1).Value
28: Next
29:
30: If Not strCol = "" Then
31: strColumns = strCol.Split(",")
32: For a As Integer = 0 To strColumns.Length - 1
33: strColumns(a) = strColumns(a).Trim()
34: Next
35: End If
36: End If
37: Return strColumns
38: End Function
Restriction : Though, we have achieved the target, but since we have used sp_helptext to extract the Stored Procedure data that is why it is not possible to process encrypted stored procedure.
Will make it more better in the future to accommodate all type of Stored Procedures.
This is on the request of some of my readers to show how effective Asp.net menu control is by using CSS Control Adapter. Most of the folks either have no idea of what CSS Adapter is or have some problem in integrating that with their applications.
So, In this post I will brief you guys how can we use CSS Adapter to format the design of Asp.net Menu Control. Before we start, let me dig out why would somebody use CSS Adapter and what does that do ?
Have you ever notice by viewing the source of your page what asp.net runtime engine generate when you use any Data list, or in our case Menu Control.
It generates Table based layout, which is of course really difficult to design and not consider a good practice in the new web standards. To overcome that issue CSS Control Adapter is the answer. It will rendered div and unorderlist (UL) instead of table which can easily be redesign using CSS. It means, you can now have standardized approach to create web based controls. If you want to see what CSS Control Adapter provide you, Click Here and notice the HTML Snippet given at bottom.
Ok, to start off lets download the source code and open the project. By the time I am writing this post only VS 2005 version of CSS Control Adapter is available. But that is not an issue, If you are using VS 2008, simply convert the project. Even if you don’t want to open the project it still have no problem as we only need to copy paste some stuff from here.
Create A Web Application or Web Site Project in which you want to implement Menu Control. right click on your project and add special folder called App_Browser. Now Right Click on the newly created folder and Add Browser File and Name it “CSSFriendlyAdapters.browser”.
You can either write the following stuff in it or Just Copy / Paste this from CSS Control Adapter Project which you have downloaded before.
1: <browsers>
2: <browser refID="Default">
3: <controlAdapters>
4: <adapter controlType="System.Web.UI.WebControls.Menu"
5: adapterType="CSSFriendly.MenuAdapter" />
6:
7: </controlAdapters>
8: </browser>
9:
10: <browser id="W3C_Validator" parentID="default">
11: <identification>
12: <userAgent match="^W3C_Validator" />
13: </identification>
14: <capabilities>
15: <capability name="browser" value="W3C Validator" />
16: <capability name="ecmaScriptVersion" value="1.2" />
17: <capability name="javascript" value="true" />
18: <capability name="supportsCss" value="true" />
19: <capability name="supportsCallback" value="true" />
20: <capability name="tables" value="true" />
21: <capability name="tagWriter" value="System.Web.UI.HtmlTextWriter" />
22: <capability name="w3cdomversion" value="1.0" />
23: </capabilities>
24: </browser>
25: </browsers>
If you see the browser file available in CSS Control Adapter project you will realize that under ControlAdapter tag there are several other ControlType specified. But in our case as we are only using Menu Control so we have removed the un wanted stuff.
Now once you have done this, you need to add reference to the CSS Adapter. You can find the assembly in CSS Control Adapter Project.
Lets create a page and drop and Menu control. See the following snippet.
1: <asp:Menu ID="Menu1" runat="server" Orientation="Horizontal" CssSelectorClass="SimpleEntertainmentMenu">
2: <Items>
3: <asp:MenuItem Text="Item 1" Value="Item 1">
4: <asp:MenuItem Text="Item a" Value="Item a">
5: <asp:MenuItem Text="Item a - a" Value="Item a - a"></asp:MenuItem>
6: </asp:MenuItem>
7: <asp:MenuItem Text="Item B" Value="Item B"></asp:MenuItem>
8: </asp:MenuItem>
9: <asp:MenuItem Text="Item 2" Value="Item 2">
10: <asp:MenuItem Text="Item a" Value="Item a"></asp:MenuItem>
11: </asp:MenuItem>
12: </Items>
13: </asp:Menu>
Now we need to create a CSS file and linked that with the page we have created. The CSS File should like as below
1: .SimpleEntertainmentMenu ul.AspNet-Menu /* Tier 1 */
2: {
3: width: 13em; /* This is more than (6em x 2) because we want to leave room for borders around the <li> elements that are selected */
4: }
5:
6: .SimpleEntertainmentMenu ul.AspNet-Menu ul /* Tier 2 */
7: {
8: width: 6em;
9: top: 100%;
10: left: 0;
11: font-weight:bold;
12: }
13:
14: .SimpleEntertainmentMenu ul.AspNet-Menu ul ul /* Tier 3+ */
15: {
16: top: 0%;
17: left: 6em;
18: font-weight:normal;
19: }
20:
21: .SimpleEntertainmentMenu li /* all list items */
22: {
23: width: 6em;
24: background: #efefef;
25: }
26:
27: .SimpleEntertainmentMenu li:hover, /* list items being hovered over */
28: .SimpleEntertainmentMenu li.AspNet-Menu-Hover
29: {
30: background: Black;
31: }
32:
33: .SimpleEntertainmentMenu a, /* all anchors and spans (nodes with no link) */
34: .SimpleEntertainmentMenu span
35: {
36: color: Black;
37: padding: 4px 2px 4px 8px;
38: border:1px solid #cccccc;
39: background: transparent url(arrowRight.gif) right center no-repeat;
40: }
41:
42: .SimpleEntertainmentMenu li.AspNet-Menu-Leaf a, /* leaves */
43: .SimpleEntertainmentMenu li.AspNet-Menu-Leaf span
44: {
45: background-image: none !important;
46: }
47:
48: .SimpleEntertainmentMenu li:hover a, /* hovered text */
49: .SimpleEntertainmentMenu li:hover span,
50: .SimpleEntertainmentMenu li.AspNet-Menu-Hover a,
51: .SimpleEntertainmentMenu li.AspNet-Menu-Hover span,
52: .SimpleEntertainmentMenu li:hover li:hover a,
53: .SimpleEntertainmentMenu li:hover li:hover span,
54: .SimpleEntertainmentMenu li.AspNet-Menu-Hover li.AspNet-Menu-Hover a,
55: .SimpleEntertainmentMenu li.AspNet-Menu-Hover li.AspNet-Menu-Hover span,
56: .SimpleEntertainmentMenu li:hover li:hover li:hover a,
57: .SimpleEntertainmentMenu li:hover li:hover li:hover span,
58: .SimpleEntertainmentMenu li.AspNet-Menu-Hover li.AspNet-Menu-Hover li.AspNet-Menu-Hover a,
59: .SimpleEntertainmentMenu li.AspNet-Menu-Hover li.AspNet-Menu-Hover li.AspNet-Menu-Hover span
60: {
61: color: White;
62: background: transparent url(activeArrowRight.gif) right center no-repeat;
63: }
64:
65: .SimpleEntertainmentMenu li:hover li a, /* the tier above this one is hovered */
66: .SimpleEntertainmentMenu li:hover li span,
67: .SimpleEntertainmentMenu li.AspNet-Menu-Hover li a,
68: .SimpleEntertainmentMenu li.AspNet-Menu-Hover li span,
69: .SimpleEntertainmentMenu li:hover li:hover li a,
70: .SimpleEntertainmentMenu li:hover li:hover li span,
71: .SimpleEntertainmentMenu li.AspNet-Menu-Hover li.AspNet-Menu-Hover li a,
72: .SimpleEntertainmentMenu li.AspNet-Menu-Hover li.AspNet-Menu-Hover li span
73: {
74: color: Black;
75: background: transparent url(arrowRight.gif) right center no-repeat;
76: }
77:
78: .SimpleEntertainmentMenu .AspNet-Menu-Selected /* this tier is selected */
79: {
80: border: solid 1px #00ff00 !important;
81: }
82:
83: .SimpleEntertainmentMenu .AspNet-Menu-ChildSelected /* a tier below this one is selected */
84: {
85: border: solid 1px #ff0000 !important;
86: }
87:
88: .SimpleEntertainmentMenu .AspNet-Menu-ParentSelected /* a tier above this one is selected */
89: {
90: border: solid 1px #0000ff !important;
91: }
92:
93: #EntertainmentMessage
94: {
95: padding-top: 2em;
96: clear: both;
97: }
Well that is pretty self describing, as I have already mention that the CSS Control adapter will rendered Divs and Unorder lists instead of table for Menu Control. Here we are simply specifying the style for Menu element on different level.
Once you have complete with the creation of CSS file, you need to link this with your page and for that
1: <link rel="stylesheet" href="/CSS/SimpleMenu.css" type="text/css" />
I have created the CSS File under CSS folder, which can be some thing else in your case.
That is it, it is pretty simple yet effective to use CSS Control Adapter with your asp.net application because it can give standardized HTML as output which is easy to design.
You can download the VS 2008 project file.
Well, from past two days I am working on SQL Server 2008 new feature called File Stream. In the period of SQL Server 2005 when we want to store some files to the database we can have that using varbinary(max) but that approach is not either smart nor popular amongst the developers. So, many developers like me wants to store images on any physical location and keep the file location in the table. But, that have issues too, what if somebody delete the files from physical location ? will file entries in the database also deleted and what if somebody deleted the records using t/sql will the files on the physical location also deleted.
In nutshell, both the previous approaches have issues. So, this File Stream data type can replace the problem we had before. It will save the file to the physical location and store the stream of of that file to the table. In my opinion, that is the smart approach.
So, let us dig down and see how can we configure file stream to the new SQL Server 2008 instance , Database and then Table.
Getting Your SQL Server 2008 Instance Ready for File Stream:
- Goto Start > Programs > Sql Server 2008 > Configuration Tools > SQL Server Configuration Manager
- Now that Configuration Manager is open, right click on the default instance and go to properties.
- On this form, Go to File Stream Tab and Enable the file stream. See the image below
Ok, let me brief you the option we have here.
- Enable FileStream for transact-sql access : This way you can access the file using t/sql
- Enable FileStream for File I/O streaming access : By checking this you can access the files using IO Stream
- All remote clients to have streaming access to file stream data : Here you are allowing remote connections to play around with File Stream Files.
Once you finish with this you need to set the access level by run the following query. Please bear in mind that the following query will not work until you set FileStream stuff from Configuration manager.
1: EXEC sp_configure filestream_access_level, 2 -- 0 : Disable , 1 : Transact Sql Access , 2 : Win IO Access
2: GO
3: RECONFIGURE
4: GO
Or alternatively, you can
- Go to SQL Server Management Studio
- Right Click the database server then properties
- From the properties window select advance and you will see the following screen
- Now simply select the access level you want for your server.
Enable database to have FileStream Data type:
Now, you have done with the sql server instance configuration. Let’s move to Database Configuration. How can we create a database which is FileStream Supported.
For that there are two options.
- Using T-sql
- Using Management Studio
Using T-Sql
For that you need to run the following query
1: CREATE DATABASE Learning_Db
2: ON
3: PRIMARY ( NAME = LearnDb1,
4: FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\Learning_Db .mdf'),
5: FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM( NAME = LearnDb2,
6: FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\Learning_DbStream')
7: LOG ON ( NAME = LearnDbLog1,
8: FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\Learning_Db.ldf')
9: GO
Ok now, notice that along with the Primary and Log file we have one new file group which we use for FileStream. Remember we have discuss above that FileStream will save a file on a physical location and store the stream in the the database which will later use for accessing that file.
Now, when you go to the location where we create our new database you will see there is a folder (in our case it should be “Learning_DbStream”). This folder contain all the files of your FileStream.
Using Management Studio
While creating a new database window go to file group, you will find the File Stream section at the bottom. See image below
Note : If your database is already created, you can set FileStream stuff by right click your database and then properties and then file group
For using the FileStream, you need to add a File Stream Group here and make it default. Once you add that, Go to the Files and add a new file
- Give Logical name In our case we have used “LearningDb_FileStream” and then Select “FileStream Data” from file type.
- After that, you only need to set the path and that’s it
- Click Ok, and now your database is ready to play with FileStream.
Using FileStream Data type in table:
Now, for using the FileStream data type in table you need to create a Unique Column which is off uniqueidentified datatype and a column that uses varbinary(max) to store the Stream. Here is the SQL for that
1: CREATE TABLE [dbo].[tbl_Files](
2: [Id] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE,
3: [SystemNumber] [int] NOT NULL,
4: [SystemFile] [varbinary](max) FILESTREAM NULL
5: ) ON [PRIMARY]
6:
7: GO
Just give this a Go and you are all done with FileStream configuration.
There are times when we need to group by the table with the date. But Datetime field also contain time part which is very deep. So, there is no way you can group by datetime field and see correct records because it will group by including the time portion of DateTime field.
So, to get rid off the time portion in group by clause here is a quick query.
1: select dateadd(dd,0, datediff(dd,0,dateCreated)) as Date,count(*) TotalCount from tblRecords
2: group by dateadd(dd,0, datediff(dd,0,dateCreated))
Currently, by the time I am posting this stuff SQL Server 2008 is in CTP. And off course, it has several installation issues. For the very first time, when I sit for the installation of SQL Server 2008 Express believe me I run the setup up to four times. So let me share with you, you must need to have the following items installed on your system.
Without installing the above component, We cannot Install SQL Server 2008.
Finally Google Analytics rectifies its spell mistake. For detail please see this post. For detail please see this post.