Sometimes we have to face a critical situation when SQL Server database going to Suspect Mode. In that moment no work can be done on database. Database may go into suspect mode because the primary file group is damaged and the database cannot be recovered during the startup of the SQL Server
Reason for database to go into suspect mode:

• Data files or log files are corrupt.
• Database server was shut down improperly
• Lack of Disk Space
• SQL cannot complete a rollback or roll forward operation

    
How to recover database from suspect mode:

• Change the status of your database. Suppose database name is “BluechipDB”?

EXEC sp_resetstatus '';
Example:
EXEC sp_resetstatus 'BlueChipDB'

• Set the database in “Emergency” mode

ALTER DATABASE  SET EMERGENCY;
Example:
ALTER DATABASE BlueChipDB SET EMERGENCY

• Check the database for any inconsistency

DBCC CHECKDB('');

Example:
DBCC checkdb('BlueChipDB')

If you get any error after executing DBCC CHECKDB then immediately bring the database in SINGLE USER MODE by running following query. If no error found then you need not execute the following query.

ALTER DATABASE  SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Example:
ALTER DATABASE BlueChipDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE

• For safety, take the backup of the database.
• Run the following query as next step.Remember while using the below query, that uses REPAIR_ALLOW_DATA_LOSS, is a one way operation that is once the database is repaired all the actions performed by these queries can’t be undone.
• There is no way to go back to the previous state of the database.
• So as a precautionary step you should take backup of your database in step 5 mentioned above.

DBCC CHECKDB ('', REPAIR_ALLOW_DATA_LOSS);

Example:
DBCC CheckDB ('BlueChipDB', REPAIR_ALLOW_DATA_LOSS)

• Finally, bring the database in MULTI USER mode

ALTER DATABASE  SET MULTI_USER;
ALTER DATABASE [BlueChipDB]  SET MULTI_USER

• Refresh your database server and verify the connectivity of your database. Now users should be able to connect to the database properly. If any data loss, you can restore database – backup taken in step 5.

HostForLIFE.eu SQL 2016 Hosting
European best, cheap and reliable ASP.NET hosting with instant activation. HostForLIFE.eu is #1 Recommended Windows and ASP.NET hosting in European Continent. With 99.99% Uptime Guaranteed of Relibility, Stability and Performace. HostForLIFE.eu security team is constantly monitoring the entire network for unusual behaviour. We deliver hosting solution including Shared hosting, Cloud hosting, Reseller hosting, Dedicated Servers, and IT as Service for companies of all size.

In this tutorial, I will explain about inline queries. Most of the developers are very familiar with inline queries. Inline queries are basically known as sub queries or Inner Select statements which are always used inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another inline query.  They can be used anywhere in SQL scripts to encounter the conditional expressions. The SQL statement containing an Inline query is also known an outer query or outer select.

How can Inline Queries helpful?

Most of the T-SQL statements which include inline queries can be alternatively formulated as join to encounter the conditional expressions. Other questions can be posed only with inline queries. In T-SQL, there is usually no performance difference between a statement that includes an inline query and a semantically equivalent version that does not. However, in some cases where existence must be checked, a join yields better performance.

Components of an Inline Query

An inline query has the same features as a normal SQL query. They only persist in the SQL statements and could have the following components –

• A regular SELECT query including regular select list components from the main table.
• A regular FROM clause including one or more table, function or view names.
• An optional WHERE clause to encounter the conditional expressions.
• An optional GROUP BY clause if your query contains aggregation functions.
• An optional HAVING clause if your query contains aggregation functions.

Points to be remember

The SELECT query of an Inline query is always enclosed in parentheses. It cannot include a COMPUTE or FOR BROWSE clause, and may only include an ORDER BY clause when a TOP clause is also specified. There are three basic types of inline queries-
Operate on lists introduced with IN, or those that a comparison operator modified by ANY or ALL.
Are introduced with an unmodified comparison operator and must return a single value.
Are existence tests introduced with EXISTS.

To understand their functionality, we will create a table variable to work as the base table for the inline statements as given below-

