11 Ağustos 2010 Çarşamba

dBase (.dbf) Dosya Okuma

Selam,
dBase yani dbf dosyalarını okumanız için ilk önce sınıfınıza gerekli olan using ifadesi olan System.Data.Odbc eklemiz gerekmektedir.

Sonra aşağıdaki metodu kullanmanız yeterli olacaktır.

private void ReadData()
        {
            OdbcConnection oConn = new OdbcConnection();
            oConn.ConnectionString = @"Driver={Microsoft dBase Driver (*.dbf)};SourceType=DBF;SourceDB= C:\ ;Exclusive=No; Collate=Machine;NULL=NO;DELETED=NO;BACKGROUNDFETCH=NO;";
            oConn.Open();
            System.Data.Odbc.OdbcCommand oCmd = oConn.CreateCommand();
            oCmd.CommandText = @"SELECT * FROM C:\item.dbf WHERE ITEM = 10001";
            DataTable dt = new DataTable();
            dt.Load(oCmd.ExecuteReader());
            oConn.Close();

            bdfDataGridView.DataSource = dt;
        }

Kolay Gelsin.

C Sharp ile Excel Dosyasına Yazmak

Herkese Selam,
Bir süre önce excell dosyası oluşturan ve bu dosyanın içerisine belirtilen değerleri yazan bir class yazmıştım. Bu class’ı sizinle paylaşmak istedim. Belki bir gün bir yerde ihtiyacınız olabilir.
public class ExcelService
    {
        private Microsoft.Office.Interop.Excel.Application app;
        private Microsoft.Office.Interop.Excel.Workbook workbook;
        private Microsoft.Office.Interop.Excel.Worksheet worksheet;
        private Microsoft.Office.Interop.Excel.Range range;
        public void CreateExcelDocument()
        {
            CultureInfo oldCulture = Thread.CurrentThread.CurrentCulture;
            Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
            app = new Microsoft.Office.Interop.Excel.Application();
            app.Visible = false;
            Thread.CurrentThread.CurrentCulture = oldCulture;
        }
        public void Save(string path)
        {
                CultureInfo oldCulture = Thread.CurrentThread.CurrentCulture;
                Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
                workbook.SaveAs(path, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                Thread.CurrentThread.CurrentCulture = oldCulture;
        }
        public void CreateWorkSheet(string workSheetName)
        {
            CultureInfo oldCulture = Thread.CurrentThread.CurrentCulture;
            Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
            Microsoft.Office.Interop.Excel.Worksheet retVal;
            if (workbook == null)
            {
                workbook = app.Workbooks.Add(true);
                retVal = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                retVal.Name = workSheetName;
                Microsoft.Office.Interop.Excel.Worksheet currentWorkSheet;
                for (int i = 1; i <= workbook.Worksheets.Count; i++)
                {
                    currentWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[i];
                    if (currentWorkSheet.Name.Equals("Sheet1"))
                    {
                        currentWorkSheet.Delete();
                    }
                }
            }
            else
            {
                retVal = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.Add(Type.Missing,
                                                                                           Type.Missing,
                                                                                           Type.Missing,
                                                                                           Type.Missing);
                retVal.Name = workSheetName;
            }
            Thread.CurrentThread.CurrentCulture = oldCulture;
        }
        public void WriteData(int row, int col, string data, string stringFormat, string workSheetName)
        {
            CultureInfo oldCulture = Thread.CurrentThread.CurrentCulture;
            Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
            for (int i = 1; i <= workbook.Worksheets.Count; i++)
            {
                Microsoft.Office.Interop.Excel.Worksheet retVal =
                    (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[i];
                if (retVal.Name.Equals(workSheetName))
                {
                    if (!(string.IsNullOrEmpty(stringFormat)))
                    {
                        range = retVal.get_Range(retVal.Cells[row, col], retVal.Cells[row, col]);
                        range.Value2 =data.Replace(",", ".");
                        range.NumberFormat = stringFormat;
                    }
                    else
                    {
                        range = retVal.get_Range(retVal.Cells[row, col], retVal.Cells[row, col]);
                        range.Value2 = data;
                    }
                }
            }
            Thread.CurrentThread.CurrentCulture = oldCulture;
        }
        public void MergeColumns(int row, int col, int row2, int col2, int mergeColumns, string workSheetName)
        {
            CultureInfo oldCulture = Thread.CurrentThread.CurrentCulture;
            Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
            for (int i = 1; i <= workbook.Worksheets.Count; i++)
            {
                Microsoft.Office.Interop.Excel.Worksheet retVal =
                    (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[i];
                if (retVal.Name.Equals(workSheetName))
                {
                    range = retVal.get_Range(retVal.Cells[row, col], retVal.Cells[row2, col2]);
                    range.Merge(mergeColumns);
                }
            }
            Thread.CurrentThread.CurrentCulture = oldCulture;
        }
        public void EditCell(int row, int col, string color, bool isBold, int size, string workSheetName)
        {
            CultureInfo oldCulture = Thread.CurrentThread.CurrentCulture;
            Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
            for (int i = 1; i <= workbook.Worksheets.Count; i++)
            {
                Microsoft.Office.Interop.Excel.Worksheet retVal =
                    (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[i];
                if (retVal.Name.Equals(workSheetName))
                {
                    range = retVal.get_Range(retVal.Cells[row, col], retVal.Cells[row, col]);
                    switch (color)
                    {
                        case "ORANGE":
                            range.Interior.Color =
                    System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Orange);
                            break;
                        case "KHAKI":
                            range.Interior.Color =
                            System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Khaki);
                            break;
                        case "BURLYWOOD":
                            range.Interior.Color =
                    System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.BurlyWood);
                            break;
                        case "SKYBLUE":
                            range.Interior.Color =
                    System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.SkyBlue);
                            break;
                        case "GAINSBORO":
                            range.Interior.Color =
                    System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Gainsboro);
                            break;
                        case "WHITESMOKE":
                            range.Interior.Color =
                    System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.WhiteSmoke);
                            break;
                        default:
                            //  workSheet_range.Interior.Color = System.Drawing.Color..ToArgb();
                            break;
                    }
                    range.Font.Bold = isBold;
                    range.ColumnWidth = size;
                }
            }
            Thread.CurrentThread.CurrentCulture = oldCulture;
        }
        internal void Dispose()
        {
            if (app != null)
            {
                app.Quit();
            }
            app = null;
            workbook = null;
            worksheet = null;
            range = null;
        }
    }

