sql bulkcopy

sql bulk copy from datatable into sql table(with transaction)


 Dim con As New  SqlConnection("connection string")
 con.Open()

 Dim myTransaction As SqlTransaction = con.BeginTransaction()
 Dim bulkCopy As New SqlBulkCopy(con, SqlBulkCopyOptions.Default,   myTransaction)

bulkCopy.DestinationTableName = "table name"
bulkCopy.WriteToServer("datatable")
myTransaction.Commit()
bulkCopy.Close()

Note:
you can use column mapping
bulkCopy.ColumnMappings.Add("column name in datatable", "column name in sql table")

LINQ query to DataTable

Converting Anonymous type generated by LINQ to a DataTable type(here LINQ left outer join)

  customer                                                      city

id   name  city_id                                     cid       city_name                         

1     xxx        2                                             1           aaa

2     yyy         3                                            2           bbb

                                                                     3           ccc

First create temporary datatable with columns you need

Dim tbTemp As New DataTable
        tbTemp.Columns.Add("cust_name")
        tbTemp.Columns.Add("city_name")
     

Using left outer join  

 Dim JoinedResult As IEnumerable(Of DataRow) = From t1 In                                                customer.AsEnumerable()
         Group Join t2 In city.AsEnumerable()
         On t1.Field(Of String)("city_id") Equals t2.Field(Of String)("cid")
        Into RightTableResults = Group
        From t In RightTableResults.DefaultIfEmpty
        Select tbTemp.LoadDataRow(New Object() {t1.Item("cust_name"),      t.Item("city_name")})

 Dim tbResult As New DataTable

tbResult=JoinedResult.CopyToDataTable() 

Reference

http://social.msdn.microsoft.com/Forums/en/csharplanguage/thread/6d4e2579-f4b5-497c-ad00-7c347eb1f902 

 

Remove empty row from datatable

Remove empty row from datatable in VB.NET

DataTable = DataTable.Rows.Cast(Of DataRow)().Where(Function(row) Not row.ItemArray.All(Function(field) TypeOf field Is System.DBNull OrElse String.Compare(TryCast(field, String).Trim(), String.Empty) = 0)).CopyToDataTable()

in C#

dataTable = dataTable.Rows.Cast<DataRow>().Where(row => !row.ItemArray.All(field => 

field is System.DBNull || string.Compare((field as string).Trim(),  

string.Empty) == 0)).CopyToDataTable();

Referred from

http://stackoverflow.com/questions/7023140/how-to-remove-empty-rows-from-datatable 

Store Indian currency symbol in SQL Database

SQL DataBase

create table currency(currency_country varchar(30),currency_name varchar(20),currency_symbol nvarchar(10))

insert into  currency values('india','rupee',N'रु'')


Tips:

Display Indian currency format in ASP.NET

 Dim price As Double
 Dim CInfo As New CultureInfo("hi-IN")
            price=50000.00
            Label1.Text = price.ToString("C", CInfo) // display --> रु 50,000.00

Post Data In ASP.NET

POST:

 WebRequest request = WebRequest.Create("post url");
 String postdata;
 Byte[] bytedata;
postdata="key=xxx&value=yyyy";
 bytedata = Encoding.UTF8.GetBytes(postdata);
            request.ContentLength = bytedata.Length;
            request.ContentType = "application/x-www-form-urlencoded";
            request.Method = "POST";
            Stream dataStream = request.GetRequestStream();
            dataStream.Write(bytedata, 0, bytedata.Length);
            dataStream.Close();

GET RESPONSE:

 WebResponse response = request.GetResponse();
            dataStream = response.GetResponseStream();
            StreamReader reader =new StreamReader (dataStream);
            String responseFromServer = reader.ReadToEnd();

REQUEST POST DATA:

NameValueCollection nvc = Request.Form
String key;
String value;
        key = nvc("key");
        value=nvc("value");

Do let me know if anything wrong.

generate XML log using log4net

 Define configuration setting as below:

<configSections>
    <section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler, log4net"/>
  </configSections>
  <log4net>
    <appender name="RollingFileAppender" type="log4net.Appender.RollingFileAppender">
      <file type="log4net.Util.PatternString" value="C:\log.xml" />
      <appendToFile value="true" />
      <datePattern value="yyyyMMdd" />
      <rollingStyle value="Size" />
      <maxSizeRollBackups value="2" />
      <maximumFileSize value="10KB" />
      <layout type="log4net.Layout.XmlLayoutSchemaLog4j">
        <locationInfo value="true" />
      </layout>
    </appender>
    <root>
      <level value="DEBUG" />
      <appender-ref ref="RollingFileAppender" />
    </root>
  </log4net>

Select nth row from table in sql server

select * from(
select id,name,row_number() over(order by id) as 'row'  from student)as temp where row=n

Note

To select last row

select top 1 * from student order by id desc // where id is unique column