---- declare OrderMaster table variable body
DECLARE @OrderMaster TABLE
(
OrdId INT IDENTITY(100,1),
OrdDate VARCHAR(30),
CustName VARCHAR(20)
)
---- declare OrderDetails table variable body
DECLARE @OrderDetails TABLE
(
OrdId INT,
ItemId VARCHAR(30),
ItemName VARCHAR(20),
SellingPrice Decimal(12,4)
)
---- Insert Values
INSERT INTO @OrderMaster (OrdDate,CustName)
Values (Getdate(),'Ryan Arjun'),(Getdate(),'Bill Trade'),
(Getdate(),'Rosy White')

---- Pull Order Data
SELECT * FROM @OrderMaster

----Fill Order Details
Insert Into @OrderDetails (OrdId, ItemId, ItemName, SellingPrice)
Values (100, 201,'Apple',135.78),(100, 202,'Mango',235.78)
,(101, 203,'Banana',124.50),(101, 204,'Orange',321.15)
,(102, 205,'Banana',124.50),(102, 204,'Orange',321.15),(102, 201,'Apple',135.78)

--- Pull Order Details
select * from @OrderDetails

Now, we have the tables and want to pull the order and customer wise total sales.

Pull Single Value

There are many ways to do this but we are using inline query to accomplish this task. To pull the single value, we are using inline query within the main SQL statement as given below-

---- Use inline query in the select statement
select OrdId, CustName,
---- Inline query for single value
[Price] =(Select 
[Price]=sum(SellingPrice)
from @OrderDetails
where OrdId=  Om.OrdId
)
from @OrderMaster OM

Pull Multiple Values with Group By
If we want to pull more than one column then inline query should work as join with the main query as given below:

---- Inline Query as Join
SELECT Om.OrdId, Om.CustName,
OD.[Item-Qty], OD.Price
FROM @OrderMaster OM
JOIN
(
SELECT  OrdId,
[Item-Qty]=COUNT(ItemId),
[Price]=SUM(SellingPrice)
FROM @OrderDetails
GROUP BY ORDID
)OD
ON OM.ORDID=OD.ORDID

Pull Value with Where Clause and Group By
If we want to pull more than one column based on some conditional expression then inline query should work as join with the main query as given below:

---- Inline Query as Join
SELECT Om.OrdId, Om.CustName,
OD.[Item-Qty], OD.Price
FROM @OrderMaster OM
JOIN
(SELECT  OrdId,
[Item-Qty]=COUNT(ItemId),
[Price]=SUM(SellingPrice)
FROM @OrderDetails
---- Conditional Expression
WHERE ItemName='Apple'
GROUP BY ORDID
)OD
ON OM.ORDID=OD.ORDID

Conclusion

It’s very beneficial concept in SQL and we can use them inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another inline query.  They are easily applicable in the function and stored procedure. These features are important in some Transact-SQL statements; the inline-query can be evaluated as if it were an independent query. Conceptually, the inline-query results are substituted into the outer query.

HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

HostForLIFE.eu was established to cater to an underserved market in the hosting industry; web hosting for customers who want excellent service. HostForLIFE.eu - a cheap, constant uptime, excellent customer service, quality, and also reliable hosting provider in advanced Windows and ASP.NET technology. HostForLIFE.eu proudly announces the availability of the ASP.NET Core 1.0 RC2 hosting in their entire servers environment.

From SQL Server 2012 version, when SQL Server instance is restarted, then table's Identity value is jumped and the actual jumped value depends on identity column data type. If it's integer (int) data type, then jump value is a thousand and if huge integer (bigint), then jump value is ten thousand. From our application point of view, this increment isn't acceptable for all the business cases especially once the value shows to the client. this is the special case/issue ships with only SQL Server 2012 and older versions don't have any such issue.

A few days ago, our QA Engineer claims that one of our table's identity column jumped ten thousand. Which means the last identity value of that table was 2200 now it's 12001. In our business logic is like that the value shows to the client and it will not be accepted by the client. So we must solve the issue.
Using the Code

The first time, we all are surprised and confused on how it's possible? we usually don't insert any value in identity column (insert value to identity column is possible). The identity value is maintained by SQL Server itself. one of our core team members started investigation the issue and found out the solution. Now, i want to elaborate the issue and solution that was found out by my colleague.

Now, you need to setup SQL Server 2012 and create a test database. Then create a table with auto identity column with the following code:
create table MyTestTable(Id int Identity(1,1), Name varchar(255));

