Tuesday, May 11, 2010

Query to Return All Columns of Table

This Query will returns the all columns of a specified TABLE.


SELECT *

FROM information_schema.COLUMNS CH
WHERE table_name='tblChMyLogins'

How to get a list of SQL Server databases

There are multiple ways of getting a list of the SQL Server databases, the easiest one is to execute the sp_databases stored procedure, like in the example below:


System.Data.SqlClient.SqlConnection SqlCon = new System.Data.SqlClient.SqlConnection("server=venkateswarlu;uid=sa;pwd=1234");

SqlCon.Open();


System.Data.SqlClient.SqlCommand SqlCom = new System.Data.SqlClient.SqlCommand();

SqlCom.Connection = SqlCon;

SqlCom.CommandType = CommandType.StoredProcedure;

SqlCom.CommandText = "sp_databases";
System.Data.SqlClient.SqlDataReader SqlDR;SqlDR = SqlCom.ExecuteReader();


while(SqlDR.Read())

{

MessageBox.Show(SqlDR.GetString(0));

}

Monday, May 10, 2010

ASP.NET - GridView RowCommand get row index

if you did assign a value into the CommandArgument e.g. below, you need to use the #1 methond else #2
<itemtemplate> <asp:imagebutton id="imgUpdate" runat="server" commandname="Update1" commandargument="'<%#">' ImageUrl="~/icons/Update001 (2).gif" /></itemtemplate>
#1
VB
Dim selectedRow As GridViewRow = DirectCast(DirectCast(e.CommandSource, LinkButton).NamingContainer, GridViewRow)Dim intRowIndex As Integer = Convert.ToInt32(selectedRow.RowIndex)GridView.Rows(intRowIndex).BackColor = System.Drawing.Color.Blue
C#
GridViewRow selectedRow = (GridViewRow)((ImageButton)e.CommandSource).NamingContainer;int intRowIndex = Convert.ToInt32(selectedRow.RowIndex);GridView.Rows[intRowIndex].BackColor = System.Drawing.Color.Blue;
or #2
VB
Dim selectedRow As GridViewRow = GridView.Rows(Convert.ToInt32(e.CommandArgument))selectedRow.BackColor = System.Drawing.Color.Blue
C#
GridViewRow selectedRow = GridView.Rows[Convert.ToInt32(e.CommandArgument)];selectedRow.BackColor = System.Drawing.Color.Blue;

Thursday, May 6, 2010

SQL Server Tutorial Resources



SQL Server Tutorial Resources

The tutorials are provided for your education and enjoyment and are not warranted in any way.

The following tutorials are excerpts from Accelebrate's Introduction to SQL Server 2008 training. These tutorials use Microsoft's AdventureWorks and AdventureWorks BI databases, available from CodePlex.





The following tutorials are excerpts from Accelebrate's Introduction to SQL Server 2005 training. These tutorials use Microsoft's AdventureWorks and AdventureWorks BI databases, available from CodePlex.



Monday, May 3, 2010

list of SQL Server Names

How to get the list of SQL Server Names using windows application available in Network

Using SqlDataSourceEnumerator class in the System.Data.Sql namespace, it is easy for developers to enumerate and get a list of existing visible Microsoft SQL Server database instances within a network.

Using the Instance property of the SqlDataSourceEnumerator class you can create an instance of the SqlDataSourceEnumerator object that can be used to retrieve information about available SQL Server instances.
Microsoft SQL Server 2000, SQL Server 2005 and SQL Server 2008 (Katmai) database instances within a network can be listed using this new class "SqlDataSourceEnumerator".
GetDataSources() method of the SqlDataSourceEnumerator class returns information in a DataTable object that can be used to display data about the retrieved SQL database instances on a DataGridView or in a ComboBox object.

Import the Imports System.Data.Sql if it is not imported within the project by default.

Imports System.Data.Sql

Calling the SqlDataSourceEnumerator.Instance will get the public shared instance of SqlDataSourceEnumerator class whose GetDataSources() method will be used to return information about the SQL Servers in a DataTable object.





As you see, DataTable that is returned by the GetDataSources() method of the SqlDataSourceEnumerator class includes the following columns:

ServerName : ServerName is the name of the server where the SQL Server database Instance is running on.

InstanceName : InstanceName is the name of the SQL database instance. If the instance is installed as the Default instance then InstanceName is blank.

IsClustered : IsClustered displays whether the SQL Server Instance is a part of a Cluster SQL Server installation.

Version : Version is the Microsoft SQL Server version number.


Works like this:

using System.Data;
using System.Data.Sql;

SqlDataSourceEnumerator servers = SqlDataSourceEnumerator.Instance;
DataTable serversTable = servers.GetDataSource();

foreach(DataRow row in serversTable.Rows)
{
string serverName = string.format(\"{0}\\\\{1}\", row[0], row[1];
// Add this to your list
}



How to bind Dropdownlist in asp.net

In this article I am going to show that how we can fetch data in a DropDownList from Sql server. Here I used a DropDownList in which on the load of form the Name value will come from a stored procedure.

In fetching data from database in a DropDownList we have to set a property of DropDownList like as.

DroplistData.DataTextField = "Name";

Sample Code (C#)

string connString =ConfigurationManager.

ConnectionStrings["connectionstring"].ToString()

SqlConnection con = new SqlConnection(connString);

con.Open();

SqlCommand cmd = new SqlCommand();

cmd.Connection = con;

cmd.CommandText = "spChGetNames";

cmd.CommandType = CommandType.StoredProcedure;

DataSet ds = new DataSet();

SqlDataAdapter da = new SqlDataAdapter();

da.SelectCommand = cmd;

da.Fill(ds);

DropDownList1.DataSource = ds;

DropDownList1.DataTextField = "Name";

DropDownList1.DataValueField = "ID";

DropDownList1.DataBind();

There was an error in this gadget

About Me

My photo
I am a Postgraduate in Mathematics from Sri Venkateswara University. I made this blog to share and gain knowledge and basically to express myself. The purpose of making this site was also to make my work available at a single point on Internet. I started programming with dBase and FoxPro, then C and C++. I am a developer working with Software Company.

This Day in History