Get the list of Stored Procs related to a table

SELECT DISTINCT so.name FROM syscomments sc INNER JOIN sysobjects so ON sc.id=so.id WHERE sc.TEXT LIKE '%tablename%'

How to use SqlDataSource SelectCommand in the backend

This Tutorial is not for those who need to plugin the sqldatasource in the front end, but rather how to use the SqlDataSource in the backend codes.

There are tons of tutorial of how to write SqlDataSource for Gridview, DataList, Repeater and other objects in the front end (aspx file), but not many tutorials out there on how to simplify things for using SqlDataSource in the backend (aspx.cs file).

Example:

you have a gridview called “gvStudents”, and you have a sql query “SELECT * FROM Students”. you want to bind the query to the gridview but you want to do it in the backend. How do you do it?

here is how you do it.

SqlDataSource SDSStudents = new SqlDataSource();
SDSStudents.SelectCommand = “SELECT * FROM Students”;
SDSStudents.ConnectionString = ConfigurationManager.ConnectionStrings["YourConnectStringName"].ConnectionString;

gvStudents.DataSource = SDSStudents;
gvStudents.DataBind();

5 Simple Steps
1) Create a new SqlDataSource object
2) Assign the SQL Query to the Select Command
3) Assign the ConnectionString to the SqlDataSource ConnectionString
4) Assign the SqlDataSource to the gridview DataSource
5) DataBind the gridview

I know your next question is “What about Stored Procedure? Can I do the same thing with a stored procedure?”

The answer is Yes, you can do the same thing with a stored procedure. here is how

SqlDataSource SDSStudents = new SqlDataSource();
SDSStudents.SelectCommand = “Stored Procedure Name”;
SDSStudents.SelectCommandType = SqlDataSourceCommandType.StoredProcedure;
SDSStudents.ConnectionString = ConfigurationManager.ConnectionStrings["YourConnectStringName"].ConnectionString;

gvStudents.DataSource = SDSStudents;
gvStudents.DataBind();

Instead of 5 Simple Steps, it is now 6 simple steps
6 Simple Steps
1) Create a new SqlDataSource object
2) Assign the SQL stored procedure name to the Select Command
3) Set the SqlCommandType to be a StoredProcedure
4) Assign the ConnectionString to the SqlDataSource ConnectionString
5) Assign the SqlDataSource to the gridview DataSource
6) DataBind the gridview


I bet your next answer is “What about SelectCommand Parameters? Can I Add Parameters to the SqlDataSource?”

*Updated*
Instead of writing another entry, I decided to continue with this entry and answer the question about the Parameters inside the SqlDataSource.

Yes you can add parameter in the SelectCommand, either the select command is sql query or stored procedure.
This is how you do it.

SqlDataSource SDSStudents = new SqlDataSource();
SDSStudents.SelectCommand = “SELECT * FROM Students WHERE ID=@ID”;
SDSStudents.SelectParameters.Add(“ID”, TypeCode.Int32, “1″);
SDSStudents.ConnectionString = ConfigurationManager.ConnectionStrings["YourConnectStringName"].ConnectionString;
gvStudents.DataSource = SDSStudents;
gvStudents.DataBind();


If using with Stored Procedure use this,

SqlDataSource SDSStudents = new SqlDataSource();
SDSStudents.SelectCommand = “Stored Procedure Name”;
SDSStudents.SelectCommandType = SqlDataSourceCommandType.StoredProcedure;
SDSStudents.ConnectionString = ConfigurationManager.ConnectionStrings["YourConnectStringName"].ConnectionString;

SDSStudents.SelectParameters.Clear();
SDSStudents.SelectParameters.Add("Param1", strParamVal1);
SDSStudents.SelectParameters.Add("Param2", strParamVal2);

if (SDSStudents.SelectParameters["Param1"].DefaultValue == null || SDSStudents.SelectParameters["Param1"].DefaultValue == "")
SDSStudents.SelectParameters["Param1"].DefaultValue = " ";