Now insert 2 rows there:
insert into MyTestTable(Name) values ('Mr.Tom');
insert into MyTestTable(Name) values ('Mr.Jackson');

You see the result:
SELECT Id, Name FROM MyTestTable;

The result is as expected. Now just restart your SQL Server service. There are various ways in which you can do it. We did it from SQL Server management studio.

Now, insert another 2 rows to the same table again:
insert into MyTestTable(Name) values ('Mr.Tom2');
insert into MyTestTable(Name) values ('Mr.Jackson2');

Now, you can see the result:
SELECT Id, Name FROM MyTestTable;

Now you see that after restarting the SQL Server 2012 instance, then identity value starts with 1002. It means it jumped 1000. Previously, I said that we also see if the data type of that identity column is bigint, then it will jump 10000.

Microsoft declares it is a feature rather than a bug and in many scenarios it would be helpful. But in our case, it would not be acceptable because that number is shown to the client and the client will be surprised to see that new number after jump and the new number depends on how many times SQL Server is restarted. If it is not visible to the client, then it might be acceptable so that the number is used internally.
Solutions

If we are not interested in this so called feature, then we can do two things to stop that jump.
    Using Sequence
  Register -t272 to SQL Server Startup Parameter

Using Sequence
First, we need to remove Identity column from tables. Then create a sequence without cache feature and insert number from that sequence. The following is the code sample:
CREATE SEQUENCE Id_Sequence
    AS INT
    START WITH 1
    INCREMENT BY 1
    MINVALUE 0
    NO MAXVALUE
   NO CACHE

   insert into MyTestTable values(NEXT VALUE FOR Id_Sequence, 'Mr.Tom');

   insert into MyTestTable values(NEXT VALUE FOR Id_Sequence, 'Mr.Jackson');

How to Register -t272 to SQL Server Startup Parameter?
First, open the SQLServer configuration manager from your server. Select SQL Server 2012 instance there right client and select Properties menu. You will find a tabbed dialog window. You select start up parameters tab from there and register -t272. Then restart SQL Server 2012 instance again and see the difference as you can see on the following picture:

If too many tables contain identity column to your database and all contain existing values, then it is better to go for solution 2. Because it is a very simple solution and its scope is server wise. This means if you add SQL Server 2012 parameter -t272 there, then it will affect all your databases there. If you want to create a new database and you need auto generated number field, then you can use solution 1, that means use sequence value to a column instead of auto Identity value. There are so many articles you can find online about when you will use auto identity column when using sequence and advantages/disadvantages of each other. I hope you will read all those and take the appropriate decision.

HostForLIFE.eu SQL Server 2012 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

That happens because sometimes it is necessary to drop and recreate a table in order to change something. This can take a while, since all data must be copied to a temp table and then re-inserted in the new table. Since SQL Server by default doesn't trust you, you need to say "OK, I know what I'm doing, now let me do my work." This problem occurs when the Prevent saving changes that require the table re-creation option is enabled, and you make one or more of the following changes to the table:

• You change the Allow Nulls setting for a column.
• You reorder columns in the table.
• You change the column data type.
• You add a new column.

When you change a table so that you alter the metadata structure of the table, and then you save the table, the table must be re-created based on these changes. This may result in the loss of metadata and in a direct loss of data during the re-creation of the table. If you enable the Prevent saving changes that require the table re-creation option in the Designer section of the SQL Server Management Studio (SSMS) Options window, you receive the error message that is mentioned in the “Symptoms” section.

To change the Prevent saving changes that require the table re-creation option, follow these steps:

• Open SQL Server Management Studio (SSMS).
• On the Tools menu, click Options.
• In the navigation pane of the Options window, click Designers.
• Select or clear the Prevent saving changes that require the table re-creation check box, and then click OK.

Note If you disable this option, you are not warned when you save the table that the changes that you made have changed the metadata structure of the table. In this case, data loss may occur when you save the table.

Risk of turning off the “Prevent saving changes that require table re-creation” option

Although turning off this option can help you avoid re-creating a table, it can also lead to changes being lost. For example, suppose that you enable the Change Tracking feature in SQL Server to track changes to the table. When you perform an operation that causes the table to be re-created, you receive the error message that is mentioned in the “Symptoms” section. However, if you turn off this option, the existing change tracking information is deleted when the table is re-created. Therefore, we recommend that you do not work around this problem by turning off the option.

