How to add chart
using System;
using System.Windows.Forms;
using
System.Windows.Forms.DataVisualization.Charting;
namespace
WindowsFormsApplication1
{
public partial class Form1 : Form
{
public
Form1()
{
InitializeComponent();
}
private
void Form1_Load(object sender, EventArgs e)
{
// Data arrays.
string[] seriesArray = { "Cats",
"Dogs" };
int[] pointsArray = { 1, 2 };
// Set palette.
this.chart1.Palette =
ChartColorPalette.SeaGreen;
// Set title.
this.chart1.Titles.Add("Pets");
// Add series.
for (int i = 0; i < seriesArray.Length;
i++)
{
//
Add series.
Series
series = this.chart1.Series.Add(seriesArray[i]);
//
Add point.
series.Points.Add(pointsArray[i]);
}
}
}
}
Convert columns data to row data sql
If you have an unknown number of
columns, then you can use dynamic sql:
DECLARE @colsUnpivot AS
NVARCHAR(MAX),
@query
AS NVARCHAR(MAX)
select @colsUnpivot =
stuff((select ','+quotename(C.name)
from sys.columns as C
where C.object_id =
object_id('yourtable') and
C.name != 'Country_Code'
for xml path('')), 1, 1, '')
set @query
= 'select country_code, year, value
from yourtable
unpivot
(
value
for year in ('+ @colsunpivot +')
) u'
exec(@query)
how to check number of table ,sp,vw etc
select Count(*) from
sys.procedures
-- Information about table --
SELECT * FROM sys.sysobjects
WHERE type = 'U'
-- Information about Stored
Procedure --
SELECT * FROM sys.sysobjects
WHERE xtype = 'P'
-- Information about Functions
--
SELECT * FROM sys.sysobjects
WHERE xtype = 'FN'
-- Information about Views --
SELECT * FROM sys.sysobjects
WHERE xtype = 'V'
To create duplicate number for same data(No)
SELECT Product_id, RIGHT('0'+
CAST((ROW_NUMBER() OVER (Partition By Product_id Order By Product_id)) as
varchar(2)), 2) as duplicate_id
FROM
tb_new_product_Name_id_duplicate
Convert data table into string
//to get state table
obj_comm._Table_name =
"state";
obj_dt =
obj_comm.all_tables_cstvpb();
foreach (DataRow _dr in
obj_dt.Rows)
{
combo =
_dr.Field<String>("State_Name");
cmbo_state.Items.Add(combo);
}
Data set E.g
using System;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
namespace WindowsApplication1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender,
EventArgs e)
{
string connetionString = null;
SqlConnection connection ;
SqlCommand command ;
SqlDataAdapter adapter = new
SqlDataAdapter();
DataSet ds = new DataSet();
int i = 0;
string firstSql = null;
string secondSql = null;
connetionString = "Data
Source=ServerName;Initial Catalog=DatabaseName;User
ID=UserName;Password=Password";
firstSql = "Your First SQL
Statement Here";
secondSql = "Your Second SQL
Statement Here";
connection = new
SqlConnection(connetionString);
try
{
connection.Open();
command = new
SqlCommand(firstSql, connection);
adapter.SelectCommand =
command;
adapter.Fill(ds, "First
Table");
adapter.SelectCommand.CommandText = secondSql;
adapter.Fill(ds, "Second
Table");
adapter.Dispose();
command.Dispose();
connection.Close();
//retrieve first table data
for (i = 0; i <=
ds.Tables[0].Rows.Count - 1; i++)
{
MessageBox.Show(ds.Tables[0].Rows[i].ItemArray[0] + " -- " +
ds.Tables[0].Rows[i].ItemArray[1]);
}
//retrieve second table data
for (i = 0; i <=
ds.Tables[1].Rows.Count - 1; i++)
{
MessageBox.Show(ds.Tables[1].Rows[i].ItemArray[0] + " -- " +
ds.Tables[1].Rows[i].ItemArray[1]);
}
}
catch (Exception ex)
{
MessageBox.Show("Can not
open connection ! ");
}
}
}
}
Convert data table data into text file
public static void
WriteDataToFile(DataTable submittedDataTable, string submittedFilePath)
{
int i = 0;
StreamWriter sw = null;
sw = new
StreamWriter(submittedFilePath, false);
for (i = 0; i <
submittedDataTable.Columns.Count - 1; i++)
{
sw.Write(submittedDataTable.Columns[i].ColumnName + ";");
}
sw.Write(submittedDataTable.Columns[i].ColumnName);
sw.WriteLine();
foreach (DataRow row in
submittedDataTable.Rows)
{
object[] array = row.ItemArray;
for (i = 0; i < array.Length
- 1; i++)
{
sw.Write(array[i].ToString() + ";");
}
sw.Write(array[i].ToString());
sw.WriteLine();
}
sw.Close();
}
--------
string FileName =
"TEST_" + System.DateTime.Now.ToString("ddMMyyhhmm") +
".txt";
StreamWriter sw =
File.CreateText(@"PATH...." + FileName);
foreach (DataRow row in
Product.Rows)
{
bool firstCol = true;
foreach (DataColumn col in
Product.Columns)
{
if (!firstCol)
sw.Write(",");
sw.Write(row[col].ToString());
firstCol = false;
}
sw.WriteLine();
}
You need to flush the Stream, by
either wrapping the StreamWriter in a using block, or call sw.Flush() at the
end of your foreach loop
------------
public void GenerateDetailFile()
{
if (!Directory.Exists(AppVars.IntegrationFilesLocation))
{
Directory.CreateDirectory(AppVars.IntegrationFilesLocation);
}
DateTime DateTime = DateTime.Now;
using (StreamWriter sw =
File.CreateText(AppVars.IntegrationFilesLocation +
DateTime.ToString(DateFormat) + " Detail.txt"))
{
DataTable table =
Database.GetDetailTXTFileData();
foreach (DataRow row in table.Rows)
{
sw.WriteLine(row["columnname"].ToString());
}
}
}
Encryption and decryption data
http://www.aspdotnet-suresh.com/2010/12/introduction-here-i-will-explain-how-to_28.html
private string
Encryptdata(string password)
{
string strmsg = string.Empty;
byte[] encode = new
byte[password.Length];
encode =
Encoding.UTF8.GetBytes(password);
strmsg =
Convert.ToBase64String(encode);
return strmsg;
}
/// <summary>
/// Function is used to Decrypt
the password
/// </summary>
/// <param
name="password"></param>
///
<returns></returns>
private string
Decryptdata(string encryptpwd)
{
string decryptpwd =
string.Empty;
UTF8Encoding encodepwd = new
UTF8Encoding();
Decoder Decode =
encodepwd.GetDecoder();
byte[] todecode_byte =
Convert.FromBase64String(encryptpwd);
int charCount =
Decode.GetCharCount(todecode_byte, 0, todecode_byte.Length);
char[] decoded_char = new
char[charCount];
Decode.GetChars(todecode_byte,
0, todecode_byte.Length, decoded_char, 0);
decryptpwd = new
String(decoded_char);
return decryptpwd;
}
Date in sql
select
convert(varchar(10),GETDATE(),104)
output: 20.12.2013
-----------
SELECT
REPLACE(CONVERT(VARCHAR(8), SYSDATETIME(), 3), '/', '') AS [DDMMYY]
output: 060114
convert data grid view into data table
DataTable dt = new DataTable();
for (int i = 0; i <
GridView1.Columns.Count; i++)
{
dt.Columns.Add("column"+i.ToString());
}
foreach (GridViewRow row in
GridView1.Rows)
{
DataRow dr = dt.NewRow();
for(int j =
0;j<GridView1.Columns.Count;j++)
{
dr["column" +
j.ToString()] = row.Cells[j].Text;
}
dt.Rows.Add(dr);
}
Grid view auto re size column width
// Configure the details
DataGridView so that its columns automatically
// adjust their widths when the
data changes.
dataGridView_sales.AutoSizeColumnsMode =
DataGridViewAutoSizeColumnsMode.AllCells;
grid view data into textbox
private void
dataGridView1_MouseClick(object sender, MouseEventArgs e)
{
DataGridViewRow dr =
dataGridView1.SelectedRows[0];
textBox1.Text =
dr.Cells[0].Value.ToString();
// or simply use column name instead of
index
//dr.Cells["id"].Value.ToString();
textBox2.Text =
dr.Cells[1].Value.ToString();
textBox3.Text =
dr.Cells[2].Value.ToString();
textBox4.Text =
dr.Cells[3].Value.ToString();
}
And add the following line in
your load event
dataGridView1.SelectionMode =
DataGridViewSelectionMode.FullRowSelect;
===================================================
private void
dataGridView_SelectionChanged(object sender, EventArgs e)
{
DataGridViewCell cell = null;
foreach (DataGridViewCell selectedCell in
dataGridView.SelectedCells)
{
cell = selectedCell;
break;
}
if (cell != null)
{
DataGridViewRow row = cell.OwningRow;
idTextBox.Text =
row.Cells["ID"].Value.ToString();
nameTextBox.Text =
row.Cells["Name"].Value.ToString();
// etc.
}
}
Prevent twice enter button
<asp:Button
runat="server" ID="btnSave"
Text="Save"
OnClick="btnSave_Click"
OnClientClick="if
(!Page_ClientValidate()){ return false; } this.disabled = true; this.value =
'Saving...';"
UseSubmitBehavior="false" />
-------------------------------
btnSave.Attributes.Add("onclick",
"this.disabled='true';") &
btnSave.Attributes.Add("onload", "this.disabled='false';")
Message box validating yes or no
if (MessageBox.Show("Are
you sure?", "Confirm", MessageBoxButtons.YesNo,
MessageBoxIcon.Question) == DialogResult.Yes)
{
// user clicked yes
}
else
{
// user clicked no
}
Single string into two string sql
CREATE FUNCTION [dbo].[GetStringPart]
(@fullString varchar(200), @pos
tinyint)
RETURNS VARCHAR(200) --
return_data_type.
AS
BEGIN
IF @pos IS NULL OR @pos <= 0
SET @pos = 1
declare @secondPart varchar(200),@firstPart
varchar(200),@output varchar(200)
declare @firstSpace int, @secondSpace int
set @firstSpace = CHARINDEX(' ',
@fullString)
IF @firstSpace <= 0
RETURN ''
ELSE IF @pos = 1
BEGIN
SET @output =
LTRIM(RTRIM(SUBSTRING(@fullString, 1, @firstSpace)))
END
ELSE
BEGIN
SET @secondSpace = CHARINDEX(' ',
@fullString, CHARINDEX(' ', @fullString)+1) - CHARINDEX(' ', @fullString)+1
IF @secondSpace <= 0
SET @secondSpace =
LEN(@fullString) - @firstSpace + 1
SET @output =
LTRIM(RTRIM(SUBSTRING(@fullString, @firstSpace, @secondSpace)))
END
RETURN @Output
END
declare @origTable table(name
varchar(100))
insert into @origTable (name)
values ('99 crystal springs road')
declare @newTable table(col1
varchar(100), col2 varchar(100))
INSERT INTO @newTable (col1,
col2)
SELECT dbo.GetStringPart(name,
1), dbo.GetStringPart(name, 2) FROM @origTable
select * from @newTable
------------------------------------------------------------
CREATE FUNCTION
dbo.GetStringPart (@sep char(1), @s varchar(512),@pos int)
RETURNS VARCHAR(200)
AS
BEGIN
DECLARE @output VARCHAR(200)
;WITH Pieces(pn, start, stop) AS (
SELECT 1, 1, CHARINDEX(@sep, @s)
UNION ALL
SELECT pn + 1, stop + 1, CHARINDEX(@sep,
@s, stop + 1)
FROM Pieces
WHERE stop > 0
)
SELECT
@output=SUBSTRING(@s, start, CASE WHEN
stop > 0 THEN stop-start ELSE 512 END)
FROM Pieces
WHERE pn=@pos
RETURN @Output
END
GO
DECLARE @origalTable TABLE(name
VARCHAR(100))
INSERT INTO @origalTable
VALUES('99 crystal springs
road')
DECLARE @newTable TABLE(col1
VARCHAR(100), col2 VARCHAR(100))
INSERT INTO @newTable (col1,
col2)
SELECT dbo.GetStringPart('
',name, 1), dbo.GetStringPart(' ',name, 2) FROM @origalTable
SELECT * FROM @newTable
To add grid view columns values
int sum = 0;
for (int i = 0; i <
dgTestSteps.Rows.Count; ++i)
{
sum +=
Convert.ToInt32(dgTestSteps.Rows[i].Cells[0].Value);
}
lblTotalTime.Text = sum.ToString();
prevent textbox accepts only number
if (e.KeyChar >= '0'
&& e.KeyChar <= '9' || e.KeyChar == '') //The character represents a backspace
{
e.Handled = false; //Do not reject the
input
}
else
{
e.Handled = true; //Reject the input
}
===============================================
if (!(char.IsDigit(e.KeyChar) ||
e.KeyChar == (char)Keys.Back))
{
e.Handled = true;
}
Convert textbox into grid view row
private void btnAdd_Click(object
sender, EventArgs e)
{
string firstColum = txtUomtyp.Text;
string secondColum = txtUomDesc.Text;
string[] row = { firstColum,
secondColum };
dgvUom.Rows.Add(row);
}
To clear grid view
dataGridView1.Rows.Clear()
OR
Collapse | Copy Code
dt.Rows.Clear() // If dgv is
bound to datatable
dataGridView1.DataBind();
OR
Use this code to check if you
are bound to a data source :
Collapse | Copy Code
//Code Block
if
(this.dataGridView1.DataSource != null)
{
this.dataGridView1.DataSource = null;
}
else
{
this.dataGridView1.Rows.Clear();
}
To find control by id
asp.net
// to get label text
void label_text()
{
obj_dt=obj_qus.label_names();
string label_te = null;
int i = 0;
foreach (DataRow dr in obj_dt.Rows)
{
i++;
label_te =
dr.Field<string>("value");
var myControl =
(Label)FindControlRecursive(Page, "Label" + i);
//Label l =
this.FindControl("Label"+i.ToString()) as Label;
if (myControl != null)
{
myControl.Text =
label_te.ToString();
}
}
}
public static Control
FindControlRecursive(Control root, string id)
{
if (root.ID == id)
return root;
return
root.Controls.Cast<Control>()
.Select(c =>
FindControlRecursive(c, id))
.FirstOrDefault(c => c != null);
}
------------------------------------------
winforms
---------
public IEnumerable<Control>
GetAll(Control control,Type type)
{
var controls =
control.Controls.Cast<Control>();
return controls.SelectMany(ctrl =>
GetAll(ctrl,type))
.Concat(controls)
.Where(c =>
c.GetType() == type);
}
private void Form1_Load(object
sender, EventArgs e)
{
var c = GetAll(this,typeof(TextBox));
MessageBox.Show("Total Controls:
" + c.Count());
}
-----
winforms
----------
public static class
ComponentExtensions
{
public static IEnumerable<Component>
GetAllComponents(this Component component)
{
IEnumerable<Component>
components;
if (component is ToolStrip) components
= ((ToolStrip)component).Items.Cast<Component>();
else if (component is Control)
components = ((Control)component).Controls.Cast<Component>();
else components =
Enumerable.Empty<Component>();
// figure out what you want to do
here
return
components.Concat(components.SelectMany(x => x.GetAllComponents()));
}
}
foreach (Component component in
this.GetAllComponents())
{
//
Do something with component...
}
Grid view value into string
string ss =
dataGridView_total.SelectedRows[0].Cells[1].Value.ToString();
to replace columns in data table
// replace column name _ with
" "
foreach (DataColumn column in
obj_dt.Columns)
column.ColumnName =
column.ColumnName.Replace("_", " ");
How to change datatable column name to upper case
· foreach (DataColumn column in obj_dt.Columns)
column.ColumnName = CultureInfo.CurrentCulture.TextInfo.ToTitleCase(column.ColumnName);
You need to add the using System.Globalization;
· foreach (DataColumn column in obj_dt.Columns)
column.ColumnName = column.ColumnName[0].ToUpper()+column.ColumnName.Substring(1);
to truncate all table
EXEC sp_MSforeachtable 'TRUNCATE
TABLE ?'
The string.IsNullOrEmpty method tests strings.
// Test with IsNullOrEmpty.
string s = null;
if (string.IsNullOrEmpty(s) ==
true)
{
// True.
Console.WriteLine("Null or empty");
}
else
{
Console.WriteLine("Not null and not empty");
}
Difference between Empty and Null
Empty - It has empty value.
NULL - It has no value.
NULL - It has no value.
string
s = null;
No
object exists, but the variable s that references a string object does
exist, but it references nothing.
string
s = String.Empty;
string
s = "";
Are equivalent. Both the object and the variable exist.
null = no memory allocated to the string
variable. no valid object of string.
String.Empty = object of String class(all
properties of String class will be available to it, but its value is empty)
"" = a blank string. memory allocated
to the variable.
Make first letter of a string upper case
stringName.First().ToString().ToUpper() + String.Join("",stringName.Skip(1));
Convert all first letter to upper case, rest lower for each word
string s = "THIS IS MY TEXT RIGHT NOW";
s=System.Threading.Thread.CurrentThread.CurrentCulture.TextInfo.ToTitleCase(s.toLower());
To get total number of columns in a table in sql
SELECT Count(*) FROM INFORMATION_SCHEMA.Columns where TABLE_NAME = 'YourTableName'
How to set a default value and check value for an existing column
ALTER TABLE tblName ADD DEFAULT 'default value' FOR columnName
ALTER TABLE tblName ADD constraint constraintName DEFAULT 'default value' FOR columnName
ALTER TABLE tblName ADD constraint constraintName CHECK (columnName like|=|etc )
We cannot alter the constraint,
only thing we can do is drop and recreate it
Altering column size in SQL Server
ALTER TABLE [column name]
ALTER COLUMN [Salary] VARCHAR(size)
sp_RENAME 'TableName.[OldColumnName]' ,
'[NewColumnName]', 'COLUMN'sp_RENAME '[OldTableName]' ,
'[NewTableName]'How to: Disable Enable constraints on a table
-- Disable the constraint and try again.
ALTER TABLE dbo.tbl_name NOCHECK CONSTRAINT constraint_name;
-- Re-enable the constraint and try another insert; this will fail.
ALTER TABLE dbo.tbl_name CHECK CONSTRAINT constraint_name;
--disable all constraints for the table ALTER TABLE Sales dbo.tbl_name NOCHECK CONSTRAINT ALL --do something --enable all constraints for the Sales table ALTER TABLE dbo.tbl_name CHECK CONSTRAINT ALL
DEFAULT, PRIMARY KEY, and UNIQUE
constraints cannot be disabled.
Disabling and re-enabling a trigger
-- Disable the trigger.
ALTER TABLE dbo.tbl_name DISABLE TRIGGER trig1 ;
-- Re-enable the trigger.
ALTER TABLE dbo.tbl_name ENABLE TRIGGER trig1 ;
Sending Email from Asp.Net:
MailMessage objMail = new MailMessage("Sending From", "Sending To","Email Subject", "Email Body");
NetworkCredential objNC = new NetworkCredential("Sender Email","Sender Password");
SmtpClient objsmtp = new SmtpClient("smtp.live.com", 587); // for hotmail
objsmtp.EnableSsl = true;
objsmtp.Credentials = objNC;
objsmtp.Send(objMail);
or
smtpClient.UseDefaultCredentials = false;
smtpClient.Host = "smtp.gmail.com";
smtpClient.Port = 587;
smtpClient.Credentials = new NetworkCredential(username,passwordd);
smtpClient.EnableSsl = true;
smtpClient.Send(mailMessage);
Query to get the names of all tables in SQL Server 2008 Database
USE your_database
SELECT name FROM sys.tables
No comments:
Post a Comment