if (SDSStudents.SelectParameters["Param2"].DefaultValue == null || SDSStudents.SelectParameters["Param2"].DefaultValue == "")
SDSStudents.SelectParameters["Param2"].DefaultValue = " ";


gvStudents.DataSource = SDSStudents;
gvStudents.DataBind();

Instead of 5 Simple Steps for the sqlquery, it is now 6 simple steps
6 Simple Steps
1) Create a new SqlDataSource object
2) Assign the SQL Query to the Select Command
3) using SelectParamters.Add(“ParameterName”, DBType or TypeCode, “ParameterValue”) to add new parameter to the selectCommand
4) Assign the ConnectionString to the SqlDataSource ConnectionString
5) Assign the SqlDataSource to the gridview DataSource
6) DataBind the gridview

Crystal Report Binding With Parameters From C#

This script can be used when you want to bind a Crystal Report with Stored Procedure and want to pass some parameters to the report. These parameters can be in SubReport also. The parameters get assigned for the respective subreports and main report if the Parameter name differs.

The major use of this code is it would not ask for the login details while showing the report. The Login details are set based on the App.Config connection which is Global across the Application.


TableLogOnInfos myTableLogOnInfos = new TableLogOnInfos();
TableLogOnInfo myTableLogOnInfo = new TableLogOnInfo();
ConnectionInfo myConnectionInfo = new ConnectionInfo();

//Set Database Connection Details
string[] arrConn = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"].Split(';');

for (int i = 0; i < arrConn.Length; i++)
{
string[] arrServer = arrConn[i].Split('=');
if (arrServer[0].ToLower().Trim() == "server" || arrServer[0].ToLower().Trim() == "data source")
myConnectionInfo.ServerName = arrServer[1];
if (arrServer[0].ToLower().Trim() == "database" || arrServer[0].ToLower().Trim() == "initial catalog")
myConnectionInfo.DatabaseName = arrServer[1];
if (arrServer[0].ToLower().Trim() == "userId" || arrServer[0].ToLower().Trim() == "user id")
myConnectionInfo.UserID = arrServer[1];
if (arrServer[0].ToLower().Trim() == "Password" || arrServer[0].ToLower().Trim() == "pwd")
myConnectionInfo.Password = arrServer[1];
}

//This Code is used to Get the Reportname based on the namespace. This code can be used globally across the application. If you can pass the Report Name directly then this piece of code wont be necessary

//Get the Report Source Name
String sFormName = UIGlobal.ReportName;
Assembly objAssembly = AppDomain.CurrentDomain.Load(UIGlobal.RptNamespaceName); ;
object objForm = null;
foreach (Type t in objAssembly.GetTypes())
{
if (t.Name.ToUpper() == sFormName.ToUpper())
{
objForm = Activator.CreateInstance(t);
break;
}
}
if (objForm != null)
{
rptDoc = (ReportDocument)objForm;

foreach (Table t in rptDoc.Database.Tables)
{
TableLogOnInfo tl = t.LogOnInfo;
tl.ConnectionInfo = myConnectionInfo;
t.ApplyLogOnInfo(tl);
}

rptDoc.Refresh();

//Set Log On Info
myTableLogOnInfo.ConnectionInfo = myConnectionInfo;
myTableLogOnInfos.Add(myTableLogOnInfo);
rptStorage.LogOnInfo = myTableLogOnInfos;
rptStorage.Refresh();

if (strParamName != null && strParamName.Length > 0 && strParamValue != null && strParamValue.Length > 0)
{
int cnt = rptDoc.DataDefinition.ParameterFields.Count;
for (int i = 0; i < cnt; i++)
{
ParameterValues myvals = new ParameterValues();
ParameterDiscreteValue myDiscrete = new ParameterDiscreteValue();

ArrayList arParams = new ArrayList();
arParams.AddRange(strParamName);

if (arParams.Contains(rptDoc.DataDefinition.ParameterFields[i].ParameterFieldName.ToUpper()))
{
for (int iCnt = 0; iCnt < strParamName.Length; iCnt++)
{
if (strParamName[iCnt] == rptDoc.DataDefinition.ParameterFields[i].ParameterFieldName.ToUpper())
{
myDiscrete.Value = strParamValue[iCnt];
myvals.Add(myDiscrete);
rptDoc.DataDefinition.ParameterFields[i].ApplyCurrentValues(myvals);
}
}
}
}
}
rptStorage.ReportSource = rptDoc;
}