To determine whether the Change Tracking feature is enabled for a table, follow these steps:

• In SQL Server Management Studio, locate the table in Object Explorer.
• Right-click the table, and then click Properties.
• In the Table Properties dialog box, click Change Tracking.

If the value of the Change Tracking item is True, this option is enabled for the table. If the value is False, this option is disabled.

When the Change Tracking feature is enabled, use Transact-SQL statements to change the metadata structure of the table.

Steps to reproduce the problem

• In SQL Server Management Studio, create a table that contains a primary key in the Table Designer tool.
• Right-click the database that contains this table, and then click Properties.
• In the Database Properties dialog box, click Change Tracking.
• Set the value of the Change Tracking item to True, and then click OK.
• Right-click the table, and then click Properties.
• In the Table Properties dialog box, click Change Tracking.
• Set the value of the Change Tracking item to True, and then click OK.
• On the Tools menu, click Options.
• In the Options dialog box, click Designers.
• Click to select the Prevent saving changes that require table re-creation check box, and then click OK.
• In the Table Designer tool, change the Allow Nulls setting on an existing column.
• Try to save the change to the table.

HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

It is not smart to execute multiple db request for loading single page.  Review your database code to see if you have got request paths that go to the database quite once. each of these round-trips decreases the number of requests per second your application will serve. By returning multiple resultsets in a single database request, you can cut the total time spent communicating with the database.

In order to enhance performance you should execute single keep proc and bring multiple resultset in to single dB request. In this article i will explain you how to avoid multiple database request and how to bring multiple resultset into single dB request. Consider a scenario of loading a Product Page, which displays:
Product information
Product Review information.

In order to bring two information request in single dB request, your sql server keep proc ought to be declared as below.

SQL Server Stored Proc

CREATE PROCEDURE GetProductDetails
 @ProductId bigint,
AS
SET NOCOUNT ON

--Product Information
Select ProductId,
 ProductName,
 ProductImage,
 Description,
 Price
From Product
Where ProductId = @ProductId


--Product Review Information
Select  ReviewerName,
 ReviewDesc,
 ReviewDate
From ProductReview
Where ProductId = @ProductId

Asp.net, C# Code to bring multiple db request into single db request
Code Inside Data Access Class Library (DAL)
public DataSet GetProductDetails()
{
SqlCommand cmdToExecute = new SqlCommand();
cmdToExecute.CommandText = "GetProductDetails";
cmdToExecute.CommandType = CommandType.StoredProcedure;
DataSet dsResultSet = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter(cmdToExecute);

try
{
    var conString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnStr"];
    string strConnString = conString.ConnectionString;
    SqlConnection conn = new SqlConnection(strConnString);

    cmdToExecute.Connection = conn;

    cmdToExecute.Parameters.Add(new SqlParameter("@ ProductId", SqlDbType.BigInt, 8, ParameterDirection.Input, false, 19, 0, "", DataRowVersion.Proposed, _productId));

    //Open Connection
    conn.Open();

    // Assign proper name to multiple table
    adapter.TableMappings.Add("Table", "ProductInfo");
    adapter.TableMappings.Add("Table1", "ProductReviewInfo");
    adapter.Fill(dsResultSet);

    return dsResultSet;             
}
catch (Exception ex)
{
    // some error occured.
    throw new Exception("DB Request error.", ex);
}
finally
{
    conn.Close();
    cmdToExecute.Dispose();
    adapter.Dispose();
}
}

Code Inside Asp.net .aspx.cs page
protected void Page_Load(object sender, EventArgs e)
{
   if (Request.QueryString[ProductId] != null)
   {
      long ProductId = Convert.ToInt64(Request.QueryString[ProductId].ToString()); 
  
      DataSet dsData = new DataSet();

      //Assuming you have Product class in DAL
      ProductInfo objProduct = new ProductInfo();
      objProduct.ProductId = ProductId;
      dsData = objProduct.GetProductDetails();

      DataTable dtProductInfo = dsData.Tables["ProductInfo"];
      DataTable dtProductReviews = dsData.Tables["ProductReviewInfo"];

      //Now you have data table containing information
      //Make necessary assignment to controls
      .....
      .....
      .....
      .....
      ..... 

    }
}

HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

In this article I am going to explain how to save the youtube into database and display it in Gridview using Linq. I have got a requirement to save the youtube video URL into database and display them where user can play the videos. I have created a table Tb_videos which store the information video tile, description and URL as you can see on the following :

HTML Markup:
<table>  
<tr>
<td>Title :</td>
 <td>
     <asp:TextBox ID="txttitle" runat="server" Width="450px"></asp:TextBox></td>
</tr>
<tr>
<td></td>
 <td></td>
</tr>
   <tr>
<td>Description :</td>
 <td><asp:TextBox ID="txtdescription" runat="server" TextMode="MultiLine" Width="450px"></asp:TextBox></td>
</tr>
<tr>
<td></td>
 <td></td>
</tr>
   <tr>
<td>Video URL :</td>
 <td><asp:TextBox ID="txturl" runat="server" Width="450px"></asp:TextBox></td>
</tr>
<tr>
<td></td>
 <td></td>
</tr>
   <tr>
<td></td>
 <td>
     <asp:Button ID="btnsubmit" runat="server" Text="Submit" /></td>
 </tr>
</table>
<br />
    <asp:GridView ID="grdvideo" runat="server" AutoGenerateColumns="False"
        BackColor="White" BorderColor="#DEDFDE" BorderStyle="None" BorderWidth="1px"
        CellPadding="4" ForeColor="Black" GridLines="Vertical">
        <AlternatingRowStyle BackColor="White" />
    <Columns>
    <asp:BoundField DataField="Title" HeaderText="Title" />
    <asp:BoundField DataField="Description" HeaderText="Description"/>
   <asp:TemplateField HeaderText="Videos">
   <ItemTemplate>
   <object  width="480" height="385">
<embed src='<%#Eval("url") %>' type="application/x-shockwave-flash" allowscriptaccess="always" allowfullscreen="true" width="480" height="385">
</embed>
</object> 
   </ItemTemplate>
   </asp:TemplateField>
    </Columns>
        <FooterStyle BackColor="#CCCC99" />
        <HeaderStyle BackColor="#6B696B" Font-Bold="True" ForeColor="White" />
        <PagerStyle BackColor="#F7F7DE" ForeColor="Black" HorizontalAlign="Right" />
        <RowStyle BackColor="#F7F7DE" />
        <SelectedRowStyle BackColor="#CE5D5A" Font-Bold="True" ForeColor="White" />
        <SortedAscendingCellStyle BackColor="#FBFBF2" />
        <SortedAscendingHeaderStyle BackColor="#848384" />
        <SortedDescendingCellStyle BackColor="#EAEAD3" />
        <SortedDescendingHeaderStyle BackColor="#575357" />
    </asp:GridView>

Import the namespace
C#:
using System.Text.RegularExpressions;

VB:
Imports System.Text.RegularExpressions

Create the object of DBML:
C#:
BlogDataContext db = new BlogDataContext();

VB:
Private db As New BlogDataContext()

Get the Youtube video id
Write a function to get the youtube video id from youtube video URL .

C# Code:
private string GetYouTubeVideoID(string youTubeVideoUrl)
{
    var regexMatch = Regex.Match(youTubeVideoUrl, "^[^v]+v=(.{11}).*",
                       RegexOptions.IgnoreCase);
    if (regexMatch.Success)
    {
        return "http://www.youtube.com/v/" + regexMatch.Groups[1].Value +"&hl=en&fs=1";
    }
    return youTubeVideoUrl;
}

VB Code:
Private Function GetYouTubeVideoID(ByVal youTubeVideoUrl As String) As String
    Dim regexMatch = Regex.Match(youTubeVideoUrl, "^[^v]+v=(.{11}).*", RegexOptions.IgnoreCase)
    If regexMatch.Success Then
        Return "http://www.youtube.com/v/" + regexMatch.Groups(1).Value + "&hl=en&fs=1"
    End If
    Return youTubeVideoUrl
End Function

Save the record to database
On button click write the below given code to insert the record into database table.

