saroj bhattacharya

NA
0
41.3k

covert code form sql server to my sql

Jul 16 2015 5:08 AM

can any one convert my code into mysql.

i am make code for csv file upload using sql server in asp.net

but now i want make this code for my sql database

my code is bellow

csvupload.aspx.cs-

?using System;

using System.Collections.Generic;

using System.Data;

using System.Data.SqlClient;

using System.IO;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

namespace csvfileupload2

{

public partial class csvupload : System.Web.UI.Page

{

string consString = @"Data Source=ANIRUDDHA-PC;Initial Catalog=sark;Integrated Security=True";

protected void Page_Load(object sender, EventArgs e)

{

BindGrid();

}

protected void Upload(object sender, EventArgs e)

{

//Upload and save the file

string fileName = Path.GetFileName(FileUpload1.PostedFile.FileName);

string csvPath = Server.MapPath("~/Files/") + fileName;

FileUpload1.SaveAs(csvPath);

int file_id = 0;

using (SqlConnection con = new SqlConnection(consString))

{

con.Open();

SqlCommand cmd = con.CreateCommand();

cmd.CommandText = "insert into files (filename,created_date) values('" + fileName + "','" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "')";

int i = cmd.ExecuteNonQuery();

con.Close();

con.Open();

SqlCommand cmd1 = con.CreateCommand();

cmd1.CommandText = "SELECT TOP 1 * FROM files ORDER BY file_id DESC";

SqlDataReader rdr = cmd1.ExecuteReader();

if (rdr.HasRows)

{

rdr.Read();

file_id = Convert.ToInt32(rdr[0]);

}

con.Close();

}

DataTable dt = new DataTable();

dt.Columns.AddRange(new DataColumn[16] {

new DataColumn("file_id", typeof(int)),

new DataColumn("phone_number"),

new DataColumn("title"),

new DataColumn("first_name"),

new DataColumn("middle_initial"),

new DataColumn("last_name"),

new DataColumn("address"),

new DataColumn("city"),

new DataColumn("state"),

new DataColumn("province"),

new DataColumn("postal_code"),

new DataColumn("country_code"),

new DataColumn("gender"),

new DataColumn("date_of_birth"),

new DataColumn("alt_phone"),

new DataColumn("email")});

string csvData = File.ReadAllText(csvPath);

foreach (string row in csvData.Split('\n'))

{

if (!string.IsNullOrEmpty(row))

{

dt.Rows.Add();

dt.Rows[dt.Rows.Count - 1][0] = Convert.ToInt32(file_id);

int i = 1;

foreach (string cell in row.Split(','))

{

dt.Rows[dt.Rows.Count - 1][i] = cell;

i++;

}

using (SqlConnection con = new SqlConnection(consString))

{

using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))

{

//Set the database table name

sqlBulkCopy.DestinationTableName = "dbo.profilesrr";

sqlBulkCopy.ColumnMappings.Add("file_id", "file_id");

sqlBulkCopy.ColumnMappings.Add("phone_number", "phone_number");

sqlBulkCopy.ColumnMappings.Add("title", "title");

sqlBulkCopy.ColumnMappings.Add("first_name", "first_name");

sqlBulkCopy.ColumnMappings.Add("middle_initial", "middle_initial");

sqlBulkCopy.ColumnMappings.Add("last_name", "last_name");

sqlBulkCopy.ColumnMappings.Add("address", "address");

sqlBulkCopy.ColumnMappings.Add("city", "city");

sqlBulkCopy.ColumnMappings.Add("state", "state");

sqlBulkCopy.ColumnMappings.Add("province", "province");

sqlBulkCopy.ColumnMappings.Add("postal_code", "postal_code");

sqlBulkCopy.ColumnMappings.Add("country_code", "country_code");

sqlBulkCopy.ColumnMappings.Add("gender", "gender");

sqlBulkCopy.ColumnMappings.Add("date_of_birth", "date_of_birth");

sqlBulkCopy.ColumnMappings.Add("alt_phone", "alt_phone");

sqlBulkCopy.ColumnMappings.Add("email", "email");

con.Open();

sqlBulkCopy.WriteToServer(dt);

con.Close();

}

public void BindGrid()

{

using (SqlConnection con = new SqlConnection(consString))

{

con.Open();

SqlCommand cmd = con.CreateCommand();

cmd.CommandText = "select * from files";

SqlDataReader rdr = cmd.ExecuteReader();

DataTable dt = new DataTable();

dt.Load(rdr);

GV_List.DataSource = dt;

GV_List.DataBind();

con.Close();

}

protected void Button1_Click(object sender, EventArgs e)

{

using (SqlConnection con = new SqlConnection(consString))

{

con.Open();

DataSet ds = new DataSet();

SqlCommand cmd = new SqlCommand("select * from profilesrr", con);

SqlDataAdapter da = new SqlDataAdapter();

da.SelectCommand = cmd;

da.Fill(ds);

da.Dispose();

cmd.Dispose();

con.Close();

GridView1.DataSource = ds.Tables[0];

GridView1.DataBind();

}

i want to convert this code for my sql database.

my design page code are bellow

csvupload.aspx-

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="csvupload.aspx.cs" Inherits="csvfileupload2.csvupload" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<head>

.style1

{

color: #FF0066;

text-decoration: underline;

font-weight: bold;

}

</style>

</head>

<body>

<h2>Well Come To WildCard Techno Services Pvt Ltd</h2>

</header>

<li><a class="hsubs" href="#">Call Details</a>

<li><a href="csvupload.aspx">File Upload</a></li>

<li><a href="search.aspx">search_data</a></li>

<li><a href="registration.aspx">New Registration</a></li>

<li><a href="empdetails.aspx">empdetails</a></li>

<li><a href="call_histry.aspx">Call Histry</a></li>

</ul>

</li>

<li><a href="#">Submenu 2-1</a></li>

<li><a href="#">Submenu 2-2</a></li>

<li><a href="#">Submenu 2-3</a></li>

<li><a href="#">Submenu 2-4</a></li>

<li><a href="#">Submenu 2-5</a></li>

<li><a href="#">Submenu 2-6</a></li>

<li><a href="#">Submenu 2-7</a></li>

<li><a href="#">Submenu 2-8</a></li>

</ul>

</li>

<li><a href="#">Submenu 3-1</a></li>

<li><a href="#">Submenu 3-2</a></li>

<li><a href="#">Submenu 3-3</a></li>

<li><a href="#">Submenu 3-4</a></li>

<li><a href="#">Submenu 3-5</a></li>

</ul>

</li>

</ul>

<div>

<tr><td class="style1" colspan="3" style="text-align: center; background-color: #FEEEDE" height="20">File Upload</td></tr>

<tr>

<td>Select File</td>

<td>

<asp:FileUpload ID="FileUpload1" runat="server"

ToolTip="Select Only Excel File" Height="27px" />

</td>

<td>

<asp:Button ID="btnUpload" OnClick="Upload" runat="server" Text="Upload"/>

</td>

<td>

<asp:Button ID="btnViewFiles" runat="server" Text="View Files" Height="25px"/>

</td>

</tr>

</table>

<table>

<tr>

<td>

<p>

<asp:Label ID="lblMessage" runat="server" Text=""></asp:Label>

</p>

</td>

</tr>

</table>

<asp:GridView ID="GV_List" runat="server" Caption=" Uploaded Files "

CaptionAlign="Top" HorizontalAlign="Justify"

DataKeyNames="file_id"

ToolTip="Excel FIle DownLoad Tool" CellPadding="4" ForeColor="#333333"

GridLines="None">

<asp:TemplateField>

<asp:HyperLink ID="HyperLink1" NavigateUrl='<%#"~/Files/"+ Eval("filename") %>' runat="server">Download</asp:HyperLink>

</ItemTemplate>

</asp:TemplateField>

</Columns>

</asp:GridView>

<br />

<asp:Button ID="Button1" runat="server" Text="show data" onclick="Button1_Click" />

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False">

<asp:HyperLinkField DataTextField="phone_number" DataNavigateUrlFields="phone_number" DataNavigateUrlFormatString="~/demo.aspx?phone_number={0}"

HeaderText="phone_number" ItemStyle-Width = "150" />

<asp:BoundField DataField="title" HeaderText="title" />

<asp:BoundField DataField="first_name" HeaderText="first_name" />

<asp:BoundField DataField="middle_initial" HeaderText="middle_initial" />

<asp:BoundField DataField="last_name" HeaderText="last_name" />

<asp:BoundField DataField="address" HeaderText="address" />

<asp:BoundField DataField="city" HeaderText="city" />

<asp:BoundField DataField="state" HeaderText="state" />

<asp:BoundField DataField="province" HeaderText="province" />

<asp:BoundField DataField="postal_code" HeaderText="postal_code" />

<asp:BoundField DataField="country_code" HeaderText="country_code" />

<asp:BoundField DataField="gender" HeaderText="gender" />

<asp:BoundField DataField="email" HeaderText="email" />

</Columns>

</asp:GridView>

</center>

</div>

</form>

</body>

</html>

Answers (8)

Unable to view my website in IE10 browser

Can you give me example page of EMI payment in asp.net