.NET Development: agosto 2011

miércoles, 31 de agosto de 2011

martes, 30 de agosto de 2011

lunes, 29 de agosto de 2011

Database engine stored procedures (Transact-SQL)

Article

Import .xls to SQL

Web Developer Article
CodeProject Article

jueves, 25 de agosto de 2011

miércoles, 24 de agosto de 2011

Keypressed to null

private void TextBox_KeyPress(object sender, KeyPressEventArgs e)
{
e.KeyChar = Convert.ToChar(0);

//equivalent expression
e.Handle = false;
}

jueves, 18 de agosto de 2011

IF OBJECT_ID('SPS_PAGEMAKER') IS NOT NULL
DROP PROCEDURE SPS_PAGEMAKER
GO

CREATE PROCEDURE SPS_PAGEMAKER
@ID NVARCHAR(50),
@TABLE NVARCHAR(50),
@ROWSxPAGE INT,
@CURRENTPAGE INT,
@FIELDS NVARCHAR(1000)

AS
BEGIN

/*
SUMMARY: SCRIPT PAGINADOR
CALL: SPS_PAGEMAKER('IDBHTransaction', 'BHTransaction', 5, 1)
MADE: AGO 18, 2011

EXAMPLE CODE:

DECLARE
@ID NVARCHAR(50),
@TABLE NVARCHAR(50),
@ROWSxPAGE INT,
@CURRENTPAGE INT,
@FIELDS NVARCHAR(1000)

SET @ID = 'IDBHTransaction'
SET @TABLE = 'BHTransaction'
SET @ROWSxPAGE = 5
SET @CURRENTPAGE = 2
SET @FIELDS = 'IDBHTransaction, f0, f2, f3, f4'
*/

--DYNAMIC SQL (SET @ROWS)

DECLARE @SQL NVARCHAR(1000)
DECLARE @PARAM NVARCHAR(1000)
DECLARE @pTABLE NVARCHAR(50)
DECLARE @ROWS INT

SET @SQL = N'SELECT @pROWS = COUNT(@pID) FROM ' + @TABLE;
SET @PARAM = N'@pID VARCHAR(25), @TABLE VARCHAR(25), @pROWS INT OUTPUT';
EXECUTE sp_executesql @SQL, @PARAM, @pID = @ID, @TABLE = @pTABLE, @pROWS = @ROWS OUTPUT;

--/DYNAMIC SQL

DECLARE @REST INT
DECLARE @PAGES INT

SET @REST = (SELECT @ROWS % @ROWSxPAGE)
SET @PAGES = (SELECT @ROWS / @ROWSxPAGE) IF (@REST > 0) SET @PAGES = @PAGES+1

--SELECT @ROWS AS ROWS, @ROWSxPAGE AS ROWSxPAGE, @PAGES AS PAGES, @REST AS REST

IF @CURRENTPAGE = 1
BEGIN
SET @SQL = N'SELECT ' + @FIELDS +
' FROM ' + @TABLE +
' WHERE ((' + @ID + ' >= 1) AND (' + @ID + ' < (1 + ' + CONVERT(NVARCHAR(5),@ROWSxPAGE) + ')))';
SET @PARAM = N'@TABLE VARCHAR(25)';
EXECUTE sp_executesql @SQL, @PARAM, @TABLE = @pTABLE;
END
ELSE
IF (@CURRENTPAGE = @PAGES) AND (@REST > 0)
BEGIN
SET @SQL = N'SELECT ' + @FIELDS +
' FROM ' + @TABLE +
' WHERE (' + @ID + ' >= ((' + CONVERT(NVARCHAR(5),@CURRENTPAGE) + ' - 1) * ' + CONVERT(NVARCHAR(5),@ROWSxPAGE) + ') + 1)';
SET @PARAM = N'@TABLE VARCHAR(25)';
EXECUTE sp_executesql @SQL, @PARAM, @TABLE = @pTABLE;
END
ELSE
BEGIN
SET @SQL = N'SELECT ' + @FIELDS +
' FROM ' + @TABLE +
' WHERE ((' + @ID + ' >= (((' + CONVERT(NVARCHAR(5),@CURRENTPAGE) + ' - 1) * ' + CONVERT(NVARCHAR(5),@ROWSxPAGE) + ') + 1)) AND '+
'(' + @ID + ' < ((((' + CONVERT(NVARCHAR(5),@CURRENTPAGE) + ' - 1) * ' + CONVERT(NVARCHAR(5),@ROWSxPAGE) + ') + 1) + ' + CONVERT(NVARCHAR(5),@ROWSxPAGE) + ')))';
SET @PARAM = N'@TABLE VARCHAR(25)';
EXECUTE sp_executesql @SQL, @PARAM, @TABLE = @pTABLE;
END
END
GO