C# Code:
protected void btnsubmit_Click(object sender, EventArgs e)
{
    try
    {
        string ytFormattedVideoUrl = GetYouTubeVideoID(txturl.Text);
        Tb_Video tb = new Tb_Video();
        tb.Title = txttitle.Text;
        tb.Description = txtdescription.Text;
        tb.URL = ytFormattedVideoUrl;
        db.Tb_Videos.InsertOnSubmit(tb);
        db.SubmitChanges();
        Response.Write("<script>alert('Record Inserted Successfully');</script>");
        BindGrid();
        Clear();
    }
    catch (Exception ex)
    { }
}
public void Clear()
{
    txturl.Text = string.Empty;
    txtdescription.Text = string.Empty;
    txttitle.Text = string.Empty;
}

VB Code:
Protected Sub btnsubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnsubmit.Click
    Try
        Dim ytFormattedVideoUrl As String = GetYouTubeVideoID(txturl.Text)
        Dim tb As New Tb_Video()
        tb.Title = txttitle.Text
        tb.Description = txtdescription.Text
        tb.URL = ytFormattedVideoUrl
        db.Tb_Videos.InsertOnSubmit(tb)
        db.SubmitChanges()
        Response.Write("<script>alert('Record Inserted Successfully');</script>")
        BindGrid()
        Clear()
    Catch ex As Exception
    End Try
End Sub
Public Sub Clear()
    txturl.Text = String.Empty
    txtdescription.Text = String.Empty
    txttitle.Text = String.Empty
End Sub

Fetch the record from database and display
Create a method to get the record from database table and display in Gridview data control.

C# Code:
protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        BindGrid();
    }
}

public void BindGrid()
{
    try
    {
        var bind = from v in db.Tb_Videos
                   select v;
        grdvideo.DataSource = bind;
        grdvideo.DataBind();
    }
    catch (Exception ex)
    {
    }
}

VB Code:
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not IsPostBack Then
        BindGrid()
    End If
End Sub

Public Sub BindGrid()
    Try
        Dim bind = From v In db.Tb_Videos
        grdvideo.DataSource = bind
        grdvideo.DataBind()
    Catch ex As Exception
    End Try
End Sub

Build the project and run. To test the application inserts a record into database.

HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

In this article I am going to explain how to save the youtube into database and display it in Gridview using Linq. I have got a requirement to save the youtube video URL into database and display them where user can play the videos. I have created a table Tb_videos which store the information video tile, description and URL as you can see on the following :

HTML Markup:
<table>  
<tr>
<td>Title :</td>
 <td>
     <asp:TextBox ID="txttitle" runat="server" Width="450px"></asp:TextBox></td>
</tr>
<tr>
<td></td>
 <td></td>
</tr>
   <tr>
<td>Description :</td>
 <td><asp:TextBox ID="txtdescription" runat="server" TextMode="MultiLine" Width="450px"></asp:TextBox></td>
</tr>
<tr>
<td></td>
 <td></td>
</tr>
   <tr>
<td>Video URL :</td>
 <td><asp:TextBox ID="txturl" runat="server" Width="450px"></asp:TextBox></td>
</tr>
<tr>
<td></td>
 <td></td>
</tr>
   <tr>
<td></td>
 <td>
     <asp:Button ID="btnsubmit" runat="server" Text="Submit" /></td>
 </tr>
</table>
<br />
    <asp:GridView ID="grdvideo" runat="server" AutoGenerateColumns="False"
        BackColor="White" BorderColor="#DEDFDE" BorderStyle="None" BorderWidth="1px"
        CellPadding="4" ForeColor="Black" GridLines="Vertical">
        <AlternatingRowStyle BackColor="White" />
    <Columns>
    <asp:BoundField DataField="Title" HeaderText="Title" />
    <asp:BoundField DataField="Description" HeaderText="Description"/>
   <asp:TemplateField HeaderText="Videos">
   <ItemTemplate>
   <object  width="480" height="385">
<embed src='<%#Eval("url") %>' type="application/x-shockwave-flash" allowscriptaccess="always" allowfullscreen="true" width="480" height="385">
</embed>
</object> 
   </ItemTemplate>
   </asp:TemplateField>
    </Columns>
        <FooterStyle BackColor="#CCCC99" />
        <HeaderStyle BackColor="#6B696B" Font-Bold="True" ForeColor="White" />
        <PagerStyle BackColor="#F7F7DE" ForeColor="Black" HorizontalAlign="Right" />
        <RowStyle BackColor="#F7F7DE" />
        <SelectedRowStyle BackColor="#CE5D5A" Font-Bold="True" ForeColor="White" />
        <SortedAscendingCellStyle BackColor="#FBFBF2" />
        <SortedAscendingHeaderStyle BackColor="#848384" />
        <SortedDescendingCellStyle BackColor="#EAEAD3" />
        <SortedDescendingHeaderStyle BackColor="#575357" />
    </asp:GridView>