Disable Buttons in a Button Column in the Windows Forms DataGridView

public void Form1_Load(object sender, EventArgs e)
{
DataGridViewCheckBoxColumn column0 =
new DataGridViewCheckBoxColumn();
DataGridViewDisableButtonColumn column1 =
new DataGridViewDisableButtonColumn();
column0.Name = "CheckBoxes";
column1.Name = "Buttons";
dataGridView1.Columns.Add(column0);
dataGridView1.Columns.Add(column1);
dataGridView1.RowCount = 8;
dataGridView1.AutoSize = true;
dataGridView1.AllowUserToAddRows = false;
dataGridView1.ColumnHeadersDefaultCellStyle.Alignment =
DataGridViewContentAlignment.MiddleCenter;

// Set the text for each button.
for (int i = 0; i < dataGridView1.RowCount; i++)
{
dataGridView1.Rows[i].Cells["Buttons"].Value =
"Button " + i.ToString();
}

dataGridView1.CellValueChanged +=
new DataGridViewCellEventHandler(dataGridView1_CellValueChanged);
dataGridView1.CurrentCellDirtyStateChanged +=
new EventHandler(dataGridView1_CurrentCellDirtyStateChanged);
dataGridView1.CellClick +=
new DataGridViewCellEventHandler(dataGridView1_CellClick);

this.Controls.Add(dataGridView1);
}

// This event handler manually raises the CellValueChanged event
// by calling the CommitEdit method.
void dataGridView1_CurrentCellDirtyStateChanged(object sender,
EventArgs e)
{
if (dataGridView1.IsCurrentCellDirty)
{
dataGridView1.CommitEdit(DataGridViewDataErrorContexts.Commit);
}
}

// If a check box cell is clicked, this event handler disables
// or enables the button in the same row as the clicked cell.
public void dataGridView1_CellValueChanged(object sender,
DataGridViewCellEventArgs e)
{
if (dataGridView1.Columns[e.ColumnIndex].Name == "CheckBoxes")
{
DataGridViewDisableButtonCell buttonCell =
(DataGridViewDisableButtonCell)dataGridView1.
Rows[e.RowIndex].Cells["Buttons"];

DataGridViewCheckBoxCell checkCell =
(DataGridViewCheckBoxCell)dataGridView1.
Rows[e.RowIndex].Cells["CheckBoxes"];
buttonCell.Enabled = !(Boolean)checkCell.Value;

dataGridView1.Invalidate();
}
}

// If the user clicks on an enabled button cell, this event handler
// reports that the button is enabled.
void dataGridView1_CellClick(object sender,
DataGridViewCellEventArgs e)
{
if (dataGridView1.Columns[e.ColumnIndex].Name == "Buttons")
{
DataGridViewDisableButtonCell buttonCell =
(DataGridViewDisableButtonCell)dataGridView1.
Rows[e.RowIndex].Cells["Buttons"];

if (buttonCell.Enabled)
{
MessageBox.Show(dataGridView1.Rows[e.RowIndex].
Cells[e.ColumnIndex].Value.ToString() +
" is enabled");
}
}
}
}

public class DataGridViewDisableButtonColumn : DataGridViewButtonColumn
{
public DataGridViewDisableButtonColumn()
{
this.CellTemplate = new DataGridViewDisableButtonCell();
}
}