Kullanmadan önce projemize interop excell referansını vermeniz gerekir.


Bu işlemi yaptıktan sonra gerekli using ifadelerini vermemiz gerekir. Bunlar;
using System.Globalization;
using System.Threading;
Bu işlemlerden sonra sınıfımız kullanmaya hazırdır. Kullanımı için basit bir örnek verecek olursam.
            excellApp.CreateExcelDocument();
            excellApp.CreateWorkSheet("Deneme Work Sheet");
            excellApp.WriteData(1, 1, "Emrah", "", "Deneme Work Sheet");
            excellApp.MergeColumns(1, 1, 1, 2, 2, "Deneme Work Sheet");
            excellApp.EditCell(1, 1, "ORANGE", true, 7, "Deneme Work Sheet");
            excellApp.Save(@"C:\deneme");
Bu kod  yardımıyla C: \ dizini altında deneme excell dosyası oluşur. Sonucunda deneme dosyasının içerisinde şunu görürsünüz.




Bu kadar basit umarım işinize yarar.
Kolay gelsin.

10 Ağustos 2010 Salı

MsSql’de Bir Tablodan Başka Bir Tabloya Veri Kopyalamak

Merhaba bazen projelerinizde bir tablo oluşturur ve  bu tabloya bir veya birden fazla tablodan verileri eklemek isteyebilirsiniz.
Bu manuel olarak yapılmaya kalkışıldığında eziyet çekmeniz içten değildir. Bu durumda hemen Sql de bir query aracılığıyla işlemimizi gerçekleştirebiliriz.
INSERT INTO TabloAdı(ad, soyad)
SELECT Ad, Soyad
FROM TabloAdı
WHERE Id = 4
Bu şekilde basit bir sorgu ile işlemimiz gerçekleşir. Eğer Farklı database tablolarında aynı işlemi yaparsak Linked Server kullanarak tabloların başına sadece varsa ip ve db adını yazarak işleminizi gerçekleştirebilirsiniz.