Import the namespace
C#:
using System.Text.RegularExpressions;

VB:
Imports System.Text.RegularExpressions

Create the object of DBML:
C#:
BlogDataContext db = new BlogDataContext();

VB:
Private db As New BlogDataContext()

Get the Youtube video id
Write a function to get the youtube video id from youtube video URL .

C# Code:
private string GetYouTubeVideoID(string youTubeVideoUrl)
{
    var regexMatch = Regex.Match(youTubeVideoUrl, "^[^v]+v=(.{11}).*",
                       RegexOptions.IgnoreCase);
    if (regexMatch.Success)
    {
        return "http://www.youtube.com/v/" + regexMatch.Groups[1].Value +"&hl=en&fs=1";
    }
    return youTubeVideoUrl;
}

VB Code:
Private Function GetYouTubeVideoID(ByVal youTubeVideoUrl As String) As String
    Dim regexMatch = Regex.Match(youTubeVideoUrl, "^[^v]+v=(.{11}).*", RegexOptions.IgnoreCase)
    If regexMatch.Success Then
        Return "http://www.youtube.com/v/" + regexMatch.Groups(1).Value + "&hl=en&fs=1"
    End If
    Return youTubeVideoUrl
End Function

Save the record to database
On button click write the below given code to insert the record into database table.

C# Code:
protected void btnsubmit_Click(object sender, EventArgs e)
{
    try
    {
        string ytFormattedVideoUrl = GetYouTubeVideoID(txturl.Text);
        Tb_Video tb = new Tb_Video();
        tb.Title = txttitle.Text;
        tb.Description = txtdescription.Text;
        tb.URL = ytFormattedVideoUrl;
        db.Tb_Videos.InsertOnSubmit(tb);
        db.SubmitChanges();
        Response.Write("<script>alert('Record Inserted Successfully');</script>");
        BindGrid();
        Clear();
    }
    catch (Exception ex)
    { }
}
public void Clear()
{
    txturl.Text = string.Empty;
    txtdescription.Text = string.Empty;
    txttitle.Text = string.Empty;
}

VB Code:
Protected Sub btnsubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnsubmit.Click
    Try
        Dim ytFormattedVideoUrl As String = GetYouTubeVideoID(txturl.Text)
        Dim tb As New Tb_Video()
        tb.Title = txttitle.Text
        tb.Description = txtdescription.Text
        tb.URL = ytFormattedVideoUrl
        db.Tb_Videos.InsertOnSubmit(tb)
        db.SubmitChanges()
        Response.Write("<script>alert('Record Inserted Successfully');</script>")
        BindGrid()
        Clear()
    Catch ex As Exception
    End Try
End Sub
Public Sub Clear()
    txturl.Text = String.Empty
    txtdescription.Text = String.Empty
    txttitle.Text = String.Empty
End Sub

Fetch the record from database and display
Create a method to get the record from database table and display in Gridview data control.

C# Code:
protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        BindGrid();
    }
}

public void BindGrid()
{
    try
    {
        var bind = from v in db.Tb_Videos
                   select v;
        grdvideo.DataSource = bind;
        grdvideo.DataBind();
    }
    catch (Exception ex)
    {
    }
}

VB Code:
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not IsPostBack Then
        BindGrid()
    End If
End Sub

Public Sub BindGrid()
    Try
        Dim bind = From v In db.Tb_Videos
        grdvideo.DataSource = bind
        grdvideo.DataBind()
    Catch ex As Exception
    End Try
End Sub

Build the project and run. To test the application inserts a record into database.

HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

In this tutorial, I will explain how to use concat function in SQL 2016. CONCAT function is also known as T-SQL Function which was introduced in SQL Server 2012. This function allows us to concatenate two or more parameters values together and these parameters values should be separated by comma. Before release of Concat function, we used to use the “+” operator to combine or concatenate two or more string values. The most important feature of Concat function is that it also takes care of data type conversion and beautifully handles NULL on its own. In case of Concat function, we don't need to care about the null values in the parameters which are going to be used.

