Wednesday, May 6, 2009

Auto Complete In MVC.

1.Write Code in Aspx Page As Below:-


2.Add JavaScript As Below

 function SearchData()    //eVERY TIME IT GO TO DATABASE

{

//where Home IS Controller And Search Is Method,SearchValue IS argument

$.getJSON("/Home/Search",

{ SearchValue:document.getElementById("listSearch").value},

function(result)

{

$("#SearchResult").empty();

$.each(result, function(i,temp) {

$("#SearchResult").append("

");

});

});

}

  

var NameList;

$(document).ready(function()

{

//where Home IS Controller And Search Is Method,SearchValue IS argument

$.getJSON("/Home/Search",

{ SearchValue:""},

function(result)

{

NameList=result;

$.each(NameList, function(i,temp) {

$("#SearchResult2").append("

");

});

});

});

  

function Search1()   //IT DO AT LOCAL

{

$("#SearchResult2").empty();

var listSearch2=document.getElementById("listSearch2");

var txt=new RegExp(listSearch2.value,"i");

$.each(NameList, function(j,temp) {

if(temp.EmployeeName.search(txt)!=-1)

{

$("#SearchResult2").append("

");

}

});

}

 

3. Code In Controller Will Be like This:-

 public JsonResult Search(string SearchValue)

        {

            string EmpNameList = "";

            List<Employee> li = new List<Employee>();

            EmployeeDAL empDal = new EmployeeDAL();

            li = empDal.SearchEmployee(SearchValue);

            return Json(li);

        }

Monday, May 4, 2009

Read Data From Excel Sheet In C#.

public ActionResult ReadExcelData()

{

var sourceFile="C:\\Documents and

                Settings\\Administrator\\Desktop\\Taxonomy_Read.xls";

 

string srcConnString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="

        +sourceFile + @";Extended Properties=""Excel 8.0;HDR=YES;""";

string srcQuery = "Select * from [" + GetExcelSheetNames(

                                   sourceFile)[0] + "]";

OleDbConnection srcConn = new OleDbConnection( srcConnString);

srcConn.Open();

OleDbCommand objCmdSelect =new OleDbCommand( srcQuery, srcConn);

OleDbDataReader readerExcel = objCmdSelect.ExecuteReader(

                              CommandBehavior.CloseConnection);

 

while (readerExcel.Read())

{

var i = 0;

while (i <>

{

var data = readerExcel[i].ToString();

i++;

}

}

readerExcel.Close();

return View("About");

}

 

 

static String[] GetExcelSheetNames(string excelFile)

{

OleDbConnection objConn = null;

System.Data.DataTable dt = null;

try

{

// Connection String. Change the excel file to the file you

// will search.

String connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +

  "Data Source=" + excelFile + ";

   Extended Properties=Excel 8.0;";

// Create connection object by using the preceding connection string.

objConn = new OleDbConnection(connString);

// Open connection with the database.

objConn.Open();

// Get the data table containg the schema guid.

dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

if (dt == null)

{

return null;

}

String[] excelSheets = new String[dt.Rows.Count];

int i = 0;

// Add the sheet name to the string array.

foreach (DataRow row in dt.Rows)

{

excelSheets[i] = row["TABLE_NAME"].ToString();

i++;

}

return excelSheets;

}

catch (Exception ex)

{

return null;

}

finally

{

// Clean up.

if (objConn != null)

{

objConn.Close();

objConn.Dispose();

}

if (dt != null)

{

dt.Dispose();

}

}

}

 

 

 

 

//-----OR Can Use Another Method Below But it requires 2 Dll  -----//

            >> Microsoft.Office.Interop.Excel.dll

          >>Office.dll

public ActionResult ReadExcelData()

{

//Request.Files["EditImgFile"];

Microsoft.Office.Interop.Excel.Application ExcelObj = null;

ExcelObj = new Microsoft.Office.Interop.Excel.Application();

if (ExcelObj == null)

{

//MessageBox.Show("ERROR: EXCEL couldn't be started!");

//System.Windows.Forms.Application.Exit();

}

var sourceFile="C:\\Documents and

                Settings\\Administrator\\Desktop\\Taxonomy_Read.xls";

 

Microsoft.Office.Interop.Excel.Workbook theWorkbook = ExcelObj.Workbooks.Open(sourceFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing,

Type.Missing, Type.Missing, Type.Missing, Type.Missing,

Type.Missing, Type.Missing, Type.Missing, Type.Missing,

Type.Missing, Type.Missing);

Microsoft.Office.Interop.Excel.Sheets sheets = theWorkbook.Worksheets;

Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(1);

int Ano = 0;

List<string> ParentList = new List<string>();

for(int x = 1; x <= 29; x++)

{

Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range("A"+x.ToString(), "I" + x.ToString());

System.Array myvalues = (System.Array)range.Cells.get_Value(Microsoft.Office.Interop.Excel.XlRangeValueDataType.xlRangeValueDefault);

string[] strArray = ConvertToStringArray(myvalues);

for(int i=0;i<=strArray.Length;i++)

{

if (!string.IsNullOrEmpty(strArray[i])&&i==0)

{

ParentList.Clear();

Ano = 0;

// //insert Main Menu

//var Id = insertTaxonomy(strArray[i],-1);

// //Add return id to ParentList

//ParentList.Add(Id.Tostring());

break;

}

if (!string.IsNullOrEmpty(strArray[i]))

{

////insert sub menu

//var Id = insertTaxonomy(strArray[i],ParentList.ToArray()[Ano-1]);

// //Add return id to ParentList

//ParentList.Insert(Ano, Id.Tostring());

ParentList.RemoveRange(Ano + 1, ParentList.Count-Ano);

Ano = 0;

break;

}

Ano++;

}

}

theWorkbook.Close(false, hpf.FileName, false);

return View("About");

}

 

 

public static string[] ConvertToStringArray(System.Array values)

{

// create a new string array

string[] theArray = new string[values.Length];

// loop through the 2-D System.Array and populate the 1-D String Array

for (int i = 1; i <= values.Length; i++)

{

if (values.GetValue(1, i) == null)

theArray[i - 1] = "";

else

theArray[i - 1] = (string)values.GetValue(1, i).ToString();

}

return theArray;

}

SQL Data Base Connection in C#

1. Connection with Your Database in Your Code, Data source is IP of data server:-

SqlConnection conn;

conn = new SqlConnection("Data Source=192.168.100.44;

Initial Catalog=DataBaseName;

Persist Security Info=True;

User ID=scoat;

Password=tiger");

conn.Open();

1.1 Connection Using WebConfig, Add this in webconfig:-

<connectionStrings>

<add name="SampleConnectionString"

connectionString="data source=192.168.100.44;

Initial Catalog=DataBaseName;

Persist Security Info=True;

User ID=scoat;

Password=tiger" providerName="System.Data.SqlClient"/>

<connectionStrings>

1.2 Add This In your code:-

SqlConnection conn = New SqlConnection

(ConfigurationManager

.ConnectionStrings["SampleConnectionString"].ConnectionString);

2.Spiecify store procedure Want To Use:-

SqlCommand cmd = new SqlCommand("ProcedureName", conn);

cmd.CommandType = CommandType.StoredProcedure;

3.Pass Parameter To Store Procedure:-

SqlParameter parameterObj = cmd.Parameters.Add("@ProcedureVariable",SqlDbType.Int);

parameterObj.Value = DataToPass;

4. Execut Procedure And Close Connection:-

SqlDataReader thisReader = cmd.ExecuteReader();

thisReader.Close();

conn.Close();