add or modify table column using sql server database script

it is always recommended that you use script to run for modifucation rather set by using interface for this case.

Here is the trick


To add new column:

ALTER TABLE Customer ADD IsFeatured bit not NULL default 0

To modify existing column such as data type:

ALTER TABLE news ALTER COLUMN title VARCHAR(150) NOT NULL

How to run VB code and C# code in appCode folder?

In the web.config, under compilation

<compilation debug="true" strict="false" explicit="true">

add,

<codeSubDirectories>
<add directoryName="VBCode" />
<add directoryName="CSCode" />
</codeSubDirectories>

VBCode and CSCode are the folders in the AppCode.

ASP.NET move to next record or move to previous record

Basically, ASP.NET does not have move next or move previous function, therefore you need to manually use a for loop to determine the position of the target index and then use position + 1 or position -1 to get the next or previous record.

If you are developing a windows form then you are lucky you can use BindingContext[dataTable].Position + 1 or BindingContext[dataTable].Position - 1.

Well, we are talking about ASP.NET here, if we are populating a few hundreds items and use for loop to get the position and I think it is fine. Or u might be using datatable something like

DataTable dt = new DataTable();
dt.Rows.IndexOf(datarow);

to get the index and from there u manipulate it.

However what if we have 1 million of records in the table? That would be crazy!

The more efficient way is to use over keyword which only available in SQL Server 2005 or later. The idea is to get the previous record and the next record given the record identity. I just have to get 2 records and I just want the first record as previous and second record as next. So here is the trick.

subcategoryid, name

31	Game Downloads
32	Computers & Accessories
33	Software
34	PC Games
35	Computer Components
36	Office Products & Supplies

select top(1) row_number() over (order by subcategoryid desc) row, subcategoryid, name from subcategory where subcategoryid<33
union
select top(1) row_number() over (order by subcategoryid) row, subcategoryid, name from subcategory where subcategoryid>33

results:

1 32 Computers & Accessories
1 34 PC Games

p/s please note that this solution only applicable for int column having auto numbering enabled only. the subcategoryid is the primary key, auto number and numaric

SQL upcoming date

if you want to get the up coming listing such as event you can do a SQL query like this:

SELECT EventID, EventName, StartDate, EndDate, EventTime, EventLink
FROM Events
WHERE (StartDate BETWEEN GETDATE() AND GETDATE() + 45) AND (GETDATE() BETWEEN StartDate AND EndDate + 1)
ORDER BY StartDate

get data from excel using linq .net

You might want to read excel file and import the data from the excel into your .net application. Therefore you need a service that provide you this functionality. Here is the class that you can use it.

I suggest you to import the data and store into a DataTable and from there you can start to manipulate it. You can also use a linq to query the data. Is that simple and easy?

Initialization

PrivatePropertyExcel ppe;
DataTable currentExcelDataTable;

