public static DataSet importXlsFileToSql(string sFile, CheckBoxList list)
{
NewsletterReceiver receiver;
try
{
DataSet ds = new DataSet();
OleDbDataAdapter MyCommand;
//OleDbConnection MyConnection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sFile + ";Extended Properties=Excel 12.0;");
OleDbConnection MyConnection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sFile + ";Extended Properties='Excel 8.0;HDR=Yes'");
MyConnection.Open();
DataTable _mytableSchema = MyConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
MyConnection.Close();
string sheetName = _mytableSchema.Rows[0]["TABLE_NAME"].ToString();
MyCommand = new System.Data.OleDb.OleDbDataAdapter("SELECT * FROM [" + sheetName + "]", MyConnection);
// MyCommand = new System.Data.OleDb.OleDbDataAdapter("SELECT * FROM [Plan$]", MyConnection);
//MyCommand = new System.Data.OleDb.OleDbDataAdapter("SELECT * FROM [vanessatesteggg$]", MyConnection);
MyCommand.TableMappings.Add("Table", "TesteTable");
MyCommand.Fill(ds);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
//receiver = CtrlNewsletterReceivers.getReceiverByEmail(ds.Tables[0].Rows[i][1].ToString());
//if (receiver == null)
//{
// if (Validation.Email(ds.Tables[0].Rows[i][1].ToString()))
// {
// receiver = new NewsletterReceiver();
// receiver.newsletterReceiverName = ds.Tables[0].Rows[i][0].ToString();
// receiver.newsletterReceiverEmail = ds.Tables[0].Rows[i][1].ToString();
// receiver.newsletterReceiverCityID = 0;
// receiver.newsletterReceiverAddress = "";
// receiver.newsletterReceiverDistrict = "";
// receiver.newsletterReceiverDescription = "";
// receiver.newsletterReceiverBornDate = DateTime.Now;
// receiver.newsletterReceiverUpdateDate = DateTime.Now;
// receiver.newsletterReceiverRegistrationDate = DateTime.Now;
// CtrlNewsletterReceivers.save(receiver);
// }
//}
// NewsletterReceiver receiverExisted = Ctrl.NewsletterReceivers.getReceiverByEmail(ds.Tables[0].Rows[i][1].ToString());
foreach (ListItem item in list.Items)
{
// if (item.Selected)
// {
// int idGroup = Convert.ToInt32(item.Value);
// GroupsNewsletterReceiverGroup obj = CtrlGroupsNewsletterReceiverGroup.getGroupNewsletterReceiverGroupByBothIds(receiverExisted.newsletterReceiverID, idGroup);
// if (obj == null)
// {
// obj = new GroupsNewsletterReceiverGroup();
// obj.newsletterReceiverID = receiverExisted.newsletterReceiverID;
// obj.newsletterReceiverGroupID = idGroup;
// CtrlGroupsNewsletterReceiverGroup.save(obj);
// }
// }
//}
}
MyConnection.Close();
return ds;
}
}
catch (Exception ex)
{
// AlertMessage.show(ex.ToString(), AlertMessageType.ERROR, true);
}
return null;
}
domingo, 20 de setembro de 2015
public void ExportToExcelSE()
{
var pessoas = new System.Data.DataTable("dataTablePessoas");
foreach( TableCell _cell in grdListar.HeaderRow.Cells)
{
pessoas.Columns.Add(_cell.Text);
}
foreach (GridViewRow _rows in grdListar.Rows)
{
pessoas.Rows.Add();
for (int i = 0; i < _rows.Cells.Count; i++)
{
pessoas.Rows[_rows.RowIndex][i] = _rows.Cells[i].Text;
}
// pessoas.Rows.Add(_rows.ItemArray);
}
var grid = new GridView();
grid.DataSource = pessoas;
grid.DataBind();
Response.ClearContent();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment; filename=ArquivoExcelPessoas.xls");
Response.ContentType = "application/ms-excel";
Response.Charset = "";
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
grid.RenderControl(htw);
Response.Output.Write(sw.ToString());
Response.Flush();
Response.End();
// return View("MinhaView");
}
{
var pessoas = new System.Data.DataTable("dataTablePessoas");
foreach( TableCell _cell in grdListar.HeaderRow.Cells)
{
pessoas.Columns.Add(_cell.Text);
}
foreach (GridViewRow _rows in grdListar.Rows)
{
pessoas.Rows.Add();
for (int i = 0; i < _rows.Cells.Count; i++)
{
pessoas.Rows[_rows.RowIndex][i] = _rows.Cells[i].Text;
}
// pessoas.Rows.Add(_rows.ItemArray);
}
var grid = new GridView();
grid.DataSource = pessoas;
grid.DataBind();
Response.ClearContent();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment; filename=ArquivoExcelPessoas.xls");
Response.ContentType = "application/ms-excel";
Response.Charset = "";
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
grid.RenderControl(htw);
Response.Output.Write(sw.ToString());
Response.Flush();
Response.End();
// return View("MinhaView");
}
protected void grdListar_RowCommand(object sender, GridViewCommandEventArgs e)
{
if ((e.CommandName.Equals("Gerar") || (e.CommandName.Equals("Editar")) || (string.Compare(e.CommandName.ToString(), "deletar")) == 0))
{
var _conn = Connection;
try
{
if (e.CommandName.Equals("Editar"))
{
var id = Int32.Parse(e.CommandArgument.ToString());
Response.Redirect("~/Default.aspx?_id=" + id);
}
if ((string.Compare(e.CommandName.ToString(), "deletar")) == 0)
{
var _id = Int32.Parse(e.CommandArgument.ToString());
var _participante = _conn.PARTICIPANTES.Find(_id);
_participante.Habilitado = false;
_conn.SaveChanges();
}else
{
if((e.CommandName.Equals("Gerar"))){
ExportToExcelSE();
// excc();
}
}
}
finally
{
_conn.Dispose();
}
// var id = 0;
// ExportToExcel();
// excc();
}
}
{
if ((e.CommandName.Equals("Gerar") || (e.CommandName.Equals("Editar")) || (string.Compare(e.CommandName.ToString(), "deletar")) == 0))
{
var _conn = Connection;
try
{
if (e.CommandName.Equals("Editar"))
{
var id = Int32.Parse(e.CommandArgument.ToString());
Response.Redirect("~/Default.aspx?_id=" + id);
}
if ((string.Compare(e.CommandName.ToString(), "deletar")) == 0)
{
var _id = Int32.Parse(e.CommandArgument.ToString());
var _participante = _conn.PARTICIPANTES.Find(_id);
_participante.Habilitado = false;
_conn.SaveChanges();
}else
{
if((e.CommandName.Equals("Gerar"))){
ExportToExcelSE();
// excc();
}
}
}
finally
{
_conn.Dispose();
}
// var id = 0;
// ExportToExcel();
// excc();
}
}
<asp:GridView ID="grdListar" Width="100%" AllowCustomPaging="true" PageSize="10" EmptyDataText="Nenhuns registros foram encontrados!" OnRowCommand="grdListar_RowCommand"
data-toggle="table" CssClass="table-hover" AllowSorting="true" AutoGenerateColumns="false" runat="server" DataKeyNames="ID"
ForeColor="#333333" GridLines="None">
<Columns>
<asp:BoundField DataField="COD_PART" HeaderText="Código Participante" />
<asp:BoundField DataField="NOME" HeaderText="Nome" />
<asp:BoundField DataField="CNPJ" HeaderText="CPF/CNPJ" />
<asp:BoundField DataField="IE" HeaderText="Inscição Estadual" />
<asp:TemplateField HeaderText="Gerar">
<ItemTemplate>
<asp:LinkButton ID="LinkButton1" runat="server" Text= "<i class='glyphicon glyphicon-circle-arrow-down icon-white'> </i>" CssClass="btn btn-primary " CommandName="Gerar"
CommandArgument='<%#Eval("ID").ToString() %>'></asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Editar">
<ItemTemplate>
<asp:LinkButton ID="LinkButton2" runat="server" Text='<i class="glyphicon glyphicon-pencil icon-white"></i>' CssClass="btn btn-primary" CommandName="Editar"
CommandArgument='<%#Eval("ID").ToString() %>'> </asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Deletar">
<ItemTemplate>
<asp:LinkButton ID="btnExcluir" runat="server" onClientClick="return fnConfirmDelete();" Text="<i class='glyphicon glyphicon-remove icon-white'></i>" CssClass="btn btn-primary " CommandName="deletar"
CommandArgument='<%#Eval("ID").ToString() %>' />
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</div>
data-toggle="table" CssClass="table-hover" AllowSorting="true" AutoGenerateColumns="false" runat="server" DataKeyNames="ID"
ForeColor="#333333" GridLines="None">
<Columns>
<asp:BoundField DataField="COD_PART" HeaderText="Código Participante" />
<asp:BoundField DataField="NOME" HeaderText="Nome" />
<asp:BoundField DataField="CNPJ" HeaderText="CPF/CNPJ" />
<asp:BoundField DataField="IE" HeaderText="Inscição Estadual" />
<asp:TemplateField HeaderText="Gerar">
<ItemTemplate>
<asp:LinkButton ID="LinkButton1" runat="server" Text= "<i class='glyphicon glyphicon-circle-arrow-down icon-white'> </i>" CssClass="btn btn-primary " CommandName="Gerar"
CommandArgument='<%#Eval("ID").ToString() %>'></asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Editar">
<ItemTemplate>
<asp:LinkButton ID="LinkButton2" runat="server" Text='<i class="glyphicon glyphicon-pencil icon-white"></i>' CssClass="btn btn-primary" CommandName="Editar"
CommandArgument='<%#Eval("ID").ToString() %>'> </asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Deletar">
<ItemTemplate>
<asp:LinkButton ID="btnExcluir" runat="server" onClientClick="return fnConfirmDelete();" Text="<i class='glyphicon glyphicon-remove icon-white'></i>" CssClass="btn btn-primary " CommandName="deletar"
CommandArgument='<%#Eval("ID").ToString() %>' />
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</div>
use DB_SPED
go
create table Teste
(
Id int identity primary key,
Name varchar(25) not null,
Dtnasc date not null
)
select * from Teste
insert into Teste (Name, Dtnasc) values( 'Jean4', '1981-12-18') --GETDATE())
use DB_SPED
go
create Procedure AddTeste
@id int,
@Name varchar( 25),
@dtNasC date
as
begin
insert into Teste (Name, Dtnasc) values(@Name , @dtNasC)
end
exec AddTeste 1, 'greg','2000-12-05'
go
Create Procedure getAll
as
begin
select * from Teste
end
exec getAll
go
create procedure UpdateROWS
@ID INT,
@NAME VARCHAR(25),
@DTNASC DATE
AS
BEGIN
UPDATE Teste SET Name=@NAME, Dtnasc=@DTNASC WHERE Id=@ID
END
SELECT * FROM Teste
GO
EXEC UpdateROWS 2," JEAN JOSEPH","2015-09-19"
go
create procedure FindById
@id int
as
begin
SELECT * FROM Teste
where id=@id
end
exec FindById 4
go
select top 1 * from teste
order by Id desc
go
create table Teste
(
Id int identity primary key,
Name varchar(25) not null,
Dtnasc date not null
)
select * from Teste
insert into Teste (Name, Dtnasc) values( 'Jean4', '1981-12-18') --GETDATE())
use DB_SPED
go
create Procedure AddTeste
@id int,
@Name varchar( 25),
@dtNasC date
as
begin
insert into Teste (Name, Dtnasc) values(@Name , @dtNasC)
end
exec AddTeste 1, 'greg','2000-12-05'
go
Create Procedure getAll
as
begin
select * from Teste
end
exec getAll
go
create procedure UpdateROWS
@ID INT,
@NAME VARCHAR(25),
@DTNASC DATE
AS
BEGIN
UPDATE Teste SET Name=@NAME, Dtnasc=@DTNASC WHERE Id=@ID
END
SELECT * FROM Teste
GO
EXEC UpdateROWS 2," JEAN JOSEPH","2015-09-19"
go
create procedure FindById
@id int
as
begin
SELECT * FROM Teste
where id=@id
end
exec FindById 4
go
select top 1 * from teste
order by Id desc
public static class DbCommandExtensions
{
public static void AddParam(this DbCommand dbCommand, string fieldName, object fieldValue)
{
string fieldNameParameter = fieldName;
DbParameter dbParameter = dbCommand.CreateParameter();
dbParameter.ParameterName = fieldNameParameter;
dbParameter.Value = fieldValue;
dbCommand.Parameters.Add(dbParameter);
}
}
params.Add("@FileName1", "my_filename")
foreach(var param in params)
dbCommand.Parameters.AddWithValue(param.Key, param.Value);
DbCommand dbCommand = SqlDb.GetStoredProcCommand(uspCommand);
foreach(String param in MyParameters)
{
DbParameter ProcessedFileName = dbCommand.CreateParameter();
ProcessedFileName.DbType = DbType.String;
ProcessedFileName.ParameterName = param;
ProcessedFileName.Value = pstrProcessedFileName;
dbCommand.Parameters.Add(ProcessedFileName);
}
Assinar:
Postagens (Atom)