Who to read an excel file using C#

Paso 1: Descargar el setup de Access Database Engine 2010 Redistributable

http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=13255

Paso 2: Ocupar “Microsoft Excel Driver” con las clases de Odbc indicando la ruta del archivo.

Importamos la libreria System.Data.Odbc

Código:

string CadenaConexion = @"driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};driverid=1046;dbq=C:\Users\v-edisga\Desktop\Libro1.xlsx;";

DataSet ds = null;

try
{
using (OdbcConnection conn = new OdbcConnection(CadenaConexion))
{
conn.Open();
using (OdbcCommand command = conn.CreateCommand())
{
command.CommandText = "SELECT * FROM [Hoja1$]";
OdbcDataAdapter ad = new OdbcDataAdapter(command);
ds = new DataSet();
ad.Fill(ds);
GridView1.DataSource = ds.Tables[0];
GridView1.DataBind();
}
}
}
catch (Exception ex)
{
Response.Write(ex.Message.ToString());
}

Source: http://ewebmx.com/2011/08/como-leer-un-archivo-de-excel-2010-en-c/

miércoles, 17 de agosto de 2011

Config file for application in C#

//Application Global Variables
int app_owner = 0;
Dictionary() operators;

...

operators = new Dictionary();

using (StreamReader str = new StreamReader(@"app.cfg"))
{
string strline;
strline = str.ReadLine();
string value = "";

while (!str.EndOfStream)
{
while (strline != "[END]")
{
if (strline.StartsWith("["))
{
value = strline;
}
else
{
operators.Add(int.Parse(strline), value);
if (value.IndexOf("*") > 0)
owner = int.Parse(strline);
}

strline = str.ReadLine();
}
}

...

//file content: app.cfg

[JOHN]
1 -------------(#) IS THE ID FOR JOHN
[THOMAS]* ------(*) MARK FOR THE OWNER OR ADMINISTRATOR
2
[END] ---------- MARK FOR THE END

Log file for application in C#

StreamWriter log;
log = File.AppendText(@"app.log");
log.WriteLine(System.DateTime.Now.ToString() + " Read DB Data.");
log.Close();

jueves, 11 de agosto de 2011

Linq & Lamda syntaxis example

var query = from q in model.Marca
select q;
foreach (var q in query)
Console.WriteLine(q.Descripcion);

var query = model.Marca;
foreach (var q in query)
Console.WriteLine(q.Descripcion);

var query = from q in model.Marca
where q.ClaveMarca == "005"
select q;
foreach (var q in query)
Console.WriteLine(q.Descripcion);

var query = model.Marca.Where(q => q.ClaveMarca == "005");
foreach (var q in query)
Console.WriteLine(q.Descripcion);

var query = from q in model.Marca
where q.ClaveMarca == "005"
select q.Descripcion;
foreach (var q in query)
Console.WriteLine(q);

var query = model.Marca.Where(q => q.ClaveMarca == "005").Select(q => q.Descripcion).Distinct();
foreach (var q in query)
Console.WriteLine(q);

var query = model.Marca.Where(q => q.ClaveMarca == "005").Distinct();
foreach (var q in query)
Console.WriteLine(q.Descripcion);

var query = model.Marca.FirstOrDefault(q => q.ClaveMarca == "005");
Console.WriteLine(query.Descripcion);

var query = model.Marca.Where(q => q.ClaveEntidad == 0).FirstOrDefault();
Console.WriteLine(query.Descripcion);

var query = (from q in model.Marca
where q.ClaveMarca == "005"
select q).FirstOrDefault() ;
Console.WriteLine(query.Descripcion);

var query = from q in model.Marca
where q.ClaveMarca == "005"
select new {
q.ClaveMarca,
q.Descripcion
};
foreach(var q in query)
Console.WriteLine(q);

var query = from q in model.ClaveVehicularSFA
where q.ClaveEntidad == 85
select new
{
cv = q.ClaveVehicular,
m = q.Marca.Descripcion,
l = q.Linea.Descripcion
};
foreach (var q in query)
Console.WriteLine(q);

var query = from q in model.ClaveVehicularSFA
where q.Marca.Descripcion == "NISSAN"
select new
{
q.ClaveVehicular,
q.Marca.Descripcion
};
foreach (var q in query)
Console.WriteLine(q);

var query = model.ClaveVehicularSFA.Where(q => q.Marca.Descripcion == "NISSAN").Select(q => new { q.ClaveVehicular, q.Marca.Descripcion });
foreach (var q in query)
Console.WriteLine("{0} {1}", q.ClaveVehicular, q.Descripcion);

var query = from q in model.ClaveVehicularSFA.Include("Marca").Include("Linea")
where q.ClaveVehicular == "0040702"
select q;
foreach (var q in query)
Console.WriteLine("{0} {1} {2}", q.ClaveVehicular, q.Marca.Descripcion, q.Linea.Descripcion);

var query = model.ClaveVehicularSFA.Include("Marca").Include("Linea").Where(q => q.ClaveVehicular == "0040702");
foreach (var q in query)
Console.WriteLine("{0} {1} {2}", q.ClaveVehicular, q.Marca.Descripcion, q.Linea.Descripcion);

var query = model.ClaveVehicularSFA.Where(q => q.ClaveVehicular == "0040702");
foreach (var q in query)
{
if (!q.MarcaReference.IsLoaded)
q.MarcaReference.Load();
Console.WriteLine("{0} {1}", q.ClaveVehicular, q.Marca.Descripcion);
}

var query = model.ClaveVehicularSFA.Include("Marca").Include("Linea").Include("Version").Where(q => q.ClaveVehicular == "0040702");
foreach (var q in query)
Console.WriteLine("{0} {1} {2} {3}", q.ClaveVehicular, q.Marca.Descripcion, q.Linea.Descripcion, q.Version.Descripcion);

var query = from q in model.ClaveVehicularSFA
where q.ClaveVehicular == "0040702"
select new
{
cv = q.ClaveVehicular,
m = q.Marca.Descripcion,
l = q.Linea.Descripcion,
v = q.Version.Descripcion
};
foreach (var q in query)
Console.WriteLine("{0} {1} {2} {3}", q.cv, q.m, q.l, q.v);

var query = (from q in model.ClaveVehicularSFA
where q.ClaveVehicular == "0040702"
select new
{
cv = q.ClaveVehicular,
m = q.Marca.Descripcion,
l = q.Linea.Descripcion,
v = q.Version.Descripcion
}).FirstOrDefault();
Console.WriteLine("{0} {1} {2} {3}", query.cv, query.m, query.l, query.v);

var query = from q in model.ClaveVehicularSFA
where q.Marca.ClaveMarca == "004"
select new
{
m = q.Marca.Descripcion,
l = q.Linea.Descripcion,
};
foreach(var q in query)
Console.WriteLine("{0} {1}", q.m, q.l);

var query = from c in model.ClaveVehicularSFA
from t in c.TipoFormato
where c.ClaveVehicular == "0040702" && t.NumeroFormato == 9
select c;
foreach (var q in query)
foreach(var t in q)
Console.WriteLine(t.Descripcion);

var query = from c in model.ClaveVehicularSFA
from t in c.TipoFormato
where c.ClaveVehicular == "0040702" && t.NumeroFormato == 9
select c;
if (query.Count() > 0)
Console.WriteLine("estan relacionadas");
else
Console.WriteLine("No estan relacionadas");

//INSERT
var p = new Puerta();
p.ClaveEstadoEntidad = 1;
p.Comentarios = "";
p.CveEntOficinaCambio = 2;
p.CveEntUsuarioCambio = 2;
p.Descripcion = "Descripcion";
p.FechaCambio = DateTime.Now;
p.IPCambio = "127.0.0.1";
model.AddToPuerta(p);
model.SaveChanges();
var query = from q in model.Puerta
select q;
foreach (var q in query)
Console.WriteLine("{0} {1}", q.ClaveEntidad, q.Descripcion);
var query = (from q in model.Puerta
where q.ClaveEntidad == 50
select q).FirstOrDefault();
//Console.WriteLine("{0} {1}", query.ClaveEntidad, query.Descripcion);

//OBJECT 1
var ph = new PuertaHist();
ph.ClaveEstadoEntidad = 1;
ph.Comentarios = "comentarios";
ph.CveEntOficinaCambio = 2;
ph.CveEntUsuarioCambio = 2;
ph.Descripcion = "ph-descripcion";
ph.FechaCambio = DateTime.Now;
ph.IPCambio = "127.0.0.1";
ph.Puerta = query;
model.AddToPuertaHist(ph);
model.SaveChanges();
var query2 = from q2 in model.PuertaHist.Include("Puerta")
select q2;
foreach(var q2 in query2)
Console.WriteLine("{0} {1} {2}", q2.ClaveEntidad, q2.Descripcion, q2.Puerta.ClaveEntidad);

//OBJECT 2
var ph = new PuertaHist();
ph.ClaveEstadoEntidad = 1;
ph.Comentarios = "comentarios";
ph.CveEntOficinaCambio = 2;
ph.CveEntUsuarioCambio = 2;
ph.Descripcion = "ph-descripcion";
ph.FechaCambio = DateTime.Now;
ph.IPCambio = "127.0.0.1";
ph.PuertaReference.EntityKey = new EntityKey(model.DefaultContainerName + ".Puerta", "ClaveEntidad", (byte)49);

model.AddToPuertaHist(ph);
model.SaveChanges();
var query2 = from q2 in model.PuertaHist.Include("Puerta")
select q2;
foreach (var q2 in query2)
Console.WriteLine("{0} {1} {2}", q2.ClaveEntidad, q2.Descripcion, q2.Puerta.ClaveEntidad);

// OBJECT 1 & 2
var p = new Puerta();
p.ClaveEstadoEntidad = 1;
p.Comentarios = "";
p.CveEntOficinaCambio = 2;
p.CveEntUsuarioCambio = 2;
p.Descripcion = "Descripcion";
p.FechaCambio = DateTime.Now;
p.IPCambio = "127.0.0.1";

var ph = new PuertaHist();
ph.ClaveEstadoEntidad = 1;
ph.Comentarios = "comentarios";
ph.CveEntOficinaCambio = 2;
ph.CveEntUsuarioCambio = 2;
ph.Descripcion = "ph-descripcion";
ph.FechaCambio = DateTime.Now;
ph.IPCambio = "127.0.0.1";
ph.Puerta = p;

model.AddToPuertaHist(ph);
model.SaveChanges();
var query = from q in model.PuertaHist.Include("Puerta")
select q;
foreach (var q in query)
Console.WriteLine("{0} {1} {2} {3}", q.ClaveEntidad, q.Descripcion, q.Puerta.ClaveEntidad, q.Puerta.Descripcion);

//OTRA SINTAXIS
var querypersona = from p in personas
where p.Edad>5
orderby p.Nombre, p.Apellido1, p.Apellido2
select new
{
p.Nombre,
p.Apellido1,
p.Apellido2,
p.Edad,
NombreCompleto =
String.Format("{0} {1} {2}",
p.Nombre,
p.Apellido1,
p.Apellido2)
};

//UPDATE
var query = (from q in model.Puerta
where q.ClaveEntidad == 51
select q).FirstOrDefault();
query.Descripcion = "Nueva descrip.";
model.SaveChanges();
var query2 = (from q2 in model.Puerta
where q2.ClaveEntidad == 51
select q2).FirstOrDefault();
Console.WriteLine(query2.Descripcion);

More examples: 101 Linq Examples

.NET Development