private void btnTestConnection_Click(object sender, EventArgs e)
{
try
{
if (openFileDialog1.ShowDialog() == DialogResult.OK && txtSheetName.Text != "")
{

ppe = new PrivatePropertyExcel(openFileDialog1.FileName, txtSheetName.Text);
currentExcelDataTable = ppe.ExcelProvider.GetDataTable();
gridSource.DataSource = currentExcelDataTable;
gridTarget.DataSource = null;


btnValidate.Enabled = true;
txtStatus.Text = "";
}
else
{
if (txtSheetName.Text == "")
{
MessageBox.Show("Please enter sheet name");
}
}
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
}

Calling function

public List<string> GetPropertyType(int columnIndex)
{
List<string> propertyTypeList = new List<string>();

var propertyTypes =
(from e in ppe.ExcelProvider
where e.GetString(columnIndex).Trim() != ""
select e.GetString(columnIndex).Trim()).Distinct();

foreach (string pt in propertyTypes)
{
if (!propertyTypeList.Contains(pt))
{
propertyTypeList.Add(pt);
}
}

return propertyTypeList;
}

Class

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Collections;
using System.Data;

namespace ReplicatorServices
{
public class ExcelRow
{
List<object> columns;

public ExcelRow()
{
columns = new List<object>();
}

internal void AddColumn(object value)
{
columns.Add(value);
}

public object this[int index]
{
get { return columns[index]; }
}

public string GetString(int index)
{
if (columns[index] is DBNull)
{
return null;
}
return columns[index].ToString();
}

public int Count
{
get { return this.columns.Count; }
}
}

public class ExcelProvider:IEnumerable<ExcelRow>
{
private string sheet;
private string filePath;
private List<ExcelRow> rows;
private string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties= ""Excel 8.0;HDR=YES;""";

public ExcelProvider()
{
rows = new List<ExcelRow>();
}

public static ExcelProvider Create(string filePath, string sheet)
{
ExcelProvider provider = new ExcelProvider();
provider.sheet = sheet;
provider.filePath = filePath;
return provider;
}

public DataTable GetDataTable()
{
DataTable dt= new DataTable();
connectionString = string.Format(connectionString, filePath);
rows.Clear();
using (OleDbConnection conn = new OleDbConnection(connectionString))
{
conn.Open();
using (OleDbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "select DISTINCT * from [" + sheet + "$] ";
using (OleDbDataReader reader = cmd.ExecuteReader())
{
dt.Load(reader);
}
}
}

return dt;
}

public DataTable GetDataTable(string sqlCommand)
{
DataTable dt = new DataTable();
connectionString = string.Format(connectionString, filePath);
rows.Clear();
using (OleDbConnection conn = new OleDbConnection(connectionString))
{
conn.Open();
using (OleDbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sqlCommand;
using (OleDbDataReader reader = cmd.ExecuteReader())
{
dt.Load(reader);
}
}
}

return dt;
}
public List<string> GetColumnsName()
{
List<string> columnlist = new List<string>();
connectionString = string.Format(connectionString, filePath);
rows.Clear();
using (OleDbConnection conn = new OleDbConnection(connectionString))
{
conn.Open();
using (OleDbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "select * from [" + sheet + "$]";
using (OleDbDataReader reader = cmd.ExecuteReader())
{
for (int i = 0; i < reader.FieldCount; i++)
{
columnlist.Add(reader.GetName(i).Trim());
}
}
}
}

return columnlist;
}

private void Load()
{
connectionString = string.Format(connectionString, filePath);
rows.Clear();
using (OleDbConnection conn = new OleDbConnection(connectionString))
{
conn.Open();
using (OleDbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "select * from [" + sheet + "$]";
using (OleDbDataReader reader = cmd.ExecuteReader())
{

while (reader.Read())
{
ExcelRow newRow = new ExcelRow();
for(int count = 0; count < reader.FieldCount; count++) {
newRow.AddColumn(reader[count]);
}
rows.Add(newRow);
}
}
}
}
}

public IEnumerator<ExcelRow> GetEnumerator()
{
Load();
return rows.GetEnumerator();
}

IEnumerator IEnumerable.GetEnumerator()
{
Load();
return rows.GetEnumerator();
}

}
}

Get html tag using regex .net

Let say you want to get a specific tag from the html, you need to use regex to scan and grab it. here is the example :)

private string FixedImgHtml(string html, int fixedwidth)
{
string input = html;
MatchCollection mc = Regex.Matches(input, "<img[a-zA-Z0-9_\\^\\$\\.\\\\{\\[\\}\\]\\(\\)\\*\\+\\?\\\\~`!@#%&-=;:'\",/\\n\\s]*>", RegexOptions.IgnoreCase);
foreach (Match m in mc)
{
input = input.Replace(m.Value, m.Value + " onload='AutoImageResizing(this, "+ fixedwidth.ToString()+")'");
}
return input;
}

dynamic sql generated column using data

Sometimes we want dynamically create a column by using data and not having table structure. You can do it having this sql statement

select *, LFV1.Description as DevName,LFV2.Description as Location
from
listing L
LEFT OUTER JOIN ListingFieldValue LFV1 ON L.ListingID =
LFV1.ListingID AND LFV1.ListingFieldTypeID = '1'
LEFT OUTER JOIN
ListingFieldValue LFV2 ON L.ListingID = LFV2.ListingID AND
LFV2.ListingFieldTypeID = '2'
WHERE L.listingid = 1