public class DataGridViewDisableButtonCell : DataGridViewButtonCell
{
private bool enabledValue;
public bool Enabled
{
get
{
return enabledValue;
}
set
{
enabledValue = value;
}
}

// Override the Clone method so that the Enabled property is copied.
public override object Clone()
{
DataGridViewDisableButtonCell cell =
(DataGridViewDisableButtonCell)base.Clone();
cell.Enabled = this.Enabled;
return cell;
}

// By default, enable the button cell.
public DataGridViewDisableButtonCell()
{
this.enabledValue = true;
}

protected override void Paint(Graphics graphics,
Rectangle clipBounds, Rectangle cellBounds, int rowIndex,
DataGridViewElementStates elementState, object value,
object formattedValue, string errorText,
DataGridViewCellStyle cellStyle,
DataGridViewAdvancedBorderStyle advancedBorderStyle,
DataGridViewPaintParts paintParts)
{
// The button cell is disabled, so paint the border,
// background, and disabled button for the cell.
if (!this.enabledValue)
{
// Draw the cell background, if specified.
if ((paintParts & DataGridViewPaintParts.Background) ==
DataGridViewPaintParts.Background)
{
SolidBrush cellBackground =
new SolidBrush(cellStyle.BackColor);
graphics.FillRectangle(cellBackground, cellBounds);
cellBackground.Dispose();
}

// Draw the cell borders, if specified.
if ((paintParts & DataGridViewPaintParts.Border) ==
DataGridViewPaintParts.Border)
{
PaintBorder(graphics, clipBounds, cellBounds, cellStyle,
advancedBorderStyle);
}

// Calculate the area in which to draw the button.
Rectangle buttonArea = cellBounds;
Rectangle buttonAdjustment =
this.BorderWidths(advancedBorderStyle);
buttonArea.X += buttonAdjustment.X;
buttonArea.Y += buttonAdjustment.Y;
buttonArea.Height -= buttonAdjustment.Height;
buttonArea.Width -= buttonAdjustment.Width;

// Draw the disabled button.
ButtonRenderer.DrawButton(graphics, buttonArea,
PushButtonState.Disabled);

// Draw the disabled button text.
if (this.FormattedValue is String)
{
TextRenderer.DrawText(graphics,
(string)this.FormattedValue,
this.DataGridView.Font,
buttonArea, SystemColors.GrayText);
}
}
else
{
// The button cell is enabled, so let the base class
// handle the painting.
base.Paint(graphics, clipBounds, cellBounds, rowIndex,
elementState, value, formattedValue, errorText,
cellStyle, advancedBorderStyle, paintParts);
}
}
}

SQL Script for creating a New Database with Security Login and Password

////Create a new database, if does not exist
if db_id('DBNAME') is null
begin
create database [DBNAME]
end

=====================================================================================
////Once the Database is Created we can create the User login for this Database

////If user does not exist
if DATABASE_PRINCIPAL_ID('[DBNAME]') is null
begin
use [DBNAME]
if DATABASE_PRINCIPAL_ID('USERNAME') is null
CREATE LOGIN [USERNAME]
WITH PASSWORD='PASSWORD',
DEFAULT_DATABASE=[DBNAME],
DEFAULT_LANGUAGE=[us_english],
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF
end

if DATABASE_PRINCIPAL_ID('USERNAME') is null
begin
use [DBNAME]
CREATE USER USERNAME
Grant Insert,Update,Delete,Execute,Select,Create Procedure to USERNAME
end


DBNAME = Database Name
USERNAME = User Login Name
PASSWORD = Password for the DB Login

Creating Word Document from Byte Array and Viceversa

//Reading a Word Document and Converting to Byte Array (Can Be Saved to DB)
byte[] myByteArray = System.IO.File.ReadAllBytes(Request.PhysicalApplicationPath + "\\document\\Test.doc");

//Converting the Byte Array to a WordDocument Again (Retrieved from DB)
FileStream fs = new FileStream(Request.PhysicalApplicationPath + "\\document\\Test2.doc", FileMode.Create, FileAccess.ReadWrite);
BinaryWriter bw = new BinaryWriter(fs);
bw.Write(myByteArray);
bw.Close();

Convert DataTable Column into String Array without Looping

Here ColumnName is the Specific Column in the Datatable

String[] rowValuesForColumn = Array.ConvertAll(dataTable.Select(),
delegate(DataRow row) { return (String) row[columnName]; });