Interesting features

1. CONCAT takes a variable number of string arguments and concatenates them into a single string.

2. It requires at least two input values; otherwise, an error is raised.

3. All arguments are implicitly converted to string types and then concatenated.

4. Best part of this; Null values are implicitly converted to an empty string. If all the arguments are null, an empty string of type varchar(1) is returned.

5. The implicit conversion to strings follows the existing rules for data type conversions. 

6. If none of the input arguments is of a supported large object (LOB) type, then the return type is truncated to 8000 in length, regardless of the return type. This truncation preserves space and supports efficiency in plan generation.

Examples:

Concatenate Space Characters with input variables

To understand the features of Concat function, we are showing the examples with Concat function and without the concat functions as given below:

In the above example, we are using the two variables to concatenating together. You can see that, we are getting the same outputs. 

Concatenate Space Characters with with NULL values

Now, we are going to make some interesting changes in the above example by setting the null value in the second variable as given below:

You can easily see that full name is showing NULL values in case of without Concat function but by using Concat function, full name is showing because of Null values are implicitly converted to an empty string.

Concatenate number and string together

If you want to concatenate string with numeric value then you should need to convert the numeric value into the string as given below:

If you want to concatenate two numeric values then there is no need to change their data types because of implicit conversion to strings follows the existing rules for data type conversions.

Conclusion

The CONCAT function appends one string to the end of another string and does not require ISNULL for converting NULL into an empty string. All arguments are implicitly converted to string types and then concatenated.

HostForLIFE.eu SQL 2016 Hosting
European best, cheap and reliable ASP.NET hosting with instant activation. HostForLIFE.eu is #1 Recommended Windows and ASP.NET hosting in European Continent. With 99.99% Uptime Guaranteed of Relibility, Stability and Performace. HostForLIFE.eu security team is constantly monitoring the entire network for unusual behaviour. We deliver hosting solution including Shared hosting, Cloud hosting, Reseller hosting, Dedicated Servers, and IT as Service for companies of all size.

When reporting Services reports are shown with ASP.NET Report Viewer control, one of the common requirements for exporting facility is, limiting it to few output formats. By default Export drop-down contains 7 output formats. If we need to limit for 1-2 output formats, one way is, hide the ExportControl and implement it with our own code. Here is the way of implementing it;

Here could be a sample screen for a ASP.NET page with reporting Services report. Note that ExportControl is hidden in the toolbar and drop-down is added to show output formats for exporting.

Write the code below for Page_Load.

protected void Page_Load(object sender, EventArgs e)
{
    ReportViewer1.ShowExportControls = false;
    ReportViewer1.ProcessingMode = ProcessingMode.Remote;
    // this can be set with control itself.
    //ReportViewer1.ServerReport.ReportServerUrl = new Uri(@"http://localhost/reportserver");
    //ReportViewer1.ServerReport.ReportPath = @"/Report Project1/Report2";
    if (!IsPostBack)
    {
        DropDownList1.Items.Add(new ListItem("Word", "Word"));
        DropDownList1.Items.Add(new ListItem("Excel", "Excel"));
        DropDownList1.Items.Add(new ListItem("Acrobat (PDF) file", "PDF"));
    }

}

Now, write the following code for Button-Click.
protected void Button1_Click(object sender, EventArgs e)
{
    string mimeType;
    string encoding;
    string fileNameExtension;
    string[] streams;
    Warning[] warnings;
    byte[] bytes = ReportViewer1.ServerReport.Render(DropDownList1.SelectedValue, null, out mimeType, out encoding, out fileNameExtension, out streams, out warnings);
    HttpContext.Current.Response.Buffer = true;
    HttpContext.Current.Response.Clear();
    HttpContext.Current.Response.ContentType = mimeType;
    HttpContext.Current.Response.AddHeader("content-disposition", "attachment; filename=SalesReport." + fileNameExtension);
    HttpContext.Current.Response.BinaryWrite(bytes);
    HttpContext.Current.Response.Flush();
    HttpContext.Current.Response.End();
}

HostForLIFE.eu ReportViewer Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.